Pivot table
Measures cross-tabulated by a row and a column dimension, with totals.
Preview
Revenue by region and plan
| Plan | Starter | Growth | Enterprise | Sum |
|---|---|---|---|---|
| Region | Revenue | Revenue | Revenue | Revenue |
| North America | $52K | $133K | $139K | $325K |
| Europe | $39K | $92K | $90K | $221K |
| Asia Pacific | $21K | $72K | $53K | $146K |
| Latin America | $11K | $36K | $37K | $84K |
| Middle East | $9.5K | $28K | $24K | $61K |
| Sum | $133K | $361K | $343K | $837K |
Usage
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:
columnAggregationandrowAggregationin the measure'sinputs.
Install with npm install em-kit. Needs <ChartsProvider> once and a parent with a height: Quick start.
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'.
Show full code (52 lines)
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 },
];const revenue = measure({
name: 'revenue',
title: 'Revenue',
inputs: {
currency: 'USD',
abbreviateLargeNumber: true,
columnAggregation: ['average'],
rowAggregation: ['average', 'min', 'max'],
},
});
<PivotTable
title="Revenue by region and plan"
rowDimension={region}
columnDimension={plan}
measures={[revenue]}
results={results(rows)}
/>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.
Show full code (52 lines)
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 },
];const revenue = measure({
name: 'revenue',
title: 'Revenue',
inputs: {
currency: 'USD',
decimalPlaces: 1,
showAsPercentage: true,
columnAggregation: ['sum'],
},
});
<PivotTable
title="Revenue by region and plan"
rowDimension={region}
columnDimension={plan}
measures={[revenue]}
results={results(rows)}
/>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.
Show full code (54 lines)
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 },
];const revenue = measure({
name: 'revenue',
title: 'Revenue',
inputs: {
currency: 'USD',
decimalPlaces: 0,
columnAggregation: ['sum'],
rowAggregation: ['sum'],
},
});
<PivotTable
title="Revenue by region and plan"
rowDimension={region}
columnDimension={plan}
measures={[revenue]}
results={results(rows)}
columnWidth={130}
firstColumnWidth={170}
/>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.
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
| Prop | Type | Default | Description |
|---|---|---|---|
rowDimensionrequired | Dimension | The dimension down the side of the grid. | |
columnDimensionrequired | Dimension | The dimension across the top of the grid. | |
measuresrequired | Measure[] | The numbers shown in the cells. Their inputs set the formatting and the row and column totals. | |
resultsrequired | DataResponse | Your query’s state: { isLoading, data, error }, where data is your rows. results(rows) builds it from an array. | |
resultsSubRows | DataResponse | Rows for the second level, loaded when a row is expanded. | |
subRowDimension | Dimension | A second row dimension, shown when a row is expanded. | |
displayNullAs | string | Text shown where a value is null, for example "–". | |
columnWidth | number | A fixed width for the value columns, in pixels. | |
firstColumnWidth | number | A fixed width for the row-label column, in pixels. | |
expandedRowKeys | string[] | The rows whose sub-rows you load, by rowDimension value: each key setExpandedRowKey has given you. Only needed with subRowDimension. | |
setExpandedRowKey | ( | 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 | The card’s title. A "key|fallback" string is looked up in your translations. | |
description | string | Smaller text under the title. Translates like title. | |
tooltip | string | Text for an info icon beside the title. | |
hideMenu | boolean | false | Hide the card’s header: the title, description and menu. |
menuOptions | ( | ["csv", | Which entries the card menu offers. Pass [] for none. |
A blank default means the prop is unset unless you pass it.
Field options
Set in a field’s inputs, beside the common options:
| Option | Type | Set on | Description |
|---|---|---|---|
columnAggregation | ( | measures | Add a row under the table for each calculation you list, worked out down each column. |
rowAggregation | ( | measures | Add a column at the end of the table for each calculation you list, worked out along each row. |
showAsPercentage | boolean | measures | Show each cell as a share of the total instead of its value. Other measures may be left out. Default: false. |
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.