Pivoting (Enterprise)

Pivoting in apex-grid-enterprise reshapes flat rows into a cross-tab: one column's distinct values become columns, one or more fields become row dimensions, and measures are aggregated into each cell.

The three pivot inputs

Property	Type	Role
pivotOn	string | string[]	Column-dimension field(s), whose distinct values become columns
pivotRows	string[]	Row-dimension field(s), one leading column each
pivotValues	AggregationConfig	Measures aggregated into each cell, e.g. { salary: ['sum'] }

import 'apex-grid-enterprise/define';

const grid = document.createElement('apex-grid-enterprise');
grid.columns = columns;
grid.data = data;

grid.pivotOn = 'quarter';                 // Q1, Q2, Q3, Q4 become columns
grid.pivotRows = ['region'];              // one row per region
grid.pivotValues = { revenue: ['sum'] };  // each cell = sum of revenue

document.body.appendChild(grid);

This produces a matrix of region (rows) × quarter (columns), each cell holding the summed revenue.

From 0.8 pivoting on several fields creates columns only for value combinations that occur, ordered by value (numbers and dates in order, then text with numeric collation, blanks last). Row dimension and measure headers take the source column's headerText, with the long localized function name, so salary (avg) reads Salary (Average); code or tests that match pivot header text need updating. An empty intersection shows a blank cell for avg, min and max, while count and sum stay 0.

Multiple row dimensions

grid.pivotRows = ['region', 'country'];   // nested row dimensions

Multiple measures

Each measure/function pair becomes its own set of value columns under each pivot column:

grid.pivotValues = {
  revenue: ['sum', 'avg'],
  units: ['sum'],
};

pivotValues uses the same AggregationConfig shape as aggregations.

Multiple column dimensions

pivotOn also takes an array, and the resulting columns are grouped by the outer dimension using the grid's own column groups, so the header spans rather than repeating:

grid.pivotOn = ['year', 'quarter'];

getPivotColumnGroups() returns the spanning groups generated for the active view, empty when nothing is pivoted.

Totals and subtotals

Off by default, so a pivot stays a plain matrix until you ask for more. Set them through pivotOptions. Added in enterprise 0.7.0.

grid.pivotOptions = {
  grandTotal: 'bottom',
  subtotals: true,
};

Option	Type	Description
grandTotal	boolean | 'top' | 'bottom'	A grand-total row over all leaves. true means 'bottom'
subtotals	boolean	A subtotal row after each distinct value of the first row dimension. Needs more than one row dimension to apply, and is ignored in expandable mode, where parent rows already carry subtotals
expandable	boolean	Render the row dimension as a nested expandable tree instead of flat combo rows. See below
defaultExpanded	boolean | number	Initial expansion in expandable mode: true (all), false (none), or a depth threshold. Default true
maxValueColumns	number	Most value columns the pivot generates (0.8). Default 200; Infinity turns the cap off. Columns are not virtualized, so a column dimension with thousands of values would otherwise freeze the grid. Whole combinations are kept in sorted order; when some are cut the grid announces it and logs one console warning

From 0.8 getState() saves pivotOptions and the collapsed nodes of an expandable pivot, and setState() restores them (a maxValueColumns of Infinity is saved as null).

Expandable row groups

With more than one row dimension, the default is one flat row per combination. expandable: true renders them as a tree instead: one auto group column with indentation and chevrons, each parent carrying the aggregate over its own subtree.

grid.pivotRows = ['region', 'country'];
grid.pivotOptions = { expandable: true, defaultExpanded: 1 };

Column width and pin state survive a re-pivot, so adjusting the dimensions does not reset the layout the user arranged.

Reading the pivot structure

import { getPivotMeta, PIVOT_GROUP_KEY } from 'apex-grid-enterprise';

grid.getPivotColumnGroups();   // spanning column groups for the active view
getPivotMeta(row);             // a row's pivot metadata: its kind, depth, group path
PIVOT_GROUP_KEY;               // the synthetic key the auto group column uses

getPivotMeta(row) returns a PivotRowMeta for a synthesized row and undefined for an ordinary one. Its kind is 'data', 'subtotal' or 'grandTotal', which is what a cell template needs to style totals differently, and in expandable mode it also carries depth, key, label and expandable.

The whole pivot surface, including the auto group column header and the total labels, is localized through the locale surface.

Disabling pivot

Set pivotOn to an empty string to turn pivoting off. Check whether a pivot view is active with the isPivoting getter:

grid.pivotOn = '';                 // back to flat rows
console.log(grid.isPivoting);      // false

Pivot vs grouping

Pivoting requires pivotRows and pivotValues in addition to pivotOn. Pivoting and row grouping are mutually exclusive: when pivotOn is set, pivot wins and groupBy is ignored.

Client-side pivoting is also mutually exclusive with the server-side row model, where the server owns the shaping. To pivot data too large to hold in the browser, use that model's pivotCols instead.

Charting a pivot

The integrated chart reads the current view, so a <apex-grid-chart> bound to the grid redraws automatically when the pivot changes (both fire the apex-view-changed event).

React example

import { useEffect, useRef } from 'react'
import 'apex-grid-enterprise/define'

export default function PivotGrid() {
  const ref = useRef<any>(null)

  useEffect(() => {
    const grid = ref.current
    grid.columns = columns
    grid.data = data
    grid.pivotOn = 'quarter'
    grid.pivotRows = ['region']
    grid.pivotValues = { revenue: ['sum'] }
  }, [])

  return <apex-grid-enterprise ref={ref} style={{ height: 480 }} />
}