PIVOT TABLES
Pivot tables
Summarize source records with several row levels, optional column groups, and one or more calculated values.
Set up a pivot table
Configure the Simple Table macro in Settings → Pivot. The source rows stay intact; the pivot changes how the table is displayed.
- 1
Enable pivot table
Turn on Enable pivot table. Add at least one Rows field and one Values entry to complete the setup. Columns can remain empty for a row-only summary.
- 2
Choose Rows and Columns
Add several fields to Rows or Columns when you need levels of detail. Their order sets the hierarchy. Use the move controls to reorder a field and the delete control to remove it.
- 3
Add Values
Add one or more Values entries. Each has its own source field and calculation: Count, Sum, Average, Max, or Min. Count can leave the source field empty; the other calculations need a numeric field.
- 4
Review Table options
Row totals add totals at the right when Columns are configured. Column totals add summary rows below row groups and at the bottom. Hide empty columns removes pivot value columns whose results are all empty.
- 5
Compare and save
At the lower right of Preview, switch between Pivot table and Original table to compare the summary with its source records. This preview choice does not change the saved pivot configuration. Submit the macro and publish the page to show the pivot.
Tip
Start with one Rows field and Count, then add more levels or calculations as needed.
Choose a calculation for each value
Each Values entry is calculated separately for every row and column group.
| Calculation | What it does | Example |
|---|---|---|
| Count | Counts source records; the value field is optional | Number of tickets per project |
| Sum | Adds numeric values | Hours worked per project and date |
| Average | Averages available numeric values | Average resolution time per team |
| Min / Max | Finds the lowest or highest numeric value | Lowest and highest cost per region |
Table options and empty values
These controls affect the pivot result, not the source data.
Row totals
Show a total to the right of each row when column groups are present.
Column totals
Show subtotal rows below row groups and a grand total at the bottom.
Hide empty columns
Hide a pivot value column only when every result in it is empty. A zero is a value, so columns containing 0 remain visible.
For Sum, Average, Min, and Max, a null or missing numeric value produces an empty result when no numeric value contributes to that cell. Zero remains 0 and participates in calculations.
Example: project hours by date
Suppose each record has Project, Date, and Hours fields.
- Original table
- Each project and date is a separate record. Some Hours values are null and some are 0.
- Pivot table
- Use Project for Rows, Date for Columns, and Sum of Hours for Values. Empty results remain blank; 0 appears as 0. Hide empty columns can remove dates where every summed result is empty, while dates with zeros remain visible.