- Data Grid docs
- Working with records
Pivot reports
Turn the matching records into a pivot table by row and column fields, with several values shown as sums, shares, running totals or ranks, and a chart of its largest values; locally or on your server.
A pivot report answers "how much of this, by that": revenue by region and product, orders by month and channel. The grid builds it from the records that match the current search and filters, shows it as a table you can sort, filter and export like any other, and draws a chart of it. With a server source, your server can build it instead.
Open the pivot example: orders by region and channel against product category, revenue and units
In the grid
- Filter the records down to what the report should cover.
- Choose Pivot in the toolbar. Pick the Rows field, optionally a second one under Then by, and optionally a Columns field. Under Values, each line is a field, its Summary (sum, average, minimum, maximum or count) and how to Show values as; Add value adds another line. The dialog starts from your current grouping and summary.
- The grid shows the pivot. Chart draws its top 20 values by absolute size, for the value column you choose, as a column, bar, line or pie chart, and Download SVG saves the picture. Export saves the pivot as CSV, XLSX or PDF.
- Back to records returns to the records with your search, filters and columns as they were.
From code
openPivot(spec) replaces the records with a pivot of the matching ones. The spec takes rows (row fields, outermost first), columns (column fields) and values, each { field, agg, showAs }: agg is sum (the default), average, min, max or count, and showAs is one of these:
showAs | Shows |
|---|---|
'normal' | The value itself (the default) |
'percentOfTotal' | A share of the grand total |
'percentOfCol', 'percentOfRow' | A share of its column's or row's total |
'runTotal' | A running total down the rows |
'rankDescending', 'rankAscending' | Its rank, largest or smallest first |
'index' | Its weight against the row and column totals, as Excel's Index |
The short form, { rowField, columnField, valueField, agg }, names one of each and still works. closePivot() returns to the records and their view. getChartData(field) gives the top 20 values of a pivot column, as the Chart dialog draws them, for a chart of your own. While a pivot is shown, getSource().kind is 'pivot', and the sourceLoad event fires on the way in and out.
JavaScript
import { mountTable } from '@8sheets/data-grid';
const grid = await mountTable(document.querySelector('#sales'), { title: 'Sales', data: sales });
await grid.setFilters([{ field: 'Year', op: 'equal', value: 2026 }]);
await grid.openPivot({
rows: ['Region', 'Channel'],
columns: ['Product'],
values: [
{ field: 'Revenue', agg: 'sum' },
{ field: 'Revenue', agg: 'sum', showAs: 'percentOfTotal' },
{ field: 'Units', agg: 'average' },
],
});
const numeric = grid.getFields().find((f) => f.type === 'number'); // a value column of the pivot
const top = await grid.getChartData(numeric.name); // [{ label, value, text }, …], largest first
await grid.download('xlsx', { fileName: 'revenue-by-region.xlsx' });
await grid.closePivot();React
import { useRef, useState } from 'react';
import { DataGrid } from '@8sheets/data-grid-react';
export function Sales({ options }) {
const ref = useRef(null);
const [pivot, setPivot] = useState(false);
async function toggle() {
if (pivot) await ref.current.closePivot();
else await ref.current.openPivot({ rowField: 'Region', columnField: 'Product', valueField: 'Revenue' });
setPivot(!pivot);
}
return (
<>
<button onClick={toggle}>{pivot ? 'Back to records' : 'Revenue by region'}</button>
<div style={{ height: 480 }}><DataGrid ref={ref} options={options} /></div>
</>
);
}Vue
<script setup>
import { ref } from 'vue';
import { DataGrid } from '@8sheets/data-grid-vue';
defineProps({ options: Object });
const dataGrid = ref(null);
const pivot = ref(false);
async function toggle() {
if (pivot.value) await dataGrid.value.closePivot();
else await dataGrid.value.openPivot({ rowField: 'Region', columnField: 'Product', valueField: 'Revenue' });
pivot.value = !pivot.value;
}
</script>
<template>
<button @click="toggle">{{ pivot ? 'Back to records' : 'Revenue by region' }}</button>
<div style="height: 480px"><DataGrid ref="dataGrid" :options="options" /></div>
</template>Angular
import { Component, Input, ViewChild } from '@angular/core';
import { DataGridComponent, DataGridOptions } from '@8sheets/data-grid-angular';
@Component({
selector: 'app-sales',
imports: [DataGridComponent],
template: `
<button (click)="toggle()">{{ pivot ? 'Back to records' : 'Revenue by region' }}</button>
<eight-data-grid #grid [options]="options" style="height: 480px" />`,
})
export class Sales {
@Input() options: DataGridOptions = {};
@ViewChild('grid') grid!: DataGridComponent;
pivot = false;
async toggle() {
if (this.pivot) await this.grid.closePivot();
else await this.grid.openPivot({ rowField: 'Region', columnField: 'Product', valueField: 'Revenue' });
this.pivot = !this.pivot;
}
}On your server
A server source offers Pivot when it has a pivot method (pivotUrl in remoteTableSource). The grid sends it the view state and the full spec, { state, spec, signal }, and expects { fields, rows }: the pivot's fields, as in fields, and a row of values per pivot line in the order of fields. The grid then shows the answer as local, read-only records, so sorting, filtering, the chart and the export of the pivot work as they do for a local one. tableQueryService in the source download has a pivot that answers it.
const grid = await mountTable(el, {
fields,
dataSource: remoteTableSource({ queryUrl: '/api/sales/query', pivotUrl: '/api/sales/pivot' }),
});
await grid.openPivot({ rows: ['Region'], values: [{ field: 'Revenue', showAs: 'percentOfTotal' }] });
The pivot endpoint gets { state, spec } as a JSON POST and answers, for example:
{
"fields": [{ "name": "Region", "type": "string" }, { "name": "Revenue", "type": "number", "format": "0.0%" }],
"rows": [["East", 0.42], ["West", 0.33], ["North", 0.25]]
}
Limits
- A server source needs a
pivotmethod to offer Pivot; without one, pivots need local data. - A pivot is a read-only table; editing returns with Back to records.
- The pivot must fit a worksheet (1,048,576 rows by 16,384 columns); a column field with more distinct values is refused.
- The dialog sets up to two row fields and one column field;
openPivottakes more. - The chart is a quick look at the largest values, not a chart editor. For charts you can format and save in a workbook, open the data in Spreadsheet.