Pivot Grid

Pivot grid allows to visualize data in a multi-dimensional format.

Use the pivot grid to arrange your data in a two-dimensional table, and compare tag names and their columns against timestamps. With the pivot grid, you can:

Rename rows/columns to replace lengthy tag names
Apply conditional formatting to rows/columns based on values
Calculate values quickly using totals
Sort, filter, and summarize data
Export pivot data to Excel

The pivot grid is available under INTEGRATION > DISPLAY. When designing application pages, drag-and-drop GEPivotGrid to a container.

The following list of properties are specific to this widget. For common properties, refer to Page Components (Classic).

Pivot Grid Properties

Field Name	Description
Source - Input	To enable the pivot grid, provide values from a data source. Select a query. Select the query output fields you want to use to get data for the pivot grid.
Use Raw Format	Select the check box to display numbers in raw format. For example, a numeric value with 5 or more decimal places is shown as it is, and not rounded off.
Number of decimals	This option allows you to format numeric values in the Pivot Grid. If you do not want to display numbers in raw format, you can specify the number of decimal places (0-7) to consider after the decimal point. The value will be rounded off to the nearest whole number based on the specified decimals. This format will apply to all Pivot Grid columns that contain numeric values, including those with a 'String' data type.
Height (px) - 0 equals dynamic	Set a height for the pivot grid in pixels. If set to 0, the widget occupies 100% of its container.
Row Header Width (px) - 0 equals dynamic	Set a width for the header row in pixels.
Show Borders	Select the check box to apply a border to the pivot grid table. To remove the border, clear the check box.
Allow Sorting	Select the check box to sort the rows and columns alphabetically by default.
Allow Sorting by Summary	Select the check box to provide an option to sort the rows by column. At runtime, right-click the column you want to sort by and select the sorting option.
Allow Filtering Row/Column Values	Select the check box to provide an option to filter data at runtime.
Allow Expand/Collapse All	Select the check box to enable expand/collapse data levels at runtime. Your pivot grid should have multiple data levels to use this property. For value-based table cell data, you can expand and collapse data details to aggregate values.
Allow Export	Select the check box to allow exporting data at runtime.
Show Row Totals	Select the check box to show totals for each row.
Show Row Grand Totals	Select the check box to show the grand totals for each row.
Show Column Totals	Select the check box to show totals for each column.
Show Column Grand Totals	Select the check box to show the grand totals for each column.
Field Configuration	Configure pivot grid rows, columns, and data. Map all the three areas (rows, columns, data). Enter the tag names under Data Field. Select the Data Type for the entered tag names. For example, `string`. Enter a Caption. Use this option to add user-friendly captions to your row/column/data fields. Avoid lengthy captions with special characters. Select the Area for the tags to appear. For example, if you select column, then the respective tags appear in the column at runtime. When area is data, define additional properties for data values.

Formatting: Set default colors for the pivot grid.

Field Name	Description
Cell Color	Select a color for the pivot grid table cells.
Cell Font Color	Select a font color for the pivot grid table cell text.
Column Header Font Color	Select a font color for the pivot grid table column header text. This helps to differentiate the header text from the cell text.
Totals Background Color	Select a unique cell color for the row/column totals.
Totals Font Color	Select a font color for the row/column totals text.
Hidden	Select this check box if you want to hide the plug-in. To show the hidden plug-in in Operations Hub new layout, go to Page Visuals and under Action column, select for the hidden plug-in. See Page Visuals Tab.

Item Configuration: Allows to customize the pivot grid table to override the default settings. Select +Add Item for configuring options.

Field Name Description

Item Display

Field Name	Description
Item Display	Row/Column Value: Enter the value that is displayed in a row or column. For example, if you’re viewing OPCUA tags and their values, you can enter a tag name such as `FIX.FWT_FWP_DPUMP_1A_FLOW.F_CV`. Display Value (optional): Use this option to display more user-friendly values instead of tag names. This display value overrides the Row/Column Value. For example, enter `Pump1A Flow` to replace the OPC UA tag name `FIX.FWT_FWP_DPUMP_1A_FLOW.F_CV` entered under Row/Column Value. At runtime, the column displays values for `Pump1A Flow` in the pivot chart.
Conditional Formatting	Apply conditions to format cells based on values. You can create more than one condition for a Row/Column Value. The conditions are applied according to their order of display. If more than one rule matches a particular cell, the first condition in the list to match takes effect and rule processing for that cell will stop. Select +Add Item for options to create conditions. For example, format a cell if its value is less than 9000: Comparison: Select `<` from the list of comparison operators. Value: Enter `9000`. Cell Color: Select a color for the cells with values less than 9000. Cell Font Color: Select a color for the cell text with values less than 9000. Tip: If you want to format a row/column with a color that does not change based on value, apply the `Always` comparison operator.

Row/Column Value: Enter the value that is displayed in a row or column.
For example, if you’re viewing OPCUA tags and their values, you can enter a tag name such as FIX.FWT_FWP_DPUMP_1A_FLOW.F_CV.
Display Value (optional): Use this option to display more user-friendly values instead of tag names. This display value overrides the Row/Column Value.
For example, enter Pump1A Flow to replace the OPC UA tag name FIX.FWT_FWP_DPUMP_1A_FLOW.F_CV entered under Row/Column Value. At runtime, the column displays values for Pump1A Flow in the pivot chart.

Conditional Formatting

Apply conditions to format cells based on values. You can create more than one condition for a Row/Column Value. The conditions are applied according to their order of display. If more than one rule matches a particular cell, the first condition in the list to match takes effect and rule processing for that cell will stop.

Select +Add Item for options to create conditions. For example, format a cell if its value is less than 9000:

Comparison: Select < from the list of comparison operators.
Value: Enter 9000.
Cell Color: Select a color for the cells with values less than 9000.
Cell Font Color: Select a color for the cell text with values less than 9000.

Tip: If you want to format a row/column with a color that does not change based on value, apply the Always comparison operator.

Pivot Grid at Runtime

Select

to export the data to an Excel file.

Select

to show the Field Chooser option.

You can switch the position of row and column fields. To switch a field position, drag and drop the field in the desired row/column.
You can apply multiple filters to the data to get focused results.
You can sort fields by ascending/descending order.

Note: In Field Chooser, row/column/data fields may get misplaced if you remove and add fields to the pivot chart. This is a DevExtreme control behavior. For example (refer screenshot):

Under All Fields, clear the check box for Name. The field is removed from Row Fields.
Under All Fields, select the check box again for Name.
The Name field may not show up under Row Fields. It may show up under column or data fields. In such cases, drag and drop the field to the appropriate location.