Pivot tables and charts

setPivot() turns the rows into a summary table. Fields in rows become the rows; with more than one, the earlier ones become groups with their own…

Live demoPivot tables & charts

Summarize rows by any fields and turn cells into bar, line or pie charts.

Code for this demo

import { createGrid, type ChartHandle, type ColumnDef } from '@freegridjs/core';
import { makeRows } from '../data';
import { button, separator, text, tr, type DemoContext } from '../ui';

export default function mount(ctx: DemoContext) {
  const { lang } = ctx;
  const h = (en: string, ko: string) => tr(lang, { en, ko });
  const columns: ColumnDef[] = [
    { field: 'country', header: h('Country', '국가'), width: 150 },
    { field: 'company', header: h('Company', '회사'), width: 140 },
    { field: 'status', header: h('Status', '상태'), width: 110 },
    { field: 'name', header: h('Name', '이름'), width: 160 },
    { field: 'q1', header: h('Q1', '1분기'), type: 'number', format: '#,##0', width: 110 },
    { field: 'q2', header: h('Q2', '2분기'), type: 'number', format: '#,##0', width: 110 },
    { field: 'q3', header: h('Q3', '3분기'), type: 'number', format: '#,##0', width: 110 },
    { field: 'q4', header: h('Q4', '4분기'), type: 'number', format: '#,##0', width: 110 },
    { field: 'total', header: h('Annual revenue', '연매출'), type: 'number', format: '#,##0', width: 130 },
  ];
  const grid = createGrid(ctx.host, {
    columns,
    data: makeRows(2000),
    locale: lang,
    theme: 'board',
    sideBar: { open: true, panels: ['pivot', 'columns', 'filters'] },
    pivot: { rows: ['country'], columns: ['status'], values: [{ colId: 'total', aggFunc: 'sum' }] },
  });
  grid.on('pivotChange', e => ctx.log(`pivotChange → ${e.pivot ? JSON.stringify(e.pivot) : 'off'}`));

  let chart: ChartHandle | null = null;
  const draw = (type: 'bar' | 'stackedBar' | 'line' | 'pie') => {
    chart?.destroy();
    grid.setRange(null);
    chart = grid.createChart({ type, title: h('Revenue by country', '국가별 매출') });
  };

  text(ctx, h('Build the pivot in the panel on the right, or drag across cells and right-click for a chart.',
    '오른쪽 패널에서 피벗을 구성하거나, 셀을 드래그한 뒤 우클릭해 차트를 만들어 보세요.'));
  separator(ctx);
  button(ctx, h('Country × status', '국가 × 상태'), () => grid.setPivot({ rows: ['country'], columns: ['status'], values: [{ colId: 'total' }] }));
  button(ctx, h('Country › company, quarterly averages', '국가 › 회사, 분기 평균'), () =>
    grid.setPivot({ rows: ['country', 'company'], values: ['q1', 'q2', 'q3', 'q4'].map(colId => ({ colId, aggFunc: 'avg' as const })) }));
  button(ctx, h('Normal view', '일반 보기'), () => grid.setPivot(null));
  separator(ctx);
  button(ctx, h('Stacked bar chart', '누적 막대 차트'), () => draw('stackedBar'));
  button(ctx, h('Line chart', '꺾은선 차트'), () => draw('line'));
  button(ctx, h('Pie chart', '원형 차트'), () => draw('pie'));
  return grid;
}

setPivot() turns the rows into a summary table. Fields in rows become the rows; with more than one, the earlier ones become groups with their own subtotals. Fields in columns spread across the top as column groups, and every value gets a Total. Each value is summed by default; choose 'avg', 'min', 'max' or 'count' as needed. Subtotals are calculated from the original rows, so averages stay correct. The pivot table can be sorted, filtered, exported and charted like any other view, and setPivot(null) brings the normal rows back. Add the 'pivot' panel to the side bar and people can build the table themselves.

Select a block of cells, right-click, and choose a chart. The first text column gives the labels and the number columns become the series. The chart opens in a panel over the grid, can switch between bar, stacked bar, line, area, bar and line, pie, donut and scatter, and redraws when the cells change. createChart() does the same from code, and container puts the chart anywhere on your page.

// Sales by country (rows) and year (columns), with totals
grid.setPivot({
  rows: ['region', 'country'],        // region becomes a group with subtotals
  columns: ['year'],
  values: [{ colId: 'sales', aggFunc: 'sum' }, { colId: 'margin', aggFunc: 'avg' }],
});
grid.setPivot(null);                   // back to the normal rows

// Let people build it themselves
createGrid(el, { columns, data, sideBar: { open: true, panels: ['pivot', 'columns'] } });

// Charts: the selected range, or everything on screen
const chart = grid.createChart({ type: 'bar', title: 'Sales' });
chart.setType('line');
grid.createChart({ type: 'pie', container: document.querySelector('#chart') });

// Without a grid
import { renderChart } from '@freegridjs/core';
renderChart(el, { type: 'line', categories: ['Jan', 'Feb'], series: [{ name: 'Sales', values: [120, 180] }] });