QPR ProcessAnalyzer Pivot Table

Pivot table visualizes data organized as dynamical rows and columns and aggregated numerical values. Compared to the table, rows in the pivot table work similarly, but in the pivot table the number of columns is determined dynamically based on the data. It's also possible set several dimensions as rows and columns to create hierarchical visualization.

Pivot Table Functionalities

Pivot table has the following functionalities:

• Pivot table can show several values at the same time, so you can define many measures, dimensions or columns and map the to the Values. By default, the measures are shown as the pivot table values.
• There can be several measures, dimensions or columns mapped as the pivot table Rows. When there are more than one, they are shown as a hierarchical structure. By default, the first dimension is shown in rows.
• Columns work similarly as the rows, so there can be several measures, dimensions or columns mapped to the pivot table Columns. By default, the second dimension is shown in columns.
• When there is the hierarchy shown, the branches in the hierarchy can be expanded and collapsed. By default, branches are collapsed, but they can be set as expanded by using the Layout settings.
• Values are aggregated using the Pivot table aggregation setting in the measure, dimension and column settings.
• Rows and columns are sorted based on the Sorting settings (in the General tab). If needed, use the custom sorting to define all the desired sortings.
• Data can be sorted by a certain value column by clicking the column header. By default, this sorting is not stored to the dashboard (see the examples for storing measure sorting).
• Appearance of the shown numerical and date values can be changed in the measure, dimension and column settings, using settings Round to decimals, Measure unit, Data type and Date format.
• Title for the pivot table can be set in the General tab.
• Columns can be resized, but the changed widths are not stored to the dashboard.
• Pivot table can be exported as Excel, pdf and csv (open settings, General tab and Export Table as...).
• Pivot table settings can be customized extensively by using the Custom layout field. Available options are described in https://ej2.syncfusion.com/documentation/pivotview/.

Conditional Formatting

Pivot table has the same conditional formatting options as the table. Conditional formatting allows to set pivot table values cells background colors and text color based on the shown values to improve visualization. It's also possible to show a databar and icon in the values cells.

Customization Examples

Hide Grand Totals

Grand totals can be hidden, and there are following settings separately for rows and columns:

{
  "dataSourceSettings": {
    "showColumnGrandTotals": false,
    "showRowGrandTotals": false
  }
}

Sort Rows by Measure Values

It's possible to sort the pivot table rows using values in a certain column (New York in the example below) as follows:

{
  "dataSourceSettings": {
    "valueSortSettings": {
      "headerText": "New York",
      "sortOrder": "Ascending"
    }
  }
}

The above example works, if there is one value column. If there are several values, the value used for sorting need to be specified (Case count in the example below)in addition to the column name as follows:

{
  "dataSourceSettings": {
    "valueSortSettings": {
      "headerText": "New York#Case count",
      "sortOrder": "Descending",
      "headerDelimiter": "#"
    }
  }
}

Sort Columns by Measure Values

It's possible to sort the pivot table columns based on the values. The principle is the same as with sorting the rows except valueAxis setting needs to be set to rows:

{
  "dataSourceSettings": {
    "valueAxis": "row",
    "valueSortSettings": {
      "headerText": "New York",
      "sortOrder": "Descending"
    }
  }
}

This example sorts columns based on the Grand Total:

{
  "dataSourceSettings": {
    "valueAxis": "row",
    "valueSortSettings": {
      "headerText": "Grand Total",
      "sortOrder": "Descending"
    }
  }
}

Expand Hierarchy

By default, the second level in the rows or columns hierarchy is collapsed. Use this setting to expand the hierarchies by default:

{
  "dataSourceSettings": {
    "expandAll": true
  }
}