Adding a Pivot Table
The Pivot Table editor window allows you to specify the appearance and behaviour of pivot tables on your dashboard tabs. A pivot table is a tabular representation of data that can automatically sort, count, total or give the average of data quickly creating un-weighted cross tabulations.
The Pivot Table editor window consists of the following tabs;
Allows you to define the data that will populate the pivot table.
Total and Differences
Allows you to define the appearance and behaviour of total and difference rows and columns for the pivot table.
Allows you to specify settings that will govern the appearance of the pivot table.
Allows you to specify the numeric format used to display numbers in the dashboard object
Allows you to add and define a drill down for the pivot table
Defining the data for a Pivot Table
Follow the steps below to specify the data;
- First select the Query data set that you wish to base your dashboard object
- Select the calendar you wish to be used to derive any date groupings (such as year, period, week etc).
- If you wish to, press the filter button to apply a filter to your data set. Next, decide how you want to group your data, and select the appropriate option;
- Next, add dimensions () to the row and column groupings you require. You can do this by selecting the appropriate available column and pressing the add button to the left of the row(s) list or the add button to the left of the column(s) list, or simply dragging columns from the available columns list to the row(s) list or column(s) list.
- If you wish to, you can change the way group values are grouped, sorted and displayed by selecting the group options button to open the group options window.
- Next, select which metrics you require as summary columns.
- For each summary column, select the summary operation you wish to be applied. Distinct count is the number of distinct values in the column for the group selected. If you select distinct sum, you will need to select a dimension to define the distinct values. Finally, if the column is calculated, rather than existing in the dataset, you will only be offered the summary type calculated.
Defining totals and differences for a pivot table
The totals and differences tab of the pivot table dialog allows you to define the appearance and behaviour of total and difference rows and columns for the pivot table.
Follow these steps to define the appearance totals for the pivot table:
- Check the appropriate checkboxes to display row and/or column totals as required.
- If required, change the label of the total row and columns.
Difference columns display the difference between a column and the preceding column. Follow these steps to show difference columns:
- Select a column
- Select the type of difference you want to show. The options are;
No differences are shown
The actual difference value is shown.
The percentage difference is shown.
Defining the appearance for a Pivot Table
The Pivot Table editor window Appearance tab allows you to specify settings that will govern the appearance of the pivot table.
The available options are:
- Enter a title for the pivot table.
Defining the Numeric Format for a Pivot Table
The Pivot Table editor window Numeric Format tab allows you to specify settings that will govern the formatting of numeric information of the pivot table.
You can specify a different numeric format for each summary value in the pivot table. As such, you need to select which summary you are specifying the details for.
Next, select the maximum number of decimal places you want to show. If your values are currency values, select force trailing zeros.
Finally, if you require it, select a currency symbol (value that will be displayed before your numeric values). If the value you want is not in the list, select other and enter your desired value. You can also enter a number suffix, a string that will be displayed after numeric values.