Pivoting

A pivot regroups your records. Its rows are grouped by some fields, its columns are generated from the values of others, and each cell aggregates the records that share that row and that column. Pivoting is built into the grid's core rather than added as a plugin. It is grid state, like sorting and grouping, so it has an event, it appears in snapshots, and it is persisted and saved with views.

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({
	pivot: {
		rows: ['rep'],
		columns: ['stage'],
		values: [{ field: 'amount', aggFunc: 'sum' }],
	},
});

api.setState({ pivot: null }); // back to the grid as it was

While a pivot is set, the grid shows its rows and generated columns in place of its own. The row fields become a grouping, so the hierarchy column, sticky group headers, expansion, keyboard navigation and selection work exactly as they do for any grouped grid. setState({ pivot: null }) restores the grouping, pinned columns and columns the grid had before.

Rows, columns and valuesCopy Anchor Link

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({
	pivot: {
		// Row fields, outermost first. None: one grand total row.
		rows: ['region', 'rep'],
		// Column fields, outermost first. None: one column per value.
		columns: [{ field: 'closedAt', bucket: 'quarter' }, 'stage'],
		values: [
			{ field: 'amount', aggFunc: 'sum' },
			{ field: 'amount', aggFunc: 'avg', label: 'Average deal' },
			{ field: 'id', aggFunc: 'count', label: 'Deals' },
		],
	},
});

A row or column field is a field name, or a PivotFieldDef:

Keys are labelled through the source column's format, so a select column shows "Won" rather than won. Empty values become a (Blank) key.

Each value has an aggFunc: sum, avg, min, max, count, distinctCount, first, last, median, stdDev or variance. You can also pass a function, which receives { values, field }. The default is sum for number, currency, percent, progress and rating columns, and count for everything else. A column with no type is judged by its first value when the pivot is laid out. Headers default to "Sum of Amount". When the same field appears twice, give each value an id so the two stay distinct.

Numeric aggregates (sum, avg, min, max, median, stdDev, variance) read numbers and numeric text, and skip blanks. count counts every record, and distinctCount skips blanks. first, last and a function read a cell's records in the grid's source order, at every level of rows.

Generated columns take their format from the source column, so currency is still shown as currency. When there is more than one value, each column key gets a header group with one column per value beneath it.

Filtered and calculated valuesCopy Anchor Link

A value can count only some records (where), and a value can be worked out from other values in the same cell (calculate). Together they express measures such as a win rate or an average deal:

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;

api.setState({
	pivot: {
		rows: ['owner'],
		columns: [{ field: 'closeDate', bucket: 'quarter' }],
		values: [
			{ field: 'amount', aggFunc: 'sum', id: 'won', label: 'Won', where: { stage: 'won' } },
			{ field: 'amount', aggFunc: 'sum', id: 'lost', label: 'Lost', where: { stage: 'lost' } },
			{ field: 'amount', aggFunc: 'count', id: 'wonDeals', label: 'Won deals', where: { stage: 'won' } },
			{ field: '', id: 'winRate', label: 'Win rate', calculate: '{won} / ({won} + {lost})', format: 'percent' },
			{ field: 'amount', id: 'avgWon', label: 'Avg won deal', calculate: '{won} / {wonDeals}' },
		],
	},
});

• where maps a field to a value, or to several values ({ stage: ['won', 'lost'] }, which matches any of them). A record with several values, such as tags or people, matches if any of its values does.
• A formula names other values by their id in braces: a value's own id, otherwise aggFunc:field, for example {sum:amount}. It allows numbers, + - * / (also × and ÷), brackets and unary minus, and nothing else, so a formula from a saved view is only data, never code. A value missing from a cell counts as 0, and dividing by zero leaves the cell empty.
• Formulas are worked out per cell, totals included. A total's win rate is the total won over the total won and lost, not an average of the rates. A calculated value can use an earlier calculated value.
• A calculated value takes the number format of its field (so an average deal stays money), or is shown as a percentage with format: 'percent'. A formula that doesn't parse leaves its cells empty instead of breaking the pivot.

In the Pivot panel, each value has a Count picker (All records or Only where…: choose a field, then its values). Calculated value opens an editor that checks the formula as you type and lets you click a value to insert its name. The same panel serves as the pivot view's field list.

Charting a pivotCopy Anchor Link

The chart source { kind: 'pivot' } draws the pivot. By default its categories are the outermost row groups and there is one series per generated column; by: 'columns' swaps them. Choose the value with value (a value id; default the first) and the row level with level. Totals are left out, since they are the sum of what is drawn. A cell with no value leaves a gap in a line and draws nothing in a bar, never a false 0. While a grid is pivoting, its chart window offers the pivot as a source and opens on it.

import { createGridChart, type GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
declare const container: HTMLElement;

createGridChart(api, container, { type: 'line', source: { kind: 'pivot', value: 'winRate', by: 'columns' } });

TotalsCopy Anchor Link

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({
	pivot: {
		rows: ['rep'],
		columns: ['region', 'stage'],
		values: [{ field: 'amount' }],
		totals: {
			rowTotals: 'after', // a Total column per value: 'before', 'after' (default) or false
			columnSubtotals: true, // a subtotal for every column-field level above the last
			grandTotal: 'bottom', // the grand total row: 'top', 'bottom' (default) or false
		},
	},
});

With more than one row field, each group row shows that group's aggregates, so it serves as the subtotal for the rows beneath it.

Show values asCopy Anchor Link

showAs displays a value relative to the totals around it, without changing the value that is aggregated.

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({
	pivot: {
		rows: ['rep'],
		columns: ['stage'],
		values: [
			{ field: 'amount', aggFunc: 'sum' },
			{ field: 'amount', aggFunc: 'sum', showAs: 'percentOfRowTotal', id: 'share', label: 'Share' },
		],
	},
});

Ordering and limitsCopy Anchor Link

Rows follow their labels by default, and the grid's sort on the row fields still applies. To order rows by a value instead, use rowSort. Its by is either a generated column's id or a value id; a value id sorts by that value's row total.

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({
	pivot: { rows: ['rep'], columns: ['stage'], values: [{ field: 'amount', id: 'amount' }], rowSort: { by: 'amount', direction: 'desc' } },
});

resultColumns sizes the generated columns: { flex: 1 } has them share the width the row labels leave (a pivot that fills its card as it is resized), { width: 100 } sizes them all; minWidth and maxWidth bound them. Without it a cell column is 120 wide and a total 136. A width the person drags a column to is kept while the column stays.

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
api.setState({ pivot: { rows: ['customer'], columns: ['cause'], values: [{ field: 'impact' }], resultColumns: { flex: 1, width: 88 } } });

maxColumns caps the number of generated columns (default 2000). Columns past the cap are left out, and pivotResult().truncated tells you how many. defaultExpanded takes true, false or a number of levels and sets which row groups start open (default: all of them).

Generated columns can be dragged into another order, like any column. The order holds while the data changes (a new column key joins next to its neighbour in key order) and resets when the pivot layout changes (the pivot state).

Reading the resultCopy Anchor Link

import type { GridApi } from '@witbybit/wit-grid-react';
declare const api: GridApi;
declare const groupRowId: string, colId: string;

const result = api.pivotResult(); // null outside pivot mode
result.columns; // { colId, keys, labels, valueId, kind: 'cell' | 'subtotal' | 'total' }[]
result.keyCounts; // distinct keys per column-field level
result.truncated; // columns left out by maxColumns
result.grand; // the grand total row: each generated column's value, by column id
result.values; // the values as resolved: { id, field, label: 'Sum of Amount', aggFunc, showAs }

// Drill down: the ids of the records behind one cell.
const ids = result?.cellRows(groupRowId, colId); // a total row's id for the grand total

api.getState().pivot is the current configuration; follow it with api.watch((grid) => grid.pivot, …).

Building a pivot in the gridCopy Anchor Link

Add 'pivot' to the sidebar's panels to let people build pivots themselves:

import { Grid } from '@witbybit/wit-grid-react';

declare const rows: Array<{ id: string }>;

<Grid rows={rows} columns={[]} sidebar={{ panels: ['columns', 'pivot', 'filters', 'sort'] }} />;

The Pivot panel lists the grid's fields. To use a field, drag it into Rows, Columns or Values, or use its + menu. You can drag chips to reorder them or move them between zones; on a chip's handle, ↑ / ↓ also reorders it. Dropping a chip back on the field list removes it. Each value has a Summarize by picker and a Show as picker, and date fields have a picker for their period. Switches control row totals, the grand total row, and column subtotals. Every change applies immediately. The Pivot mode switch turns the pivot off and back on, and the panel keeps the layout in between.

Every column's header menu also has Pivot by this column. It puts that column's values across the top, uses the grid's current grouping for the rows, and sums the first other number column, or counts records if there is none. While a pivot is set, the group strip above the headers shows what it is pivoted by, with a Leave pivot button.

A column holds numbers if its schema says so (number, currency, percent, progress or rating) or if it has a number filter. When a column says neither, its first value decides. Numbers are summed by default and everything else is counted. A generated column uses its source column's number format, or the source's own format when the aggregate is in the same units (sum, average, min, max, median).

Select fields such as status and stage keep their option order across and down, so a pipeline reads Lead, Qualified, Won, Lost rather than alphabetically. The row-label column is headed by the row fields ("Owner", or "Region › Owner"). Each column field has a Show picker for its top 5, 10, 20 or 50 values by number of records; the rest fold into Other. Every field has an ascending or descending order. While pivoting, the Filters panel offers the grid's own columns, and the Pivot panel's Filter records button opens it, showing how many filters apply. Color scales, data bars and icon sets (styleRules) work on generated columns. They paint the innermost row groups only, so subtotals and totals neither skew the scale nor get painted.

For a pivot as one of a workspace's views, with its own field list, a heatmap and a drill-down into the record inspector, see the Pivot view.

Drill-downCopy Anchor Link

Double-clicking a pivot cell lists the records behind it in a popover. The list starts with each record's name (the grid's first column of its own), then the pivot's fields, so each record shows what it is and why it was counted there. The records are exactly the ones the value counts: "Won revenue · East" lists East's won deals, not its lost ones (a value's where narrows them). A calculated value (a win rate) has no records of its own: its cells list none, and the event says calculated: true. The event also carries the cell's rowKeys, columnKeys, valueId and the value's where, which a server-side grid uses to fetch the records itself. Choosing a record fires recordOpenRequested, which a workspace answers by opening the record in its inspector.

Show these records (in the popover, and in the cell's menu) runs the showPivotRecords command, which shows the cell's records through api.showRecords. The cell's row and column keys become column filters (a date bucket as its days, a number range as [low, high), a blank as a blank cell), and the value's where becomes filters on its fields. Those filters are joined to the person's own filters. Without a destination of your own, the pivot turns off, the table shows exactly those records, and there is a way back to the pivot. A cell whose keys or where no filter can name (the folded "Other" column, a several-value key, a where field with no column) cannot go to the table. A calculated value's cell says why. api.pivotRecords(rowId, colId) gives a cell's records directly.

Server-side pivotingCopy Anchor Link

On a server-side grid, pivot mode asks the server for the whole pivot through the datasource's getPivot. A pivot's size depends on its keys (rows × columns), not on how many records there are, so one answer covers it all. The grid keeps the answer as a tree and opens and closes groups without asking again. It asks again when the pivot, the filters, the search, the query or the sort change.

import { pivotRecords, type ColumnDef, type ServerSideDatasource } from '@witbybit/wit-grid-react';

type Deal = { id: string; region: string; stage: string; amount: number };
declare const columns: ColumnDef<Deal>[];
declare function loadDeals(filter: unknown): Promise<Deal[]>;
declare function loadPage(start: number, end: number): Promise<{ rows: Deal[]; rowCount: number }>;

const datasource: ServerSideDatasource<Deal> = {
	getRows: (request) => loadPage(request.startRow, request.endRow),
	// The request carries the pivot (as JSON), the filters, the search, the query and the sort.
	getPivot: async (request) => pivotRecords(await loadDeals(request.filter), request.pivot, { columns, sortModel: request.sortModel }),
};

pivotRecords runs the grid's own pivot engine, so a Node server holding the records in memory answers exactly as pivot mode would in the browser. A server backed by SQL groups by the row and column fields itself. It returns:

• columns: each generated column's keys (and optional labels), its valueId (sum:amount, or the value's id), and its kind (cell, subtotal or total).
• rows: every row group, parents before their children, each with its keys down the row fields and its values keyed by pivotColumnId(column).
• grand: the grand total row's values. It can also return values to say which aggregate it used for a value that had none.

Custom aggregate functions can't be sent to a server, so they are left out of the request. Drill-down passes the cell's keys and the value's where in pivotDrillDown, and the app fetches exactly those records from there. If the datasource has no getPivot, pivot mode reports the error through serverSideStateChanged.

ExportCopy Anchor Link

exportCsv and exportExcel write the pivot as it appears: one header row per column-field level above the value headers, group rows with their totals, and the grand total row. In Excel, each header group is merged across the columns it spans, and aggregates are written as numbers in the column's format. Header groups are exported this way for any grid with headerGroup columns, not only pivots.

In ReactCopy Anchor Link

The grid's API is passed to onGridReady:

import { Grid, type ColumnDef } from '@witbybit/wit-grid-react';

type Deal = { id: string; rep: string; stage: string; amount: number };
declare const deals: Deal[];
declare const columns: ColumnDef<Deal>[];

<Grid
	rows={deals}
	columns={columns}
	getRowId={(row) => row.id}
	onGridReady={({ api }) => api.setState({ pivot: { rows: ['rep'], columns: ['stage'], values: [{ field: 'amount' }] } })}
/>;

Persistence and viewsCopy Anchor Link

A persisted state or saved view records the pivot together with the grid's own grouping, pinned columns and columns, meaning the setup it goes back to when pivot mode ends. Restoring that state enters pivot mode again. Restoring a state that was saved without a pivot ends pivot mode.

A function aggFunc cannot be stored. It is saved as 'custom' under its value id, and on restore it is taken from the grid's current pivot value with the same id. If no such value exists, that value is left out.