A pivot table’s toolbox is shown to the left.
Its display is controlled by the Show/hide Toolbox button. It is shown automatically when a chart is created or opened.
It exposes a number of Options:
•Row Attributes: you must specify at least one Row attribute to display a pivot table. You can add row attributes by dragging one or more attributes from the toolbox and dropping them onto the property. You cannot add a parameter, model project or exists in table attribute. A maximum of four row attributes can be added to the pivot table. If the total maximum number of attributes – eight – have already been added across the pivot table’s three lists, an informational message is displayed and any excess attributes are not added.
Having added an attribute to the Rows or Columns list, a series of buttons are displayed on hover:
o View file information...: clicking this button displays details of the attribute in the File Information Dialog.
o Move attribute up: this button is only available if the attribute is not the first in the list. Clicking it moves the attribute up one position within the list.
o Move attribute down: this button is only available if the attribute is not the last in the list. Clicking it moves the attribute down one position within the list.
o Remove attribute: removes the attribute from the pivot table without displaying an ‘Are You Sure?’ dialog.
•Column Attributes: you must specify at least one Column attribute to display a pivot table. For more information on Column attributes, please see the Row Attributes documentation.
•Function attribute: this optional property defines an attribute that can be aggregated and analyzed in the context of cells within the pivot table’s results grid. You can browse for an attribute using the recent items chooser or File System Dialog, or you can provide one using drag and drop. Once provided, you can clear the selected attribute, or invoke View Information... to view its details in the File Information dialog. The attribute selected must be numeric, cannot be same as one of the Row or Column attributes, cannot be a model project, exists in table or parameter attribute, and cannot be a function attribute configured with a constant value. The attribute selected must be sourced from the same database as the panel’s resolution level.
•Filter: this optional property allows you to provide a selection rule that will be executed at the point of the pivot table’s being refreshed, thereby limiting the cell counts therein to include just records that match the selected filter’s criteria. A Filter is initially not provided, with a message (‘Using all records’) being shown instead. You can browse for a selection rule with which to configure the property, or you can provide one using drag and drop. You can also initiate the creation of a new selection rule to serve as the pivot table’s Filter. Once a Filter has been provided, you can open its latest version in the Rule Designer or clear it if required. Note that the resolution level of the chosen selection rule must be from the same database as the panel’s resolution level.
When a Filter has been applied, it is displayed within the panel:
•Aggregates: this property allows you to control the type of count values displayed at cells within the pivot table results. A list of available aggregates is displayed when a Row attribute or Column attribute are provided. Each is accompanied by a checkbox.
If a Function attribute is not provided, only a single Count aggregate is available, and is checked by default. In the absence of a Function attribute, a pivot table can only display simple counts of the intersects between Row and Column attribute values.
If a Function attribute is provided, the following Aggregates are available:
o Count: the Function attribute is ignored, and a simple count of the intersection of attribute values is provided per cell.
o Minimum (the minimum Function attribute value per cell)
o Maximum (the maximum Function attribute value per cell)
o Sum (the sum of all Function attribute values per cell); always available
o Average (the average of all Function attribute values per cell)
At least one aggregate must be checked prior to invocation of Refresh.
Note that, if only the Average aggregate is selected, on refreshing the pivot table, the Sum aggregate will also be available (due to sums being calculated as part of average calculation during pivot table refresh).
•Heatmap: you can apply a colored heatmap to pivot table results . By default, a new pivot table’s Heatmap is set to Off. You can click the property to display available heatmap options using the dropdown:
Upon selecting a heatmap color range, the selected color gradient is applied at pivot table results:
Count values are not shown when a heatmap applied, unless a cell is hovered over.
The selected Heatmap setting is persisted along with the analysis panel and applied each time results are refreshed. The lowest value takes the leftmost gradient color, and the highest value takes the rightmost gradient color. Any change to the heatmap setting is applied immediately to displayed pivot table results. Coloration is removed if the Heatmap setting is set to Off. Cells with no data are displayed in white.
Note that the configuration panel is read-only when a pivot table panel is refreshing.