# Pivot table

Measures cross-tabulated by a row and a column dimension, with totals.

- Component: `<PivotTable />`, imported from `em-kit`
- Required props: `rowDimension`, `columnDimension`, `measures` and `results`
- HTML page, with live previews: https://emkit.embeddable.com/docs/components/pivot-table
- Playground (needs a browser): https://emkit.embeddable.com/docs/components/pivot-table/playground

`PivotTable` takes `rowDimension` + `columnDimension` + `measures`. Its siblings name their fields differently: `HeatMap` takes `rowDimension` + `columnDimension` + `measure`.

## Install

```bash
npm install em-kit
pnpm add em-kit   # or, with pnpm
yarn add em-kit   # or, with yarn
bun add em-kit   # or, with bun
```

`react` and `react-dom` 19.1.1 or later are the only peer dependencies; Chart.js installs with the package. Wrap your app, or the part with charts, in `<ChartsProvider>` once: without it every chart throws `EmKit components must be rendered inside <ChartsProvider>.` Every example on this page includes `<ChartsProvider>` so that it runs on its own: if your app already has one, delete it from the example. The table fills its parent, so the parent needs a height. In the Next.js App Router, every file that renders `ChartsProvider` or a chart starts with `'use client'`, and the provider pins the locale, `theme={{ formatter: { locale: 'en-US' } }}`, so the server and the browser format numbers alike. [The quick start](/docs/quick-start.md#in-the-nextjs-app-router) has the Next.js files.

## Usage

```tsx
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    abbreviateLargeNumber: true,
    columnAggregation: ['sum'],
    rowAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
```

- Totals are per measure: `columnAggregation` and `rowAggregation` in the measure's `inputs`.

## States

The table draws its own states from `results`: a skeleton while `isLoading` is true and it has no rows, a spinner in the header while it reloads rows it already has, an empty state when `data` is `[]`, and the message when `error` is set. Pass your query’s state straight through.

## Examples

### Average, min and max

One measure with row statistics instead of plain totals. `rowAggregation` adds a column per statistic and `columnAggregation` a row, always in the order sum, min, max, average. Both take any of `'sum'`, `'min'`, `'max'`, `'average'`.

```tsx
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    abbreviateLargeNumber: true,
    columnAggregation: ['average'],
    rowAggregation: ['average', 'min', 'max'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
```

### Share of total

Each cell as a percentage, with column totals. `showAsPercentage` shows each cell as its share; other measures may be ignored when it's on. `columnAggregation: ['sum']` adds the totals row.

```tsx
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 1,
    showAsPercentage: true,
    columnAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
```

### Full numbers

Every cell and total written out in full, in wider columns. `columnWidth` and `firstColumnWidth` are in pixels; the table scrolls sideways when the columns outgrow the card.

```tsx
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 0,
    columnAggregation: ['sum'],
    rowAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
        columnWidth={130}
        firstColumnWidth={170}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
```

### Sub-rows on demand

Each region opens to its countries, loaded the first time it is opened. `setExpandedRowKey` is called with a row’s `rowDimension` value the first time it is opened: add it to `expandedRowKeys` and load `resultsSubRows`, whose rows carry the `rowDimension`, `subRowDimension` and `columnDimension` values and the measures.

```tsx title="PivotSubRows.tsx"
import { useState } from 'react';
import { ChartsProvider, PivotTable, dimension, measure, results } from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const country = dimension({ name: 'country', title: 'Country' });
const quarter = dimension({ name: 'quarter', title: 'Quarter' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: { currency: 'USD', decimalPlaces: 0 },
});

// The table's rows: one per region and quarter.
const rows = [
  { region: 'Americas', quarter: 'Q1', revenue: 182000 },
  { region: 'Americas', quarter: 'Q2', revenue: 197000 },
  { region: 'EMEA', quarter: 'Q1', revenue: 143000 },
  { region: 'EMEA', quarter: 'Q2', revenue: 151000 },
];

// The sub-rows: one per region, country and quarter. Each carries its
// region, so the table can put it under the right row.
const byCountry = [
  { region: 'Americas', country: 'United States', quarter: 'Q1', revenue: 141000 },
  { region: 'Americas', country: 'United States', quarter: 'Q2', revenue: 150000 },
  { region: 'Americas', country: 'Canada', quarter: 'Q1', revenue: 41000 },
  { region: 'Americas', country: 'Canada', quarter: 'Q2', revenue: 47000 },
  { region: 'EMEA', country: 'Germany', quarter: 'Q1', revenue: 79000 },
  { region: 'EMEA', country: 'Germany', quarter: 'Q2', revenue: 83000 },
  { region: 'EMEA', country: 'France', quarter: 'Q1', revenue: 64000 },
  { region: 'EMEA', country: 'France', quarter: 'Q2', revenue: 68000 },
];

export function RevenueByRegion() {
  // The regions opened so far. The table calls setExpandedRowKey with a
  // region the first time a reader opens it; closing it is the table's own.
  const [opened, setOpened] = useState<string[]>([]);
  const open = (key: string) =>
    setOpened((keys) => (keys.includes(key) ? keys : [...keys, key]));

  // Stand-in for your API: the countries of the opened regions only.
  const subRows = byCountry.filter((row) => opened.includes(row.region));

  return (
    <div style={{ height: 300 }}>
      <PivotTable
        title="Revenue by region"
        rowDimension={region}
        subRowDimension={country}
        columnDimension={quarter}
        measures={[revenue]}
        results={results(rows)}
        resultsSubRows={results(subRows)}
        expandedRowKeys={opened}
        setExpandedRowKey={open}
      />
    </div>
  );
}

// In your app, the provider goes once around the part with charts.
export const Example = () => (
  <ChartsProvider>
    <RevenueByRegion />
  </ChartsProvider>
);
```

## Props

Required props come first. A blank default means the prop is unset unless you pass it.

| Prop | Type | Required | Default | Description |
|---|---|---|---|---|
| `rowDimension` | `Dimension` | yes |  | The dimension down the side of the grid. |
| `columnDimension` | `Dimension` | yes |  | The dimension across the top of the grid. |
| `measures` | `Measure[]` | yes |  | The numbers shown in the cells. Their `inputs` set the formatting and the row and column totals. |
| `results` | `DataResponse` | yes |  | Your query’s state: `{ isLoading, data, error }`, where `data` is your rows. `results(rows)` builds it from an array. |
| `resultsSubRows` | `DataResponse` | no |  | Rows for the second level, loaded when a row is expanded. |
| `subRowDimension` | `Dimension` | no |  | A second row dimension, shown when a row is expanded. |
| `displayNullAs` | `string` | no |  | Text shown where a value is null, for example `"–"`. |
| `columnWidth` | `number` | no |  | A fixed width for the value columns, in pixels. |
| `firstColumnWidth` | `number` | no |  | A fixed width for the row-label column, in pixels. |
| `expandedRowKeys` | `string[]` | no |  | The rows whose sub-rows you load, by `rowDimension` value: each key `setExpandedRowKey` has given you. Only needed with `subRowDimension`. |
| `setExpandedRowKey` | `(expandedRowKeys: string) => void` | no |  | Called with a row’s `rowDimension` value, as a string, the first time a reader opens the row. Add it to `expandedRowKeys` and load `resultsSubRows` for it; closing the row is the table’s own. |
| `title` | `string` | no |  | The card’s title. A `"key\|fallback"` string is looked up in your translations. |
| `description` | `string` | no |  | Smaller text under the title. Translates like `title`. |
| `tooltip` | `string` | no |  | Text for an info icon beside the title. |
| `hideMenu` | `boolean` | no | `false` | Hide the card’s header: the title, description and menu. |
| `menuOptions` | `("csv" \| "xlsx" \| "png" \| "maximize")[]` | no | `["csv", "xlsx", "png", "maximize"]` | Which entries the card menu offers. Pass `[]` for none. |

## Field options

Options go in a field’s `inputs`, for example `measure({ name: 'revenue', inputs: { prefix: '$' } })`. Every field takes `prefix`, `suffix`, `decimalPlaces`, `currency`, `abbreviateLargeNumber`, `displayName` and `maxCharacters`. A time dimension in `columnDimension` or `rowDimension` also takes `granularity`, `dateBounds` and `ignoreEmptyDate`. Its `granularity` is what labels the axis as dates and fills periods with no row; `dimension()` takes it in `inputs` or at the top level, `dimension({ granularity: 'month' })`. [Field options](/docs/describing-your-data.md#field-options) describes them. `PivotTable` also reads:

| Option | Type | Set on | Default | Description |
|---|---|---|---|---|
| `columnAggregation` | `('sum' \| 'min' \| 'max' \| 'average')[]` | `measures` |  | Add a row under the table for each calculation you list, worked out down each column. |
| `rowAggregation` | `('sum' \| 'min' \| 'max' \| 'average')[]` | `measures` |  | Add a column at the end of the table for each calculation you list, worked out along each row. |
| `showAsPercentage` | `boolean` | `measures` | `false` | Show each cell as a share of the total instead of its value. Other measures may be left out. |

## Keyboard

| Key | Where | What it does |
|---|---|---|
| `Tab`, `Shift` + `Tab` | Table | With `subRowDimension`, moves between the row headers. Without it, nothing in the table takes focus. |
| `Enter` or `Space` | Row header, with `subRowDimension` | Opens or closes its sub-rows. |

The card menu’s keys, and what is still missing, are in [Accessibility](/docs/accessibility.md#keyboard).
