Report Builder - Filtering and Transforming Data through Data Pipeline

Add a Data Source for a Widget

Please find the instructions on setting up a Data Source at Report Builder - Adding a Data Source for a Widget

Select the widget

Left-click on a Widget put on the Canvas

Go to ‘Properties’ tab

Left-click on a ‘Properties’ tab of the selected Widget

Click on ‘Add Transformer’ button inside Data Pipeline section

Left-click on ‘Add Transformer’

Select the required Data Transformer and set up its parameters if available

Please find the list of available transformers and their description at Report Builder - Adding a Data Source for a Widget

Click on ‘Save’ button

Left-click on ‘Save’

Advanced Transformer

Supports custom JS code to transforms the data

1. Description - user readable description of the transformer

2. Data From - the widgets from which data needs to be made available to the transformer. The data of each widget will be passed as a parameter to the custom JS function. Leave this empty, if no other data is required.

3. Code - custom JavaScript code

/**
* Your code will be called with the following parameters:
* 'data', 'dataFrom' and '_'.
* @param {any[]} data data from previous transformer
* @param {Record<string, any[]>} dataFrom a map of widget name to it's data
* @param _ Lodash library for list/collection processing
*
* @returns processed/modified data
*/ 
// Sample Code
//
// const value = dataFrom['My Value Widget'];
// data = _.map(data, d => ({...d, value: d.value/value}));
return data;

Arithmetic Operation

Returns the result of arithmetic operation applied to an attribute

1. Attr - name of the attribute whose value needs to be manipulated. Possible values are:

   1. ‘delta’ (difference between data rows returned by a previous pipe)

   2. ‘value’ (Point’s value)

   3. any custom attribute (variable) defined by a previous pipe

2. Operation - Arithmetic operation to perform

   1. Add

   2. Divide

   3. Modulo

   4. Multiply

   5. Subtract

3. Value - the value to add/subtract/multiply/divide

Convert Time

Returns the converted time value

1. Attr - name of the attribute whose value needs to be converted.

   1. ‘timestamp’ (default parameter associated with point data)

   2. any custom attribute (variable) defined by a previous pipe that contains time data

2. From - input value's unit of time.

3. To - output value's unit of time.

Count

Returns the number of data strings

None

Count will return 3 according to the number of data strings:

Cumulative delta

Returns the difference in target attribute of each data row versus the next one

1. Attr - the attribute name on whose value delta has to be calculated. Possible values are:

   1. ‘delta’ (difference between data rows returned by a previous pipe)

   2. ‘value’ (Point’s value)

   3. any custom attribute (variable) defined by a previous pipe

2. Target Attr - the attribute to which the delta needs to be written. Possible values are:

   1. ‘delta’ (difference between data rows returned by a previous pipe)

   2. ‘value’ (Point’s value)

   3. any custom attribute (variable) defined by a previous pipe

3. Calculate Percentage - if enabled, Ithe delta is calculated in %. Otherwise, it’s calculated in absolute value

Example 1: Calculating delta in absolute value

Cumilative Delta with the following parameters

1. Attr = value

2. Target Attr = value

3. Calculate Percentage = No

will return:

row 109 = row 109 - row 108 = 51-55= -4

Before Applying Cumulative Delta

After Applying Cumulative Delta

Example 2: Calculating delta in %

Cumilative Delta with the following parameters

1. Attr = value

2. Target Attr = value

3. Calculate Percentage = Yes

will return:

row 360 = row 360 / row 359 *100% = 9 / 6.1 * 100%= 1,475 * 100% = 147,5% (+48%)

Before Applying Cumulative Delta

After Applying Cumulative Delta

Extract attribute value

Retrieve value of an attribute from data

1. Attr - attribute name whose value needs to be retrieved from data. Possible values are:

   1. ‘delta’ (difference between data rows returned by a previous pipe)

   2. ‘value’ (Point’s value)

   3. any custom attribute (variable) defined by a previous pipe

Getting attributes from a Data String:

1. Add ‘Value’ widget to get the single value of a single attribute, specify any data source with existing data:

   

2. Add ‘Last item’ pipeline for Value widget to get the latest data string:

   
   

3. Add ‘Extract Attribute value’ pipeline, try out different ‘Attr’ parameters to see their values:

   

   a. ‘Attr’ = ‘timestamp’ returns ‘2021-12-19T21:00:00+00:00’

   

   b. ‘Attr’ = ‘value’ returns '9'

   c. ‘Attr’ = ‘uuid’ returns 'e96a3498-b332-4d4c-bd41-2d68dfe6c388'

   

   d. ‘Attr’ = ‘uri’ returns '/TurntideTest/Denver Lab/Lab Equipment/Wattnode/properties/AI/Power Sum'

   

Format Number

Returns locale-based formatted number

1. Attr - name of the attribute whose value needs to be formatted.

   1. value

   2. any custom attribute (variable) defined by a previous pipe that contains numeric value

Format Time

Returns Time in human-readable format

1. Attr - name of the attribute whose value needs to be formatted.

   1. ‘timestamp’ (default parameter associated with point data)

   2. any custom attribute (variable) defined by a previous pipe that contains time data

2. Source Format - format of the source value

   1. Day

   2. Hour

   3. Minute

   4. Second

   5. Week

3. Minimum Time Unit - minimum time unit to be displayed

   1. Day

   2. Hour

   3. Minute

   4. Week

Seconds to Week conversion for 'timestamp' attribute

Formatted Time (weeks)

Format Relative data

Formats relative date time strings in natural language ex: Last week, This month etc; to absolute date strings.

None

1. Add a Text Widget

2. Add ‘Format Relative Data’ pipeline

3. Try out different ‘Text’ values with relative data and review the result:

   1. 

4. ‘Text’ = ‘Today’ will return ‘Dec 20, 2021’

5. ‘Text’ = ‘Yesterday’ will return 'Dec 19, 2021’

6. ‘Text’ = ‘Last Week’ wil return ‘Dec 13, 2021 - Dec 19, 2021’

ISO formatter

Formats ISO dates into user specified format

1. Attr - name of the attribute whose value should be formatted. Ex: timestamp.

2. Source Format - the source datetime format

3. Format - the format to which the value must be converted to.

1. Add a Table Widget, specify any data source with existing data:

2. Select Table Columns:

   1. Timestamp

   2. Any other columns, e.g. Value, Parameter

3. Add ISO formatter pipeline:

   1. The Source Data format is YYYY-MM-DDTHH:mm:ss (2021-12-17T00:00:00)
      
      

4. Try out different ‘Format’ values with and review the result:

   1. ‘Format’ = ‘YYYY-MM-DD’ will convert source data format to ‘2021-12-17’

      

   2. ‘Format’ = ‘DD-MM-YYYY’ will will convert source data format to ‘17-12-2021’

      

   3. ‘Format’ = ‘HH:MM’ will will convert source data format to ‘00:00’
      

Localize Data & Time

Localizes the date and time to users's timezone

1. Attr - name of the attribute whose value should be formatted. Ex: timestamp.

2. Enabled - if enabled, disables localizing, uses UTC

1. Add a Table Widget, specify any data source with existing data:

2. Select Table Columns:

   1. Timestamp

   2. Any other columns, e.g. Value, Parameter

3. Add 'Localize Data & Time' pipeline

Current User’s Timezone is UTC+3

Date&Time format before applying Localize Data & Time:

Date&Time format after applying Localize Data & Time

Parse URI

Extracts attributes from Data String’s URI ("uri": "/client_id/site_name/properties/equip_name/ptype/point_name").

Example:

None

Example 1: Table view

Table widget applies Parse URI piplene by default to represent data from URI in multiple columns

Example 2: String view

1. Add ‘Value’ widget to get the single value of a single attribute, specify any data source with existing data

   

2. Add ‘Last item’ pipeline for Value widget to get the latest data string

3. Add ‘Parse URI' pipeline for Value widget
   

4. Add ‘Extract Attribute value’ pipeline, try out different URI’s parameters as ‘Attr’ to get their values:

Reverse list

Reverses the items in the list

None

Rollup data

Rolls up history data based on the given rollup frequency and digest

1. Digest - digest function to be run on the data

   1. First - use the first value available inside the given time frame

   2. Last - use the last value available inside the given time frame

   3. Max - use the max value available inside the given time frame

   4. Mean - use the first value available inside the given time frame

   5. Median - use the average value available inside the given time frame

   6. Min - use the middle number of a group of values available inside the given time frame. That is, half the numbers return values that are greater than the median, and half the numbers return values that are less than the median.

   7. Mode - use the the most commonly occurring value (the mode value) in the aggregated value available inside the given time frame

   8. None - shows the historical value change (raw data)  available inside the given time frame

   9. Standard Deviation - use the statistical standard deviation of all values in the set based on a sample of the population available inside the given time frame

   10. Sum - use the sum of values available inside the given time frame

   11. Variance - use the variance value available inside the given time frame

2. Frequency - the frequency at which history data needs to be grouped and digest applied

3. Group By - the attribute on which the data should be grouped by, the digest is run on each group. If data needs to be aggregated, leave this empty. Possible values:

   1. uuid

Set precision

Sets the precision of floating point values

1. Attr - name of the attribute whose value should be transformed, ex. ‘value’

2. Precision - the precision to round up to

Sort data

Sorts data by the specified attribute

1. Attr - name of the attribute whose value should be transformed, ex. ‘value’

2. Is Timestamp - defines whether a value will be sorted as a string or as a timestamp. Will be set to ‘true’ if the field is a timestamp value. Otherwise, will be set to ‘false’.

Transform data to scatter

1. Xpoint - point to be displayed on X axis of a Scatter Chart

2. Ypoint - point to be displayed on Y axis of a Scatter Chart

3. Xrollup - rollup to be applied to X axis of a Scatter Chart (see Ref.10 Rollup data for rollups description)

4. Yrollup - rollup to be applied to Y axis of a Scatter Chart (see Ref.10 Rollup data for rollups description)

Transforms data to support Scatter Chart widget. Will be always applied for that widget to see any data visualized.

Add a Data Source for a Widget

Please find the instructions on setting up a Data Source at Report Builder - Adding a Data Source for a Widget

Select the widget

Left-click on a Widget put on the Canvas

Go to ‘Properties’ tab

Left-click on a ‘Properties’ tab of the selected Widget

Click on ‘Add Filter’ button inside Data Pipeline section

Left-click on ‘Add Transformer’

Select the required Data Filter and set up its parameters if availbale

Please find the list of available Filters and their description at Report Builder - Filtering and Transforming Data through Data Pipeline

Click on ‘Save’ button

Left-click on ‘Save’

Contains

Filters rows of data whose attr's value is contained in the specified list of of values

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Values - list of values that need to be considered for filtering (OR condition is applied in case of multiple Values specified)

Equal to (==)

Filters rows of data whose value is equal to the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering

First Item

Retrieves the first item in the data

None

Greater than (>)

Filters rows of data whose value is greater than the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering

Greater than / equal to (>=)

Filters rows of data whose value is greater than or equal to the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering

Last Item

Retrieves the last item in the data

Lesser than (<)

Filters rows of data whose value is lesser than the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering

Lesser than/equal to (<=)

Filters rows of data whose value is lesser than or equal to the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering

Limit

Limits/Truncates data to the no of items specified from the first item in the list

1. Limit - number of items to limit starting from the first item

2. From Bottom - slice the list from bottom

Not equal to (!=)

Filters rows of data whose value is not equal to the specified value

1. Attr - name of the attribute whose value should be compared. Ex: value, point_name etc.

2. Value - list of values that need to be considered for filtering