Pivot Table

A pivot table is a table of statistics that summarizes the data of a more extensive table. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

The following is a list of the fields in the View Properties box in the dashboard designer for pivot table. There are some required fields to be set for each created view.

Basic Properties

The View Properties fields vary in different charts. Below are the basic properties in the View Properties box.

Field Description Mandatory
View Type Type of chart, table or view. Yes
Title Name of the view. No
Dataset Data used for the chart, table or view. Yes
Subset Partial data from a dataset used for the chart, table or view. No
Columns Defines the fields for the columns and their labels.
More than one field can be added.
Rows Defines the fields for the rows and their labels.
More than one field can be added.
Measures Defines the field as the values in the cells and their properties.
More than one field can be added.

The dataset and subset data derive from Datasets module. Choose the right chart from the View Type and data points to build a view.

Other Properties

The other property fields are available to enhance the view. Below are the other properties in the View Properties box for the pivot table.

Field Description Default
Controlled By Links dashboard controls to the view.
One or more controls can be added to affect the data displayed on the view.
Select to Control Allows a selection made in the view to set the value of a control. Other views which are dependent on that control will then update accordingly. Empty
Expand If selected, expands all rows and columns (if more than one row and/or columns are defined). Selected
Collapse Single Totals If selected, removes total column from table if there exist only one value column. Selected
Column Options Allows show or hide root and totals in the columns, as well as renaming root and total. Default settings
Row Options Allows show or hide labels, root and totals in the rows, as well as renaming root and total. Default settings
Measures Vertical If selected, the “Measures” property labels are displayed vertically.
By default, the labels are displayed horizontally.
Not selected
Show Measure Labels If selected, displays the name of the field selected in “Measures” property. Selected
Show Export XLSX If selected, displays the export XLSX icon on the top left of the view. Not selected
Maximisable If selected, places a maximise button on the top right of the view (when not in edit mode).
This will maximise the view to fill the browser.
Not selected
Advanced Add a special CSS class to your view. Empty

Example 1: Using “Measures” Property

You can use the “Measures” property to choose the type of information to be displayed in the table cell.

In the “Measures” property, click on the “Properties” icon next to the desired field to display the “Measure Properties” dialog box.

You can customise and select different types of operation in the dialog box. The table below summarises the usage on the fields in the dialog box.

Field Description Default
Operation Types of operation.
The options are count, sum, average, minimum and maximum.
Label Name of the measure.
By default, it is undefined (empty), therefore the name of the selected field is used.
Alignment Alignment of the value.
The options are left, centre and right.
Suffix Defines the suffix to be added to the value.
For example, the dollar sign $.
Format Selects the format of the value.
For example, if the value is currency, select 0.00.
Exponent Format Selects the exponent format of the value.
The options are exponent, SI and B.
Calculation Mode Defines the calculation mode of the value.
The options are normal, ratio of row and ratio of column.

Example 2: Using “Select to Control”

In the pivot table, you can select “Pivot Filter” in the “Select to Control” property to control the content of other views.

In this example, a pivot table and a linear table is created. Both uses the same dataset. Create the “Pivot Filter” to use the same dataset. In the pivot table, select the “Pivot Filter” in the “Select to Control” property.

In the linear table, select the same filter in the “Controlled By” property. This will allow the pivot table to control the data to be displayed in the linear table.

Select any value in the cell in the pivot table. In this example, “Fruits” and “Asia” is selected. If the “Visibility” property in the “Pivot Filter” is set to “Always”, you will see your selection in the Filter Control Panel.

At the same time, the header row of the pivot table displays the selected item and the linear table now shows only the items from “Asia” and “Fruits”.

To reset the selection, click on the “Close” icon on the header row.

Example 2: Using Sort Field in Rows and Columns Properties

The rows and columns of the pivot table is by default listed according to the order in the dataset, which can be difficult to read at times. You can sort the rows and/or columns of the table by using the “Sort” field in their properties.

In the example below, the “DOW” rows are not sorted according to days which make it hard to read.

To sort the days, in the Row Properties, select a field that corresponds 1:1 to the “DOW” field. In this case, “DOWAsc” is selected to sort the days from “Monday” to “Sunday”.

Click on the “OK” button to save the change. The table is now sorted according to days, which is easier to read.

The columns can be sorted in the same manner as well.