QPR ProcessAnalyzer Pivot Table: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 3: Line 3:
== 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.
* 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.
* The first dimension is linked to the pivot table rows and the second dimension is linked to the columns.
* The first dimension is shown as rows in the pivot table and the second dimension is shown as columns.
* The third dimension is linked to the rows as a sub level, and the fourth linked to the columns as a sub level (the sub levels can be expanded from the arrow icon).
* 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).
* Rows and columns in the pivot table are sorted based on the ''Sorting'' settings (in the ''General'' tab). If needed, use the custom sorting to define all column sortings.
* 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 on ad-hoc basis by clicking the column headers (this sorting is not stored to the dashboard).
* 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).
* Shown data appearance can be changed in the measure and dimension settings, using following settings: Round to decimals, Measure unit, Data type, and Date format.
* 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'''.
* Measures in the pivot table are aggregated to the ''Grand Total'' column. The aggregation is determined automatically from the measure settings (e.g. aggregation used there). If needed, override the aggregation with the desired one (see the configuration examples). Available aggregations are: '''Sum''', '''Min''', '''Max''', '''Avg''', '''Product''', '''Count''', '''DistinctCount''', '''PopulationStDev''', '''SampleStDev''', '''PopulationVar''' and '''SampleVar'''.
* 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''.
* Pivot table can be exported as Excel, pdf and csv (open settings,  ''General'' tab and ''Export Table as...'').
* Pivot table can be exported as Excel, pdf and csv (open settings,  ''General'' tab and ''Export Table as...'').


Line 15: Line 15:


===Change Aggregation Method===
===Change Aggregation Method===
This example sets the aggregation method for the first measure to sum and the second measure to average.
This example sets the aggregation method for the first measure to sum and for the second measure to average.
<pre>
<pre>
{
{
Line 43: Line 43:


=== Sort by Measure Values ===
=== Sort by Measure Values ===
It's also possible to sort the pivot grid rows using a certain measure from a certain column. If there is only one measure, the following settings can be used:
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:
<pre>
<pre>
{
{
Line 55: Line 55:
</pre>
</pre>


If there are several measures, the individual measure need to be specified as follows:
If there are several measures, the measure used for sorting need to be specified (in addition to the column dimension value) as follows:
<pre>
<pre>
{
{
Line 68: Line 68:
</pre>
</pre>


=== Expand All by Default ===
=== Hierarchy Expanded ===
By default, the second level in the rows or columns hierarchy is collapsed. Use this setting to expand the hierarchies by default:
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
    "expandAll": true
}
}
</pre>
</pre>


=== Show Tooltip ===
=== Show Tooltip ===
The cell tooltip can be shown as follows:
<pre>
<pre>
{
{
Line 83: Line 84:
}
}
</pre>
</pre>
[[Category: QPR ProcessAnalyzer]]
[[Category: QPR ProcessAnalyzer]]

Revision as of 22:04, 1 September 2020

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 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.
  • The first dimension is shown as rows in the pivot table and the second dimension is shown as columns.
  • 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).
  • 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 measure column by clicking the column headers. By default, this sorting is not stored to the dashboard (see the examples for storing measure sorting).
  • 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.
  • 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.
  • Pivot table can be exported as Excel, pdf and csv (open settings, General tab and Export Table as...).

Examples

Change Aggregation Method

This example sets the aggregation method for the first measure to sum and for the second measure to average.

{
  "dataSourceSettings": {
    "values": [
      {
        "type": "Sum"
      },
      {
        "type": "Avg"
      }
    ]
  }
}

Hide Grand Totals

Grand totals can be hidden separately for rows and columns.

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

Sort 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:

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

If there are several measures, the measure used for sorting need to be specified (in addition to the column dimension value) as follows:

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

Hierarchy Expanded

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
}

Show Tooltip

The cell tooltip can be shown as follows:

{
  "showTooltip": true
}