1. Data Grid docs
  2. 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

  1. Filter the records down to what the report should cover.
  2. 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.
  3. 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.
  4. 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:

showAsShows
'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 pivot method 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; openPivot takes 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.