Aggregate grid and pivot grid

Do it

1. Part A — Open the aggregate grid

   With your raw report in the preview, click Visualizations and choose Aggregate Grid. Instead of a chart palette, you get a sidebar where every field is a card. Click a card's action menu and you'll see options the default grid never offered — set Column Width and Column Format right here, per column, no need to go back to the dataset level. (For a click-by-click version of everything below, the help center has How to create a new Aggregate Grid report.)

2. Group, then subtotal

   On the field you want to group by — category in the video — open the action menu and choose Group By. You can group by more than one column; one is plenty to start. Then open the Settings accordion and turn on Auto Expand Grouped Columns so the groups open by default instead of arriving collapsed.

   Now the subtotal. On your numeric card (amount), the action menu now shows the grouping-aware options — SubTotals and Grand Totals. Choose SubTotals → Sum, and while you're there set Column Format to Currency. Notice what just happened: things you used to set at the dataset level — the aggregate, the data type, the format — you now set inside the visualization.

3. Place the subtotal where you want it

   By default the sum prints in the group header, in parentheses next to the category name. The aggregate grid lets you move it. Back on the amount card:

   • Align by Column — set to false and the subtotal aligns underneath the actual amount column it's calculating, rather than riding up in the group heading.
   • In Group Footer — set to true and the subtotal drops to the bottom of each group instead of the top.

   These are presentation choices — pick whichever reads best for your report. The Heading Colors and Conditional Highlighting options (shade a row when a value crosses a threshold, or when two columns compare a certain way) live in the same sidebar; the full control list is in the options reference, and the color-rule walkthrough is here. Click Save & Apply.

4. See that the totals are real

   Open View Full Document and page through. Now do the thing that breaks the default grid: apply a filter to a couple of categories. The subtotals and grand totals recalculate to match what's left — because they're attached to the report, not painted on. Export respects the same filter. This is the difference the whole lesson turns on: your end users can slice the grid and the numbers stay honest.

5. Part B — From Simple Pivot to the Pivot Grid

   Reset back to the raw grid and let's look at pivoting. There are two layers, and it's easy to conflate them:

   • Data Prep → Pivot (Simple Pivot) is a transpose — a data-prep step that rearranges the report's own rows and columns. Pick one column, one row, one data field, and the data field defaults to a count. If that simple transpose is all you need, you're done; no visualization required.
   • The Pivot Grid visualization is the feature-rich version — and where this part of the lesson goes.

   The line between them matters enough that Data Prep tools spells it out: Simple Pivot reshapes the report data itself; the Pivot Grid is a rendered cross-tab widget with its own configuration, totals, and saved state. You use one or the other, not both on the same report.

6. Build the cross-tab

   Clear any Simple Pivot, then open Visualizations → Pivot Grid. You get a field chooser with areas for Rows, Columns, Data, and Filter. Drag your fields into place — the video puts category and subcategory into Columns (so you get multiple column expansions), name into Rows, and amount into Data. The preview builds as you drag.

   By default a data field counts. To make it useful, right-click the amount value and set its Summary Type to Sum, and its Format Type to Currency. You can drop multiple fields into Data; right-click any of them to hide it again. There are checkboxes for showing column/row totals and grand totals, for putting data-field headers in rows, and — a feature plenty of organizations need — Show Data As Text, which lets a pivot cell display text instead of a number. Click Save & Apply. (Click-by-click: How to configure a Pivot Grid report.)

7. Saving a pivot saves its state

   Open View Full Document. Right-click a column to expand it; click a dimension like Category to filter to just the values you want — the totals and grand totals adjust as you go. Here's the part to understand: when you save the pivot grid, you're saving the state it's in at that moment — which dimensions are expanded, which filters are set. Users open it in exactly that starting state, and from there they can keep expanding, filtering, and re-slicing live, with every numeric value recalculating dynamically. You're handing them a configured starting point, not a frozen picture.