Pivot

The Pivot dashboard item displays a cross-tabular report that presents multi-dimensional data in an easy-to-read format.

Providing Data

The Dashboard Designer allows you to bind various dashboard items to data in a virtually uniform manner.

The only difference is in the data sections that the required dashboard item has. This topic describes how to bind a Pivot dashboard item to data in the Designer.

Binding to Data in the Designer

The image below shows a sample Pivot dashboard item that is bound to data.

To bind the Pivot dashboard item to data, drag and drop a data source field to a placeholder contained in one of the available data sections. A table below lists and describes a Pivot's data sections.

Section	Description
Values	Contains Data Items used to calculate values displayed in the pivot table.
Columns	Contains Data Items whose values are used to label columns.
Rows	Contains Data Items whose values are used to label rows.

Transposing Columns and Rows

The Pivot dashboard item provides the capability to transpose pivot columns and rows. In this case, Data Items contained in the Columns section are moved to the Rows section and vice versa.

To transpose the selected Pivot dashboard item, use the Transpose button in the Home ribbon tab.

Interactivity

This document describes the features that enable interaction between the Pivot and other dashboard items. These features include Master Filtering.

Master Filtering

The Dashboard allows you to use any data-aware dashboard item as a filter for other dashboard items (Master Filter).

Data displayed in the Pivot dashboard item can be filtered by other master filter items. You can prevent the pivot from being affected by other master filter items using the Ignore Master Filters button on the Data Ribbon tab.

Conditional Formatting

The Pivot dashboard item supports the conditional formatting feature that provides the capability to apply formatting to cells whose values meet the specified condition. This feature allows you to highlight specific cells or entire rows/columns using a predefined set of rules.

Conditional Formatting Overview

The Pivot dashboard item allows you to use conditional formatting to measures placed in the Values section and dimensions placed in the Columns/Rows sections. Note that you can use hidden measures to specify a condition used to apply formatting to visible values.

Note that you can use hidden measures to specify a condition used to apply formatting to visible values.

New appearance settings are applied to pivot data cell or cells corresponding to column/row field values.

Create a Format Rule

To create a new format rule for the Pivot's dimension/measure, do one of the following:

• Click the Options button next to the required measure/dimension, select Add Format Rule and choose the condition. Use the Edit Rules dialog.

• Depending on the selected format condition, the dialog used to create a format rule for Pivot contains different settings. For instance, the image below displays the Greater Than dialog invoked for the measure.

This dialog contains the following settings specific to Pivot.

Intersection mode specifies the level on which to apply conditional formatting to pivot cells. The following levels are supported:

• Auto: Identifies the default level. For the Pivot dashboard item, Auto identifies the First Level.

• First Level: First level values are used to apply conditional formatting.

• Last Level: The last level values are used to apply conditional formatting.

• All Levels: All pivot data cells are used to apply conditional formatting.

• Specific Level: Values from the specific level are used to apply conditional formatting.

If you specified the Intersection mode as Specific Level, use the Row dimension and Column dimension combo boxes to set the specific level.

The Apply to row and Apply to column check boxes allow you to specify whether to apply the formatting to the entire pivot row/column.

If you are creating a new format rule for the dimension from the Column/Rows section, the corresponding format condition dialog would not contain any Pivot specific settings.

Edit a Format Rule

To edit format rules for the current Grid dashboard item, use the following options:

• Click the Edit Rules button in the Home ribbon tab or use corresponding item in the Pivot context menu. Click the menu button or the required data item and select Edit Rules.

• All of these actions invoke the Edit Rules dialog containing existing format rules.

Layout

This topic describes how to control the Pivot dashboard item layout, the visibility of totals and grand totals, etc.

Layout Type

If the Pivot dashboard item contains a hierarchy of dimensions in the Rows section, you can specify the layout used to arrange values corresponding to individual groups.

Layout Type	Example	Description
Compact		Displays values from different Row dimensions in a single column. Note that in this case totals are shown at the top of a group, and you cannot change totals position.
Tabular		Displays values from different Row dimensions in separate columns.

Use the Layout button in the Design ribbon tab to change the Pivot layout.

Totals Visibility

You can control the visibility of totals and grand totals for the entire Pivot dashboard item. For instance, the image below displays the Pivot dashboard item with the disabled row totals.

To manage the visibility of totals and grand totals, use the Totals and Grand Totals buttons in the Design ribbon tab, respectively.

These buttons invoke a popup menu that allows you to manage the visibility of column and row totals/grand totals separately.

Moreover, you can control the visibility of totals for individual dimensions/measures by using the data item's context menu (Show Totals and Show Grand Totals options).

Totals Position

If necessary, you can change the Pivot dashboard items totals/grand totals position. For instance, in the image below the row totals are moved from the bottom to the top.

To manage totals position, use the Row Totals Position and Column Totals Position buttons in the Design ribbon tab.

Values Visibility

The Pivot dashboard item can contain several measures in the Values section to hide summary values corresponding to specific measures. For instance, the image below shows the Pivot with hidden Quantity values.

To do this, use the Show Values command in the measure menu.

Values Position

The Pivot dashboard item allows you to control the position of headers used to arrange summary values corresponding to different measures. For instance, you can display values in columns or rows.

To manage this position, use the Values Position button in the Design ribbon tab.

Rest Layout Options

To reset layout options, click the Reset Layout Options button in the Design ribbon tab.

Expanded State

If the Columns and Rows section contains several Data Items, the Pivot column and row headers are arranged in a hierarchy and make up column and row groups.

You can collapse and expand row and column groups using the arrow buttons. However, the current expanded state of column and row groups do not save in the dashboard definition. If necessary, you can specify the default expanded state using the Initial State button in the Design ribbon tab.

This button invokes the pop-up menu that allows you to select whether column and row groups should be collapsed or expanded by default.