Overview
Pivots offer data visualization in a hierarchical grid.
Data can be added to the pivot, nested into a hierarchy, formatted and configured to interact with other Dashboard Items.
Adding Data
Data fields are bound to the Pivot by dragging them to the Data Items Pane as a Value, Column or Row.
Additionally, Hidden Data Items can be used for additional configurations, such as filtering, without displaying the field in the Pivot.
Working with Pivot Data
Each Dashboard Item has different requirements for the data you add to it. Pivots use Values, Columns and Rows.
| • | Values - Used to calculate data (e.g. the Sum of all Hours) |
| • | Columns - Used to label the grid columns |
| • | Rows - Used to label grid rows |
For example, to view the total hours for each project by year, add:
Values
|
Columns
|
Rows
|
Hours (Sum)
|
Work Date (Year)
|
Project Name
|
Columns can be further broken out by adding additional fields to Values.
Values
|
Columns
|
Rows
|
Hours (Sum)
|
Work Date (Year)
|
Project Name
|
Bill_Dollars (Sum)
|
|
|
Hierarchy can be added to the pivot by binding additional fields to Columns or Rows. Once hierarchy is added, the pivot will display expand/collapse icons which can be used to view or hide the added hierarchy.
Values
|
Columns
|
Rows
|
Hours (Sum)
|
Work Date (Year)
|
Project Name
|
Bill_Dollars (Sum)
|
Work Date (Month)
|
Employee Name
|
TIP: If you’ve added data to the Pivot but don’t see it reflected, click the Refresh button above the Field List in the Data Browser (left-hand pane of the designer).
Conditional Formatting
Once data is added to the pivot, it can be formatted in a variety of ways to help visualize otherwise static data points, highlighting cells that meet certain defined criteria.
Pivots support conditional formatting on Values, Columns and Rows.
Formatting can be added by clicking Edit Rules from the toolbar or by clicking Options>Add Format Rule from the data item (Options Button- down arrow - becomes visible when hovering over the data field in the Data Items pane).
For example, to color a cell green if the sum of hours exceeds 100 hours on a given project:
| 1. | Hover over the Hours field |
| 2. | Click the Options button (down arrow) |
| 3. | Select Add Format Rule>Value>Greater Than. The Greater Than dialogue will appear. |
| 4. | Enter 100 in the <enter a value> text box |
| 5. | Select green to set the appearance. Note, you could also choose to add an Icon to the cell by selecting the Icon tab. |
| 6. | Use the Auto Intersection Mode. |
| • | Intersection Mode defines the level at which the condition should be applied. Auto will typically default to the highest level of hierarchy. If selecting Specific level, you can then specify a level based on Row or Column to apply the conditional formatting to. |
| 7. | Use Hours as the Apply to. While this defaults to the selected field, you can add the formatting to the cell of your choosing. |
| 8. | Apply to Row/Column can be left unchecked. Checking either will apply the format to the entire column/row in which the evaluated cell resides. |
| 9. | Click Apply to preview results |
Pivot Tools
Pivots feature a specific set of design and data tools, available from the toolbar (some options are also available by right-clicking the pivot).
Data
The Data Tab includes several options for shaping your data.
| • | Edit Filter - Use to add filters to the pivot based on displayed or Hidden Data Items. |
| • | Ignore Master Filter - Pivot Grids can interact with other dashboard items marked as a Master Filter (e.g. data displayed on the pivot is filtered by, for instance, a bar chart). Clicking Ignore Master Filter removes interactivity. |
| • | Initial State - Sets the default state of grid hierarchy (expanded or not). |
| • | Totals - Shows/Hides row and/or column totals |
| • | Grand Totals - Shows/Hides grand totals |
| • | Font - Adjust font options for the data results in the pivot |
Design
Pivots allow the following design options.
| • | Show Caption - Shows/Hides the Pivot caption (e.g. “Pivot 1” displayed at the top left of the pivot). |
| • | Edit Names - Used to configure display name for the Dashboard Item and Values |
|