Totals That Follow However You Group: a Derived Grid

Three people ask the revenue ops analyst for the same orders, cut three different ways. The sales lead wants it by rep, to see who closed what this month. The regional VP wants the same numbers by region. Finance wants it by month once the quarter is close to turning over. It is one orders table every time; each ask today means a new pivot, or a new query with a different GROUP BY, because the summary that answers it was written for one grouping and has to be rewritten, or duplicated, for the next one.

What the three of them are actually asking for, without a name for it, is a dynamic group by grid: one summary panel that regroups itself to match however the orders table is grouped right now, instead of a query rebuilt by hand for every new ask.

A derived grid can take its grouping from the reader instead of from the query. Turn on a group-by panel on the orders grid, so dragging a column header into it groups the orders by whatever the reader picks, and point the summary grid at it with follow: 'grouped': instead of a dimension fixed when the page was built, it re-aggregates by whatever the orders grid is grouped by right now. Group the orders by rep and the summary reads rep. Regroup by region and it reads region, with nothing edited in the summary’s own configuration, because groupBy only covers the one case with nothing to follow: before anyone has grouped the source, follow: 'grouped' falls back to it, so the panel always shows a total rather than waiting for an instruction that has not arrived yet.

That turns three asks into one grid instead of three queries. The sales lead’s cut, the VP’s cut and finance’s cut are the same totals grid, regrouped at its own header by whoever is asking, and the analyst writes none of them by hand. One totals grid, however it ends up grouped, never needs a second version.

A dynamic group by grid, in plain JavaScript

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

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

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  groupPanel: { hint: 'Drag Region or Rep here to change how the totals group' },
  columns: [
    { field: 'id',     title: 'Order' },
    { field: 'region', title: 'Region' },
    { field: 'rep',    title: 'Rep' },
    { field: 'amount', title: 'Amount', type: 'number' },
  ],
  rows: orders,
});

// A second grid that reads whatever the orders grid is grouped by right now.
const totals = createGrid(document.querySelector('#totals'), {
  source: {
    mode: 'derived',
    from: book,
    follow: 'grouped',  // re-aggregate by the orders grid's own grouping
    groupBy: 'region',  // the fallback, read only before anyone groups the orders grid
    select: {
      revenue: { of: 'amount', fn: 'sum' },
      orders:  { fn: 'count' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
  columns: [
    { field: 'region',  title: 'Region' },
    { field: 'rep',     title: 'Rep' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
    { field: 'orders',  title: 'Orders',  type: 'number' },
  ],
});

totals declares a column for each dimension anyone might group by, Region and Rep. Whichever one the orders grid is actually grouped by is the column that comes back with values; the other sits empty on every row, because the derived rows only ever carry the dimension that is active. Drag the Rep heading into the group-by panel and totals swaps straight to rep totals, with nothing touched in its own configuration.

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 OrdersWithTotals({ orders }) {
  const [book, setBook] = React.useState(null);

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

  const totalColumns = [
    { field: 'region',  title: 'Region' },
    { field: 'rep',     title: 'Rep' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
    { field: 'orders',  title: 'Orders',  type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        groupPanel={{ hint: 'Drag Region or Rep here to change how the totals group' }}
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={totalColumns}
          source={{
            mode: 'derived',
            from: book,
            follow: 'grouped',
            groupBy: 'region',
            select: {
              revenue: { of: 'amount', fn: 'sum' },
              orders:  { fn: 'count' },
            },
            sort: [{ col: 'revenue', dir: 'desc' }],
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the totals panel’s from needs; the panel itself only renders once that instance exists. Grouping the orders grid, from its own panel or from book.columns.group(['rep']), moves the totals panel the same way it does in the plain example, because both read the grid’s own grouping state rather than a dimension passed in as a prop.

The options used

What the example actually produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, before anyone groups the orders grid, gives the groupBy fallback:

Then calling book.columns.group(['rep']), the same command the group-by panel runs when a reader drags the Rep header into it, and reading totals again, with no change to totals’s own configuration:

Twenty orders, the same twenty both times, read through two different groupings with the one source config: three regions first, three reps after, and neither number was looked up by hand.

The derived and chained grids guide covers follow, groupBy and the rest of the derived pipeline in full. For the same totals panel grouped on a dimension fixed in the config rather than chosen by the reader, see revenue by region from your orders grid.