Derived and chained grids

A grid built from another grid

A derived grid takes its rows from a grid you already have. Instead of loading it with data, you point it at another grid and describe the shape you want: the rows grouped and totalled, an array column expanded, the top few in each category, or a statistical profile of a column. The result is a real grid - it sorts, filters, exports and themes like any other - that stays in step with its source. Write to the source; the derived grid recomputes.

const book = createGrid(book_el, { columns, rows });

// A second grid whose rows come from the first.
const byRep = createGrid(panel_el, {
  source: {
    mode: 'derived',
    from: book,            // the grid to read
    follow: 'filtered',    // read whatever the filters leave
    groupBy: 'rep',
    select: {
      revenue: { of: 'amount', fn: 'sum' },
      deals:   { fn: 'count' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
  columns: [
    { field: 'rep',     title: 'Rep' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
    { field: 'deals',   title: 'Deals',   type: 'number' },
  ],
});

That is the whole idea behind a dashboard on this grid: a table of raw records, and beside it the panels a manager actually reads - revenue by rep, the best-selling products, this week's numbers - each one a derived grid, and all of them fed from the same source.

It follows the filter

By default a derived grid reads the filtered rows of its source. Filter the source - from a header, the quick filter, or a chart click - and every derived grid re-derives from what is left, on the next frame. There is nothing to wire together and nothing to keep in sync: the panels cannot disagree with the table they came from, because they are computed from it.

Grouping and derived columns

Group the rows with groupBy, then declare the columns you want out of each group in select. Each derived column names the field it reduces and the reduction to apply - sum, count, median, p95, distinct and more than thirty others. These are real columns: sort by revenue, filter to the groups above a threshold, export the summary, save it into a view.

Group by more than one field with an array, and rank or trim the result with sort and limit. limitPer applies the limit within each value of a column, so "the busiest agent in every queue" is one line rather than a query per queue.

Reshaping the rows

Grouping is the common case, but a derived grid can reshape its source in a few more ways, applied before the grouping runs. Together they cover the shapes a dashboard asks for without a data pipeline of your own.

unnest: 'lines',                    // an array column, one row per element
groupBy: 'lines.sku',               // then group on a field inside it
where: (row) => row.amount > 0,     // keep only the rows you want
bucket: { of: 'opened', by: 'week' }, // round a date down, group on the period
limit: 5,                           // keep the top five
limitPer: 'region'                  // ...or the top five in every region

• unnest - an array column becomes one row per element, so an order with three lines contributes three rows before anything is grouped. Group on a field inside it with a dotted path.
• where - a row test applied first, to keep only the rows that qualify. With no grouping it simply passes those rows through: an exceptions list, like the tickets that breached their target.
• bucket - round a date column down to the day, week, month, quarter or year and group on the period, turning a column of timestamps into a clean time series.
• cumulative - keep rows until their running share of the total reaches a fraction you set, for a Pareto "the few that make eighty per cent".

Joining a second grid

A derived grid can bring fields across from a second grid, matching on a shared key. The join runs before the grouping, so a group, a condition or a total can read a field it produced - group orders by the customer tier that lives in a customers grid rather than in the orders themselves.

source: {
  mode: 'derived',
  from: orders,
  join: {
    with: customers,             // a second grid
    on: 'customerId',            // the shared key
    select: ['name', 'tier'],    // the fields to bring across
  },
  groupBy: 'tier',               // group on a field the join produced
  select: { revenue: { of: 'amount', fn: 'sum' } },
}

It is an inner join by default, keeping the rows that matched, which is usually what "the orders we have a customer for" means. A left join keeps every row and leaves the brought-across fields empty, the shape you want when the unmatched rows are the finding.

A profile of a column

Where the other shapes give you one row per group, profile gives you one row per column, with the statistics as the columns: count, mean, standard deviation, the range and the outliers. It is the quality-control view of a measurement - the spread of a machined dimension across a batch - in a single declaration.

source: { mode: 'derived', from: batch, profile: ['bore', 'temp', 'cycle'] }
// one row per column: count, mean, stddev, min, max, outliers

Chaining, to any depth

A derived grid can itself be the source of another derived grid. Group the book into revenue per rep, then take the top three of that; profile the per-rep revenues to see how evenly the business is spread. Each level reads the one above it, and the chain composes as deep as the question needs.

const byRep = createGrid(a, {
  source: { mode: 'derived', from: book, groupBy: 'rep', select },
});

const topThree = createGrid(b, {
  source: { mode: 'derived', from: byRep, limit: 3 },
});

// Filter the book once, and byRep and topThree both follow.

The pay-off is a single point of control. A filter on the book at the root of the chain moves every level below it at once - the per-rep totals, the top three, the profile and the tiles - so the whole dashboard answers "just the APAC region" or "just this quarter" from one gesture, and always agrees with itself.

Cross-filtering: the path back up

Derivation runs one way. A derived grid reads its source and never writes to it, which is what makes a chain of grids safe to reason about. Cross-filtering is the single deliberate path back up: set crossFilter: true, and clicking a row in a summary panel filters the grid it summarises.

const byRep = createGrid(panel_el, {
  source: { mode: 'derived', from: book, groupBy: 'rep', crossFilter: true },
  columns: [{ field: 'rep' }, { field: 'total', type: 'number' }],
});

// Clicking a summary row filters the book it came from.
byRep.on('row:click', (e) => byRep.crossFilter.toggle(e.key));

It is an ordinary filter. The condition goes through the source's own filter model, so it undoes, rides in a saved view, and appears in whatever filter UI the grid already has - there is no second filter model beside the real one. Two summary panels over different columns then narrow each other while both stay whole: pick a rep and the regions panel shows that rep's regions, pick a region and the reps panel answers back.

Tiles that read the same rows

A dashboard usually opens with a row of headline figures. createStat draws one: a label, a value, and how it has moved. It reads a grid rather than a copy of the data, so a tile can never drift from the table beneath it, and it formats through the column's own type, so a figure in hours reads in hours with nothing declared.

createStat({
  grid: byRep,
  container: tile_el,
  title: 'Best rep',
  of: 'revenue', fn: 'max', show: 'rep',  // the name of the best rep, not the figure
  goodWhen: 'up',
});

createStat({
  grid: book,
  container: total_el,
  title: 'Company total',
  of: 'amount', fn: 'sum',
  scope: 'all',                           // hold the whole-company figure while the rest follow the filter
});

A tile follows the same filter as everything else, so the numbers across the top move with the table. scope: 'all' is the deliberate exception: hold a whole-set figure - the company total - steady while the filtered figures beside it change, and the contrast is the story. show turns a maximum into the name of the row that holds it, so "best rep" is a name rather than a number, and goodWhen colours the movement the right way for the measure.

See it running

Every one of these is a live demo, generating its data in your browser and following one filter:

• Chaining, three levels deep - a book, revenue per rep, the top three and a profile, with tiles reading the middle level.
• A sales dashboard - top reps by grouping, best sellers by unnesting the order lines, and a tile that holds the company total.
• A production line - a measurement profile, an out-of-tolerance list, and process capability in a tile.
• A support desk - tickets by week, the busiest agent per queue, the commonest tags, and the breaches.

Derived grids pair naturally with shadow columns, which follow the same data the other way - how a single row has moved since the page loaded - and with the charts module, which draws the same filtered rows.