Revenue by Region: a Derived Grid, Not a Second Query

Every orders screen grows the same panel next to it: revenue by region, with its own query against the database or its own loop over the same array the table already holds. It agrees with the table on the day it ships. Then someone adds a region filter, or a date range, and the table narrows while the panel keeps showing the total for everyone, because nothing told it to run again. Now there are two numbers for “this quarter’s revenue” on one screen, and whichever one the reader trusts is the wrong half of the time. A group by inside the JavaScript data grid itself closes that gap.

A derived grid removes the second query instead of fixing it. Build the summary as a second grid whose rows come from the first: group the orders by region, reduce each group to a revenue sum, an order count and an average order size, and point it at the orders grid rather than at a dataset of its own. It reads whatever the orders grid’s filters leave, so filtering the orders table to a region, a quarter or a single customer re-derives the summary on the next frame, with nothing wired between them by hand.

That is one query to write instead of two, and one state that can be wrong instead of two that can disagree. The panel cannot drift from the table, because it is computed from the table, every time the table changes. Revenue by region becomes a few lines of configuration, not a query you write, test and keep in step.

Group by in a JavaScript data grid

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

const orders = [
  { id: 1,  region: 'EMEA',     amount: 420 },
  { id: 2,  region: 'APAC',     amount: 180 },
  { id: 3,  region: 'Americas', amount: 960 },
  { id: 4,  region: 'EMEA',     amount: 310 },
  { id: 5,  region: 'Americas', amount: 540 },
  { id: 6,  region: 'APAC',     amount: 275 },
  { id: 7,  region: 'EMEA',     amount: 890 },
  { id: 8,  region: 'Americas', amount: 150 },
  { id: 9,  region: 'APAC',     amount: 410 },
  { id: 10, region: 'EMEA',     amount: 220 },
  { id: 11, region: 'Americas', amount: 730 },
  { id: 12, region: 'APAC',     amount: 95 },
  { id: 13, region: 'EMEA',     amount: 605 },
  { id: 14, region: 'Americas', amount: 340 },
  { id: 15, region: 'APAC',     amount: 515 },
  { id: 16, region: 'EMEA',     amount: 175 },
  { id: 17, region: 'Americas', amount: 820 },
  { id: 18, region: 'APAC',     amount: 260 },
  { id: 19, region: 'EMEA',     amount: 450 },
  { id: 20, region: 'Americas', amount: 390 },
];

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',     title: 'Order' },
    { field: 'region', title: 'Region', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
  ],
  rows: orders,
});

// A second grid whose rows come from the first.
const byRegion = createGrid(document.querySelector('#by-region'), {
  source: {
    mode: 'derived',
    from: book,            // the grid to read
    follow: 'filtered',    // read whatever the filters leave
    groupBy: 'region',
    select: {
      revenue:  { of: 'amount', fn: 'sum' },
      orders:   { fn: 'count' },
      avgOrder: { of: 'amount', fn: 'avg' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
  columns: [
    { field: 'region',   title: 'Region' },
    { field: 'revenue',  title: 'Revenue',   type: 'number' },
    { field: 'orders',   title: 'Orders',    type: 'number' },
    { field: 'avgOrder', title: 'Avg order', type: 'number' },
  ],
});

Filter the orders grid to Americas and EMEA only, and byRegion drops from three rows to two, Americas and EMEA unchanged, APAC gone, with no second call anywhere in the example above.

React

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

// Build the component once, at module scope, not on every render.
const LatticeGrid = createLatticeGrid({ React, createGrid });

function RevenueByRegion({ orders }) {
  const [book, setBook] = React.useState(null);

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

  const regionColumns = [
    { field: 'region',   title: 'Region' },
    { field: 'revenue',  title: 'Revenue',   type: 'number' },
    { field: 'orders',   title: 'Orders',    type: 'number' },
    { field: 'avgOrder', title: 'Avg order', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={regionColumns}
          source={{
            mode: 'derived',
            from: book,
            follow: 'filtered',
            groupBy: 'region',
            select: {
              revenue:  { of: 'amount', fn: 'sum' },
              orders:   { fn: 'count' },
              avgOrder: { of: 'amount', fn: 'avg' },
            },
            sort: [{ col: 'revenue', dir: 'desc' }],
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the region panel’s from needs; the panel itself only renders once that instance exists. Filtering the orders grid, from its own header or a quick filter, moves the region panel the same way it does in the plain example, because both read the grid’s public API, not a copy of its rows.

The options used

What the example actually produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, and printing byRegion’s rows, gives:

Filtering the orders grid to Americas and EMEA and re-reading byRegion leaves exactly those two rows, with the same revenue, order count and average for each, which is the whole point: the summary never has its own copy of the numbers to get out of step.

The derived and chained grids guide covers joins, column profiles, chaining a derived grid from a derived grid, and cross-filtering back into the source in full. For a worked dashboard built the same way, with top sellers and a company-wide tile alongside the per-rep totals, see the sales dashboard demo.