QPR ProcessAnalyzer Pivot Table: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
 
(29 intermediate revisions by the same user not shown)
Line 1: Line 1:
The '''pivot table''' visualizes measures sliced into dimensions where a dimension can be shown as rows and another dimension as columns. Compared to the '''table''', rows in the pivot table work similarly, but unlike the table, in the pivot table the number of columns is determined dynamically based on number of values in the dimension. It's also possible set several dimensions as rows or columns to create hierarchical visualization.
Pivot table visualizes data organized as dynamical rows and columns and aggregated numerical values. Compared to the [[QPR_ProcessAnalyzer_Table|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 Functionalities ==
Pivot table has the following functionalities:
Pivot table has the following functionalities:
* Pivot table is able to show several measures at the same time, so you can define as many measures in the ''Measures'' tab as you need.
* Pivot table can show several measures (i.e., values) in the same table by defining several measures, dimensions or columns and mapping them the to the '''Values''' (''Mapping to visualization'' in the measure, dimension or column settings). By default, the measure expressions are shown as the pivot table values.
* The first dimension is shown as rows in the pivot table and the second dimension is shown as columns.
* There can be several measures, dimensions or columns mapped to the pivot table '''Rows'''. When there are more than one mapped, they are shown as a hierarchical structure. By default, the first dimension expression is shown in rows.
* The third dimension is shown in the rows as a sublevel, and the fourth is shown in the columns as a sublevel (the sublevels can be expanded from the arrow icon).
* Pivot table 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 as columns.
* 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.
* When rows or columns show the hierarchy, the branches in the hierarchy can be expanded and collapsed. By default, branches are collapsed, but they can be set as expanded on open by using the [[#Expand_Hierarchy|Layout settings]].
* Data can be sorted by a certain measure column by clicking the column headers. By default, this sorting is not stored to the dashboard (see the examples for storing measure sorting).
* Values are aggregated using the '''Pivot table aggregation''' setting in the measure, dimension and column settings. All aggregations can be used with the numerical measures, dimensions and columns, and also the ''Count'' and ''Distinct count'' can be used with any type of measures, dimensions and columns.
* Measures in the pivot table are aggregated to the ''Grand Total'' row in the bottom and column in the right. The aggregation is determined automatically from the measure settings. If needed, the aggregation can be overridden - see the configuration examples. Available aggregations are: '''Sum''', '''Min''', '''Max''', '''Avg''', '''Product''', '''Count''', '''DistinctCount''', '''PopulationStDev''', '''SampleStDev''', '''PopulationVar''' and '''SampleVar'''.
* Rows and columns are sorted based on the ''Sorting'' settings (in the ''General'' tab). If needed, use the custom sorting to get the desired sorting.
* Appearance of the shown numerical and date values can be changed in the measure and dimension settings, using settings ''Round to decimals'', ''Measure unit'', ''Data type'' and ''Date format''.
* Data can also be sorted by a certain value column by clicking the column header. Sorting by values can also be stored to the dashboard, so that the values sorting is applied when the dashboard is opened. This is done by using the ''Custom layout'' settings (see the examples below for values sorting).
* Pivot table can be exported as Excel, pdf and csv (open settings,  ''General'' tab and ''Export Table as...'').
* Appearance of the shown data points in rows, columns and values can be changed in the measure, dimension and column settings by using settings ''Round to decimals'', ''Unit'', ''Unit position'' and ''Date format''.
* The top left cell of the pivot grid shows the measure, dimension or column labels that have been selected as rows. If there are several measures/dimensions/columns as rows (i.e., there is a hierarchy), the labels are separated by the slash character ("/"). If you want to customize the labels, you can define a ''Custom label'' for the measure/dimension/column selected as rows. If you don't want to show the label at all, you can set a space character for the custom label.
* Title for the pivot table can be set in the ''General'' tab.
* Columns can be resized by dragging the border between column headers. 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 as...'').
* For advanced needs, the pivot table settings can be customized by using the ''Custom layout'' settings. Available options are described in https://ej2.syncfusion.com/documentation/pivotview/.


==Examples==
==Conditional Formatting==
Pivot table has the same [[QPR_ProcessAnalyzer_Table#Conditional_formatting|conditional formatting]] options as the table. Conditional formatting allows to set pivot table value cells background color and text color based on the shown values to improve the illustration. It's also possible to show a databar and icon in the value cells.


===Change Aggregation Method===
==Change Column Widths==
This example sets the aggregation method for the first measure to sum and for the second measure to average.
Width of the first column (contains the row headers) and widths of the value columns (rest of the columns) can be changed as follows: In the chart settings dialog ''Advanced'' tab, open the ''Chart settings (editable)'' and add the '''tableColumnWidths''' property to the existing json settings. Here is an example how does the tableColumnWidths property look:
<pre>
<pre>
{
{
   "dataSourceSettings": {
   "tableColumnWidths": [ 200, 150 ],
    "values": [
   ...
      {
        "type": "Sum"
      },
      {
        "type": "Avg"
      }
    ]
   }
}
}
</pre>
</pre>
The first value (200) defines the width of the first column (as pixels) and the seconds value (150) defines the widths of the value columns (as pixels). Note that as the number of value columns varies based on the data, width for each column cannot be set separately, but the second value affects all the value columns.
==Customization Examples==
Using the '''Custom Layout''' field (in the '''Visual''' tab), the pivot table functionality and visual appearance can be adjusted for custom needs. This chapter contains examples of common customizations.


=== Hide Grand Totals ===
=== Hide Grand Totals ===
Grand totals can be hidden separately for rows and columns.
Grand totals can be hidden, and there are following settings separately for rows and columns:
<pre>
<pre>
{
{
   "dataSourceSettings": {
   "dataSourceSettings": {
     "showColumnGrandTotals": false,
     "showColumnGrandTotals": false,
     "showRowGrandTotals": false,
     "showRowGrandTotals": false
   }
   }
}
}
</pre>
</pre>


=== Sort by Measure Values ===
=== Sort Rows by Measure Values ===
It's possible to sort the pivot grid rows using a measure values from a certain column. If there is only one measure, the following settings can be used:
It's possible to sort the pivot table rows using values in a certain column (''New York'' in the example below) as follows:
<pre>
<pre>
{
{
Line 55: Line 58:
</pre>
</pre>


If there are several measures, the measure used for sorting need to be specified (in addition to the column dimension value) as follows:
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:
<pre>
<pre>
{
{
Line 61: Line 64:
     "valueSortSettings": {
     "valueSortSettings": {
       "headerText": "New York#Case count",
       "headerText": "New York#Case count",
      "sortOrder": "Descending",
      "headerDelimiter": "#"
    }
  }
}
</pre>
=== 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'':
<pre>
{
  "dataSourceSettings": {
    "valueAxis": "row",
    "valueSortSettings": {
      "headerText": "New York",
       "sortOrder": "Descending"
       "sortOrder": "Descending"
      "headerDelimiter": "#"
     }
     }
   }
   }
Line 68: Line 85:
</pre>
</pre>


=== Hierarchy Expanded ===
This example sorts columns based on the ''Grand Total'':
By default, the second level in the rows or columns hierarchy is collapsed. Use this setting to expand the hierarchies by default:
<pre>
<pre>
{
{
   "dataSourceSettings": {
   "dataSourceSettings": {
     "expandAll": true
     "valueAxis": "row",
    "valueSortSettings": {
      "headerText": "Grand Total",
      "sortOrder": "Descending"
    }
  }
}
}
</pre>
</pre>


=== Show Tooltip ===
=== Expand Hierarchy ===
The cell tooltip can be shown as follows:
By default, the second level in the rows or columns hierarchy is collapsed. Use this setting to expand the hierarchies by default:
<pre>
<pre>
{
{
   "showTooltip": true
   "dataSourceSettings": {
    "expandAll": true
  }
}
}
</pre>
</pre>


[[Category: QPR ProcessAnalyzer]]
[[Category: QPR ProcessAnalyzer]]

Latest revision as of 15:58, 29 June 2023

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 measures (i.e., values) in the same table by defining several measures, dimensions or columns and mapping them the to the Values (Mapping to visualization in the measure, dimension or column settings). By default, the measure expressions are shown as the pivot table values.
  • There can be several measures, dimensions or columns mapped to the pivot table Rows. When there are more than one mapped, they are shown as a hierarchical structure. By default, the first dimension expression is shown in rows.
  • Pivot table 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 as columns.
  • When rows or columns show the hierarchy, the branches in the hierarchy can be expanded and collapsed. By default, branches are collapsed, but they can be set as expanded on open by using the Layout settings.
  • Values are aggregated using the Pivot table aggregation setting in the measure, dimension and column settings. All aggregations can be used with the numerical measures, dimensions and columns, and also the Count and Distinct count can be used with any type of measures, dimensions and columns.
  • Rows and columns are sorted based on the Sorting settings (in the General tab). If needed, use the custom sorting to get the desired sorting.
  • Data can also be sorted by a certain value column by clicking the column header. Sorting by values can also be stored to the dashboard, so that the values sorting is applied when the dashboard is opened. This is done by using the Custom layout settings (see the examples below for values sorting).
  • Appearance of the shown data points in rows, columns and values can be changed in the measure, dimension and column settings by using settings Round to decimals, Unit, Unit position and Date format.
  • The top left cell of the pivot grid shows the measure, dimension or column labels that have been selected as rows. If there are several measures/dimensions/columns as rows (i.e., there is a hierarchy), the labels are separated by the slash character ("/"). If you want to customize the labels, you can define a Custom label for the measure/dimension/column selected as rows. If you don't want to show the label at all, you can set a space character for the custom label.
  • Title for the pivot table can be set in the General tab.
  • Columns can be resized by dragging the border between column headers. 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 as...).
  • For advanced needs, the pivot table settings can be customized by using the Custom layout settings. 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 value cells background color and text color based on the shown values to improve the illustration. It's also possible to show a databar and icon in the value cells.

Change Column Widths

Width of the first column (contains the row headers) and widths of the value columns (rest of the columns) can be changed as follows: In the chart settings dialog Advanced tab, open the Chart settings (editable) and add the tableColumnWidths property to the existing json settings. Here is an example how does the tableColumnWidths property look:

{
  "tableColumnWidths": [ 200, 150 ],
  ...
}

The first value (200) defines the width of the first column (as pixels) and the seconds value (150) defines the widths of the value columns (as pixels). Note that as the number of value columns varies based on the data, width for each column cannot be set separately, but the second value affects all the value columns.

Customization Examples

Using the Custom Layout field (in the Visual tab), the pivot table functionality and visual appearance can be adjusted for custom needs. This chapter contains examples of common customizations.

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
  }
}