Units and Revenue by SKU: Unnest Order Lines

Whoever has to answer “what’s actually selling” does not find it on the orders grid at all. The orders table has one row per order; the SKU sits one level down, inside each order’s own line items, three or four to an order. So the answer gets built the slow way: export the orders, explode every order’s lines into their own rows in a spreadsheet, and pivot by SKU. It is correct for exactly as long as nobody touches the filter. Narrow the orders to one sales channel and the spreadsheet pivot still shows every channel, because it was built from an export, not from the grid, and nothing told it to run again. Flattening a nested array into a JavaScript table that stays live is the step the export was standing in for.

A derived grid skips the export. Point a second grid at the orders grid, unnest the lines array so each line becomes its own row, and group those lines by SKU: units sold and revenue, summed per SKU, straight from the orders grid’s own rows. Filter the orders grid to a channel and the SKU breakdown re-derives on the next frame, because it reads the orders grid’s live rows rather than a copy taken at export time.

That removes the pivot step entirely, not just the risk of it going stale. The SKU breakdown is a few lines of configuration next to the orders grid, not a recurring export somebody owns and reruns by hand.

Flatten the nested array in plain JavaScript

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

const orders = [
  { id: 1,  channel: 'Online',    lines: [{ sku: 'SKU-100', qty: 3,  value: 210 }, { sku: 'SKU-200', qty: 1, value: 85 }] },
  { id: 2,  channel: 'Wholesale', lines: [{ sku: 'SKU-300', qty: 10, value: 640 }] },
  { id: 3,  channel: 'Retail',    lines: [{ sku: 'SKU-100', qty: 2,  value: 140 }, { sku: 'SKU-400', qty: 4, value: 360 }] },
  { id: 4,  channel: 'Online',    lines: [{ sku: 'SKU-200', qty: 5,  value: 425 }] },
  { id: 5,  channel: 'Wholesale', lines: [{ sku: 'SKU-100', qty: 8,  value: 560 }, { sku: 'SKU-300', qty: 6, value: 384 }] },
  { id: 6,  channel: 'Retail',    lines: [{ sku: 'SKU-400', qty: 2,  value: 180 }] },
  { id: 7,  channel: 'Online',    lines: [{ sku: 'SKU-300', qty: 4,  value: 256 }, { sku: 'SKU-200', qty: 2, value: 170 }] },
  { id: 8,  channel: 'Wholesale', lines: [{ sku: 'SKU-100', qty: 12, value: 840 }] },
  { id: 9,  channel: 'Retail',    lines: [{ sku: 'SKU-200', qty: 3,  value: 255 }, { sku: 'SKU-400', qty: 1, value: 90 }] },
  { id: 10, channel: 'Online',    lines: [{ sku: 'SKU-100', qty: 1,  value: 70 }] },
  { id: 11, channel: 'Wholesale', lines: [{ sku: 'SKU-400', qty: 9,  value: 810 }, { sku: 'SKU-300', qty: 3, value: 192 }] },
  { id: 12, channel: 'Retail',    lines: [{ sku: 'SKU-100', qty: 5,  value: 350 }] },
  { id: 13, channel: 'Online',    lines: [{ sku: 'SKU-200', qty: 4,  value: 340 }] },
  { id: 14, channel: 'Wholesale', lines: [{ sku: 'SKU-300', qty: 15, value: 960 }] },
  { id: 15, channel: 'Retail',    lines: [{ sku: 'SKU-400', qty: 3,  value: 270 }, { sku: 'SKU-100', qty: 2, value: 140 }] },
  { id: 16, channel: 'Online',    lines: [{ sku: 'SKU-300', qty: 2,  value: 128 }] },
  { id: 17, channel: 'Wholesale', lines: [{ sku: 'SKU-200', qty: 7,  value: 595 }, { sku: 'SKU-100', qty: 4, value: 280 }] },
  { id: 18, channel: 'Retail',    lines: [{ sku: 'SKU-400', qty: 5,  value: 450 }] },
  { id: 19, channel: 'Online',    lines: [{ sku: 'SKU-100', qty: 6,  value: 420 }, { sku: 'SKU-300', qty: 1, value: 64 }] },
  { id: 20, channel: 'Wholesale', lines: [{ sku: 'SKU-200', qty: 2,  value: 170 }, { sku: 'SKU-400', qty: 4, value: 360 }] },
];

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',      title: 'Order' },
    { field: 'channel', title: 'Channel', filter: { type: 'set' } },
    { field: 'lines',   title: 'Lines' },
  ],
  rows: orders,
});

// A second grid whose rows are the orders' own line items, unnested and grouped.
const bySku = createGrid(document.querySelector('#by-sku'), {
  source: {
    mode: 'derived',
    from: book,          // the grid to read
    unnest: 'lines',     // one row per line item, keeping the parent's channel
    groupBy: 'lines.sku',
    select: {
      units:   { of: 'lines.qty',   fn: 'sum' },
      revenue: { of: 'lines.value', fn: 'sum' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
  columns: [
    { field: 'sku', title: 'SKU' },
    { field: 'units',   title: 'Units',   type: 'number' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ],
});

Filter the orders grid’s Channel column to Online and Wholesale, dropping Retail, and bySku re-totals every SKU down to just those two channels’ lines, with no second pass over the orders array 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 UnitsBySku({ orders }) {
  const [book, setBook] = React.useState(null);

  const orderColumns = [
    { field: 'id',      title: 'Order' },
    { field: 'channel', title: 'Channel', filter: { type: 'set' } },
    { field: 'lines',   title: 'Lines' },
  ];

  const skuColumns = [
    { field: 'sku', title: 'SKU' },
    { field: 'units',   title: 'Units',   type: 'number' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={skuColumns}
          source={{
            mode: 'derived',
            from: book,
            unnest: 'lines',
            groupBy: 'lines.sku',
            select: {
              units:   { of: 'lines.qty',   fn: 'sum' },
              revenue: { of: 'lines.value', fn: 'sum' },
            },
            sort: [{ col: 'revenue', dir: 'desc' }],
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the SKU panel’s from needs; the panel itself only renders once that instance exists. Filtering the orders grid’s Channel column moves the SKU panel the same way it does in the plain example, because both read the grid’s public API, not a copy of the rows taken at render time.

The options used

What the example actually produced

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

Filtering the orders grid’s Channel column to Online and Wholesale, dropping every Retail order, and re-reading bySku gives:

Every SKU’s units and revenue drop to match, in the same row set, because the totals were never anywhere but in the derived grid to begin with.

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.