Skip to main content

Pivot

Pivot mode turns the grid into a cross-tab: rows become the row-group tree, and the header is generated from data — one nested column group per distinct value of each pivot column, with one value column per aggregate underneath. Non-participating source columns are hidden while pivoted and return exactly when pivot mode turns off. Client-side row model only.

Pivot mode is a state layer, not a lens over the current state: the roles assigned while pivoted belong to the pivot view. Turning the mode off restores the grid exactly as it was before it turned on — grouping, measures and all — and turning it back on reinstates the pivot the user left rather than starting from scratch (see Entering and leaving the mode).

Loading interactive example…
React
<Grid
  rowData={rows}
  columnDefs={columns}
  toolbar={{ pivot: true }}
  columnPanel={{ trigger: "toolbar" }}
  groupDefaultExpanded={1}
  onGridReady={(api) => {
    api.setAggregates([{ colId: "revenue", type: AggregateType.SUM }]);
    api.setRowGroupColumns(["region"]);
    api.setPivotColumns(["status"]);
    api.setPivotMode(true);
  }}
/>

The three roles​

A pivot is configured from state the grid already has:

  • Row groups — the existing row-grouping model (rowGroupSet). With no row groups, the pivot shows a single grand-total row (with no roles at all it shows nothing — see the blank canvas).
  • Values — the existing aggregate model. Every aggregated column becomes a measure; a column may carry several aggregates (each is its own generated column, labelled Revenue (sum), Revenue (avg), …). The column menu's aggregate items are additive per-type toggles: checking Sum and Average on one column yields two measures. While pivoted with no measures at all, an inline header hint points at that menu.
  • Pivot columns — setPivotColumns(colIds), in level order. Two pivot columns nest their values in the generated header (2024 → Q1). With none, pivot mode shows a plain grouped-aggregate view (one generated column per measure).
api.setAggregates([{ colId: "revenue", type: AggregateType.SUM }]);
api.setRowGroupColumns(["region"]);
api.setPivotColumns(["quarter"]);
api.setPivotMode(true);

Everything is addressed by public colId — setRowGroupColumns and setPivotColumns take level-ordered colId arrays, and setAggregates takes { colId, type } entries (ColumnAggregate[]; a column may carry several types, and entry order sets the generated column order). getRowGroupColumns, getPivotColumns, and getAggregates read the same state back. The column panel, column menus, and toolbar are optional chrome over exactly this API.

Interactively, the column menu offers Pivot on Column / Add to Existing Pivot / Remove from Pivot (gated by the pivotable column flag, default true), and the toolbar's pivot section shows a mode indicator + toggle. Initial state can also be seeded with the pivotMode and pivotColumns grid options. All four pivot options (those two plus pivotResultColumnDef and maxPivotColumns) are React props and Angular inputs; pivotMode and pivotColumns are live — changing either reconciles through the API setters, so the mode can be driven declaratively instead of imperatively.

The blank canvas​

Pivot mode with no row group, no pivot column and no value displays nothing at all — no columns (not even the auto-group column) and no rows, just an empty state reading "Add row groups, column labels or values to build the pivot" (pivotEmptyMessage). That is the state a fresh pivot sheet opens on, and the state the toolbar toggle lands on when the grid had no roles configured.

api.isPivotUnconfigured() reports it, so an app driving pivot through its own UI can react to the same state the grid does.

Filling any one of the three roles ends it. A partly configured pivot is not blank: row groups alone show the group tree, a value alone shows the grand-total row, and a pivot column alone shows the group tree under the "no values" header hint — which is the affordance telling you what is still missing.

Where the grid's column panel is enabled, entering pivot mode with nothing configured opens it, since the blank canvas has no header to reach a column menu from.

Entering and leaving the mode​

The three roles are ordinary grid state, so the grid keeps two versions of them: the one the flat grid uses and the one the pivot view uses. setPivotMode swaps between them.

  • Turning the mode on stashes the current row groups, measures, pivot columns, generated-column arrangement and aggregate scope, then reinstates whatever the last pivot session ended with (nothing, the first time).
  • Turning it off stashes the pivot view's own version of all of that and restores what was in force before the mode turned on. Nothing configured for the pivot view leaks into the flat grid: group by Region inside pivot mode and the flat grid is ungrouped again when you leave. Sorts on generated columns leave with the columns that owned them.
  • Pivot mode entered from the pivotMode grid option has no earlier state to return to, so leaving it clears the roles entirely.

Filters and the quick filter are deliberately not layered — they select source rows, which means the same thing in both modes.

Every exit path behaves identically: the toolbar toggle, the auto-group column menu's Exit Pivot Mode, and api.setPivotMode(false).

api.setPivotMode(false); // flat grid, exactly as it was before pivot
api.setPivotMode(true);  // the same pivot the user left

captureViewState() carries the inactive layer too (prePivotState while pivoted, pivotState while not), so a restored view — a saved view, or a sheet switch — toggles the mode exactly like the live grid.

Generated columns​

Generated value columns are ordinary columns for reading purposes — resizable and sortable (sorting one orders the group rows by that cell's aggregate at every level) — but read-only, unpinnable, and unfilterable. Their ids are stable (derived from the pivot-value path, source column, and aggregate), so widths and sorts survive data refreshes that re-derive the same columns.

Generated columns are still draggable — what a drag means is chosen by pivotColumnMoveMode:

  • "measures" (default) — dropping a value column reorders the measures: leaf order inside every generated group is the value-entry order, so one drag reorders all groups consistently, and the new order persists everywhere (it is the aggregate model's order — the same order setAggregates and the column panel's Values well control). A value column never leaves its group; group headers stay in pivotComparator order.
  • "free" — value columns and whole generated groups arrange per position: a leaf can sit anywhere in the pivot area, carrying a duplicated group caption with it (the same split-and-carry behavior source column groups have), and dragging a group header reorders the pivot values themselves. The arrangement is a leaf-order list (setPivotColumnOrder(colIds) / getPivotColumnOrder(), captured in view state so sheets keep per-sheet arrangements). It survives data- and filter-driven re-discoveries — newly discovered columns slot in at their canonical position — and resets to the canonical layout on any explicit role edit (setAggregates, setPivotColumns, and the menus/wells that call them).

The mode is updatable at runtime (updateGridOptions({ pivotColumnMoveMode }), or the React prop / Angular input). In both modes generated columns stay unpinnable and confined to the pivot area.

  • pivotResultColumnDef styles every generated value column (width, cell class, formatter) without being able to unlock the grid-owned behavior.

  • pivotComparator on a pivot column orders its distinct values across the header (default: the column's sort comparator, ascending; blanks last).

  • maxPivotColumns (default 200) caps the generated columns; past it the discovery truncates deterministically and fires pivotColumnLimitReached. The cap is a target rather than an exact ceiling, because truncation is per pivot value: a value is kept or dropped with all of its measures, so the generated count is a whole multiple of the measure count. Three measures against a cap of 200 keep ⌊200 / 3⌋ = 66 values — 198 columns, under the cap — while at least one value always survives, so five measures against a cap of three generate five columns, over it.

    The grid shows no built-in truncation notice — that UI is the application's, driven by the event. It is latched so one subscription can drive a notice through its whole life: it fires when truncation starts, when the truncated count changes, and once more when it ends (including on leaving pivot mode) — never repeatedly for re-derivations that change nothing. The payload is { limited, truncatedColumnCount, maxPivotColumns }; limited: false means "back under the limit, take the notice down".

  • pivotNoValuesMessage rewords the inline header hint shown while pivot mode is on with no aggregates chosen (default "Choose Aggregate on a column to add values") — for different wording, a different entry point, or another language.

const options = {
  pivotResultColumnDef: { width: 110 },
  maxPivotColumns: 120,
  columnDefs: [
    { key: "quarter", label: "Quarter", pivotComparator: (a, b) => quarterRank(a) - quarterRank(b) },
    // ...
  ],
} satisfies GridOptions;

The column panel is the pivot setup​

Enable the column panel alongside pivot mode and it becomes the place to edit the three roles. columnPanel: { availability: "pivot" } mounts it only while pivoted, for apps that manage columns their own way but want the grid's pivot customizer. Either way, entering pivot mode with nothing configured opens the drawer. Outside pivot mode every column row shows removable role chips — Group 1, Pivot 1, and one chip per aggregate type (Sum, Average, …) — so the pivot recipe stays readable from the flat grid.

While pivot mode is on, the panel is the pivot setup and nothing else: three field wells — Row groups, Column labels, and Values — listing the roles in level order.

  • Each entry moves up/down or drags within its own well (level order matters for groups and pivot columns; value order sets the generated column order), and removes with its ×. A drag never crosses between wells: the roles hold different things — Values holds column-and-aggregate pairs, not columns — so role changes stay with the + and × buttons.
  • Each well's + menu adds any eligible source column; values pick an aggregate type from a nested menu.
  • Clicking a Values row opens its function picker — the aggregate types that column admits, its own one checked — and picking one retypes that entry in place. (Unlike the column menu's per-type toggles, which add a second measure alongside the first.) Retyping onto a type the column already carries merges the two entries, keeping the clicked row's position.
  • The column list itself is gone while pivoted, along with search, the bulk visibility toggle and the layout reset. The displayed columns are generated, so the listed source columns had no visible layout for those controls to act on. Everything returns exactly when pivot mode turns off.

Removing the last pivot column from its well keeps pivot mode on — that is the grouped-aggregate view; the mode switch stays with the toolbar indicator and the column menu. Everything the panel edits is per-sheet state, so with sheets enabled the panel always edits the active sheet in place.

Live behavior​

Filters and the quick filter keep operating on source rows, pre-pivot. Cell edits (setCellValue, transactions) re-derive the affected pivot cells immediately — the pivot is always live against the shared row model. The footer aggregate row shows grand totals per generated column, computed from source rows (an average of averages can never appear).

captureViewState() includes pivotMode, pivotColumns, and the aggregate model, so a saved view restores the complete pivot — plus the inactive state layer described above; pivotChanged reports mode/column changes and getPivotResultColumns() describes the generated columns.

The cell cursor, keyboard navigation, and range selection work on pivot rows without any configuration — while pivoted the group rows are the data, so they are always cell-selectable (groupRowsSelectable keeps governing group rows in ordinary grouped views, and row-checkability everywhere). Copying a pivot range puts the formatted aggregate values on the clipboard, with group labels as key (count) under the group column. Editing stays blocked — pivot cells are derived.

Exporting while pivoted (toolbar or API, CSV and Excel) writes the pivot table itself: the nested generated header over one row per group node (every level, indented), with real numbers in the aggregate cells.

Pivot mode is mutually exclusive with tree data, and pairs naturally with row grouping, whose group tree it reuses. For Data + pivot views as coexisting tabs over one row model, see sheets.