Documentation

Server-side pivot

The PivotGrid normally holds every row in the browser and aggregates them in a Web Worker. That works well up to several million rows. Past that, or when the rows must not leave your database, let the server do the aggregating. The grid sends the layout, the server answers with the totals, and the grid lays them out.

Only the totals ever reach the browser. The first answer is one level deep: when a group is closed, the grid does not ask for its children. Opening a group sends one more request.

import { EnterprisePivotGrid, pivotServerSource } from '@kanunilabs/pivotgrid-react-enterprise';

const source = pivotServerSource({
  load: (request) => fetch('/api/pivot', { method: 'POST', body: JSON.stringify(request) }).then((r) => r.json()),
  loadFieldValues: (field) => fetch(`/api/pivot/values?field=${field.fieldId}`).then((r) => r.json()),
});

<EnterprisePivotGrid licenseKey={key} serverSource={source} initialFields={fields} />;

data is not needed. In JavaScript, pass the same serverSource to createEnterprisePivotGrid.

Rendering, sorting (by a field or by a measure, with top-N), percent-of-total and the other display modes, calculated summary fields, charts, the row sparkline, saved layouts and the pivot exports all work as usual. They run in the browser on the totals the server sent.

What the grid asks

Each change sends one PivotServerRequest, such as a field moved, a filter ticked or a group opened:

{
  rows:    [{ fieldId: 'region', dataField: 'region', dataType: 'string' }, { fieldId: 'city', … }],
  columns: [{ fieldId: 'year', dataField: 'year', dataType: 'number' }],
  values:  [{ fieldId: 'revenue', dataField: 'revenue', dataType: 'number', summaryType: 'sum' }],
  filters: [{ fieldId: 'region', dataField: 'region', type: 'exclude', values: ['APAC'] }],
  prefilter: null,              // the prefilter builder's AND/OR tree, when set
  expandedRows: [['EMEA']],     // open row groups by path, or 'all'
  expandedColumns: [],
}

What the server answers

Your server answers with cells. A cell is a row path, a column path and the measures at that point. You need one cell for every combination the open groups make visible, including each level's subtotal and the grand totals (the empty path []):

{
  cells: [
    { row: [],                 col: [],       values: { revenue: 100 } },  // grand total
    { row: ['EMEA'],           col: [],       values: { revenue: 35 } },   // a row total
    { row: ['EMEA'],           col: [2026],   values: { revenue: 25 } },
    { row: ['EMEA', 'Berlin'], col: [2026],   values: { revenue: 20 } },   // EMEA is open
    { row: [],                 col: [2026],   values: { revenue: 50 } },   // a column total
    // …
  ],
}

In SQL this is one GROUP BY with grouping sets or ROLLUP, one set per visible level. Apply the filters and the prefilter first. Cells for levels below a closed group are ignored, so a server that always returns a little more than was asked is fine.

rollupInMemory(rows, request) is the reference implementation. It answers a request from an array, and its source is the specification. A test in this repository puts random data through it and through the local engine, and checks that every visible cell matches for all six aggregates, fully expanded and filtered. Use it to check your own server: for the same rows, both should send back the same cells.

Caching and refresh

pivotServerSource keeps the last 16 answers by request (cacheSize). Closing a group and opening it again, or changing totals and sort order, does not ask the server again. Call refreshEngine() (the toolbar's refresh button) when the data on the server has changed. It clears the cache and asks again. A failed request is not cached. It shows as an engine error, and the next change tries again.

What does not apply

Everything that needs the individual rows in the browser:

  • Custom summaries (summaryType: 'custom') and row-level calculated fields (calculateExpression). The server computes what it is asked for; compute such values there and send them as an ordinary measure. Summary-level calculated fields (calculateSummaryExpression) work, because they combine totals.
  • Drill-down to source rows. The JavaScript Enterprise grid turns its drill-down modal off in server mode, and cellClick reports no records.
  • Import, live updates (applyTransaction) and raw-row exports. The pivot exports (the layout as you see it) work.
  • The header filter's checklist shows what loadFieldValues returns. Leave it out and the list is empty.