Pivot Tables

A pivot table summarizes records into a grid. You choose the fields for the rows and the columns, and each cell shows a calculated value for the records that fall into it. It is the right choice for cross-tabulating data, for example revenue by Region down the side and by Quarter across the top.

For choosing the source table and using dynamic filters to automate reports, see the Report Automation guide.

Add a pivot table

1. Type / in the editor and choose Pivot Table. It appears with sample data.
2. Click the table. The Properties panel shows two tabs: Configuration and Design & Data.

Set the data

On the Configuration tab:

1. Under Source, pick the Base, the Table, and optionally a View.
2. Under Row Grouping, click + Add row and choose a field, such as Region. Add more than one to nest the rows, such as Region then City.
3. Under Column Dimensions, click + Add column and choose a field, such as Quarter. Each value becomes a column.
4. Under Value Fields, choose what each cell calculates. It reads like a sentence: Sum of Amount, labelled Revenue. Click + Add value for more than one metric, and set a display format if you want.

Formulas

A value field can be a formula instead of a plain aggregation. In the value sentence, set the function to Formula and type an expression, such as Revenue - Cost. A formula is calculated for each cell and can read the other value fields in that cell.

Reference a value field by its label. If the label has spaces, replace them with underscores, so Total Revenue becomes Total_Revenue.

There are three kinds of reference you can use:

Reference	Reads	Example
Label	another value in the same cell	Revenue - Cost
PrevRow_Label	the same value in the previous row	Revenue - PrevRow_Revenue
PrevCol_Label	the same value in the previous column	Revenue - PrevCol_Revenue

The previous row is the one above along the row grouping, and the previous column is the one to its left. These are useful for change and growth columns, for example month-over-month growth with (Revenue - PrevCol_Revenue) / PrevCol_Revenue.

note

The underscore rule applies to the prefixed references too. A value labelled Total Revenue is read as Total_Revenue, PrevRow_Total_Revenue, or PrevCol_Total_Revenue.

Totals and style

On the Design & Data tab:

• Sub groups: show a sub-group total for each row group and a grand total for the whole table, and add space after each group.
• Border: choose None, Full, Row Only, or Header Only, then set the width and color.
• Color: set the header, row, and alternate row colors.

Font family and size come from the toolbar while the pivot table is selected.

note

The editor fills the table with sample data. Turn on preview to see it calculated from your real records.