Region by Category Revenue Matrix From One Order Grid

You have built this panel before: a region by category breakdown sitting beside the orders table, fed by its own query and kept roughly in step by an event hook and good intentions. It works the day you ship it. Then someone filters the orders grid to this quarter, or to one region, and the breakdown keeps showing the old totals, because nothing told it to run again. Now there are two numbers on one screen and only one of them is right. A pivot table in JavaScript can live inside the grid instead.

A derived grid closes that gap by making the breakdown computed rather than queried. Build the orders grid the way you already do, then build a second grid whose source reads the first: group by region and category, sum the revenue, and the result is a live grid, not a snapshot of one.

source: {
  mode: 'derived',
  from: orders,
  groupBy: ['region', 'category'],
  select: { revenue: { of: 'amount', fn: 'sum' } },
}

Filter the orders grid once, by region, by a date range, by typing into the quick filter, and the matrix recomputes on the next frame, because it was never a second query to begin with. It is the same table, read a different way.

For you that means the breakdown panel stops being a second thing to maintain. There is no second query to write, no event hook to wire up, and no meeting where two numbers on one screen do not agree, because the matrix is never able to see a row the table has filtered out. One filter moves both grids, every time, with no code of yours in between.

Plain JavaScript

Twenty orders, a grid that holds them, and a second grid grouped by region and category:

import { createGrid } from '@toclocoinc/lattice-grid';

const orders = [
  { id: 'O1',  region: 'EMEA', category: 'Hardware', amount: 2400 },
  { id: 'O2',  region: 'EMEA', category: 'Software', amount: 1800 },
  { id: 'O3',  region: 'EMEA', category: 'Hardware', amount: 1200 },
  { id: 'O4',  region: 'EMEA', category: 'Services',  amount: 900 },
  { id: 'O5',  region: 'EMEA', category: 'Software', amount: 2600 },
  { id: 'O6',  region: 'AMER', category: 'Hardware', amount: 3100 },
  { id: 'O7',  region: 'AMER', category: 'Software', amount: 1500 },
  { id: 'O8',  region: 'AMER', category: 'Services',  amount: 2200 },
  { id: 'O9',  region: 'AMER', category: 'Hardware', amount: 1700 },
  { id: 'O10', region: 'AMER', category: 'Support',   amount: 600 },
  { id: 'O11', region: 'AMER', category: 'Software', amount: 2000 },
  { id: 'O12', region: 'APAC', category: 'Hardware', amount: 2900 },
  { id: 'O13', region: 'APAC', category: 'Software', amount: 1100 },
  { id: 'O14', region: 'APAC', category: 'Services',  amount: 1600 },
  { id: 'O15', region: 'APAC', category: 'Hardware', amount: 800 },
  { id: 'O16', region: 'APAC', category: 'Support',   amount: 400 },
  { id: 'O17', region: 'EMEA', category: 'Support',   amount: 700 },
  { id: 'O18', region: 'AMER', category: 'Services',  amount: 1300 },
  { id: 'O19', region: 'APAC', category: 'Software', amount: 1900 },
  { id: 'O20', region: 'EMEA', category: 'Services',  amount: 1100 },
];

const orderColumns = [
  { field: 'id', title: 'Order' },
  { field: 'region', title: 'Region', filter: { type: 'set' } },
  { field: 'category', title: 'Category', filter: { type: 'set' } },
  { field: 'amount', title: 'Amount', type: 'number', total: 'sum' },
];

// The table a reader already knows how to build.
const ordersGrid = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: orderColumns,
  rows: orders,
});

// The matrix beside it, grouped two levels deep and summed.
const matrix = createGrid(document.querySelector('#matrix'), {
  columns: [
    { field: 'region', title: 'Region' },
    { field: 'category', title: 'Category' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ],
  source: {
    mode: 'derived',
    from: ordersGrid,
    groupBy: ['region', 'category'],
    select: { revenue: { of: 'amount', fn: 'sum' } },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
});

Filter ordersGrid to one region and matrix narrows to the same rows, re-summed, on the next frame.

React

The same two grids through the React adapter. The orders grid forwards a ref; once it is mounted, its live instance becomes the from of the second grid’s derived source:

import React from 'react';
import { createGrid } from '@toclocoinc/lattice-grid';
import '@toclocoinc/lattice-grid/css';
import { createLatticeGrid } from '@toclocoinc/lattice-grid/modules/react';

const LatticeGrid = createLatticeGrid({ React, createGrid });

const orderColumns = [
  { field: 'id', title: 'Order' },
  { field: 'region', title: 'Region', filter: { type: 'set' } },
  { field: 'category', title: 'Category', filter: { type: 'set' } },
  { field: 'amount', title: 'Amount', type: 'number', total: 'sum' },
];

const matrixColumns = [
  { field: 'region', title: 'Region' },
  { field: 'category', title: 'Category' },
  { field: 'revenue', title: 'Revenue', type: 'number' },
];

function RevenueMatrix({ orders }) {
  const ordersRef = React.useRef(null);
  const [matrix, setMatrix] = React.useState(null);

  // Runs once the orders grid exists, reached through its ref.
  React.useEffect(() => {
    const ordersGrid = ordersRef.current && ordersRef.current.grid;
    if (!ordersGrid) return;
    setMatrix({
      mode: 'derived',
      from: ordersGrid,
      groupBy: ['region', 'category'],
      select: { revenue: { of: 'amount', fn: 'sum' } },
      sort: [{ col: 'revenue', dir: 'desc' }],
    });
  }, []);

  return (
    <>
      <LatticeGrid
        ref={ordersRef}
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        style={{ height: 320 }}
      />
      {matrix && (
        <LatticeGrid
          autoHeight
          columns={matrixColumns}
          source={matrix}
          style={{ marginTop: 16 }}
        />
      )}
    </>
  );
}

source takes the derived config directly, the same shape as the plain JavaScript example; only the way the grid is mounted changes.

The options used

Option	Type	What it does
from	Grid	The grid to read rows from.
groupBy	string | string[]	The dimension, or dimensions, to group by. Omit to pass rows through.
select	Record<string, { of, fn }>	The reduced columns, by output id. fn is any key of the totals row kernels, so sum, avg, median and p95 are as available as the plain sum used here.
sort	{ col, dir }[]	Order the derived rows before limiting them.

The pivot table it produces

Running the plain JavaScript example above against those twenty orders produces twelve rows, one for every region and category combination that has at least one order in it:

Region	Category	Revenue
AMER	Hardware	4800
EMEA	Software	4400
APAC	Hardware	3700
EMEA	Hardware	3600
AMER	Software	3500
AMER	Services	3500
APAC	Software	3000
EMEA	Services	2000
APAC	Services	1600
EMEA	Support	700
AMER	Support	600
APAC	Support	400

Filter the orders grid to AMER and the matrix drops to its four AMER rows, still summed, still sorted, with no second request behind it.

Read the derived and chained grids guide for grouping, joins and profiles in full, or see derived sources as a grid source option for how a derived grid fits beside every other way of feeding one.