Data Grid Row Grouping, Totals and Pivot

Developer guide › Sorting, filtering and find › Data Grid Row Grouping, Totals and Pivot

Grouping, totals and pivot

grid.columns.group(['region', 'country']);
grid.rows.expandAll();
grid.rows.collapse('EMEA');

Expand all and Collapse all are on the menu too, once the grid is grouped: the data area's own right-click menu and every column's header menu (the 3-dot button and a right-click on the heading alike) carry both, driving grid.rows.expandAll()/collapseAll() - the same public API a host calls directly. They are hidden, not disabled, on an ungrouped grid: there is nothing for either to act on. Both flow through the same contextMenu/ columnMenu customisation chain as every other built-in item, so a host filtering or extending the menu sees them as ordinary items - matched, like every other built-in, by their translated name (catalogue keys menu.expandAll and menu.collapseAll, the same ones the generated group column's own header menu already used - see your own menu items).

groupPanel: true adds a drag-and-drop strip above the column header - the row-group panel. A user drags a heading into it to group by that column; the active groups show as removable, reorderable chips, and dragging one chip past another changes the nesting order. It is keyboard-operable, so grouping is not drag-only: arrows move between chips, Shift with an arrow reorders, Delete ungroups, and an add control at the end groups any column. Every change is spoken through the live region. The strip drives grid.columns.group() - it is the same grouping model, surfaced as chrome - so a group made in the strip, from the column menu or through the API is one state, not three.

const { createHeadlessGrid } = await import('../packages/core/src/index.js');

// `groupPanel` is chrome, so the strip itself needs the DOM build; the config
// key is accepted everywhere, and it drives the ordinary grouping model - which
// is what a headless grid can show. The strip renders the state below as chips.
const grid = createHeadlessGrid({
  columns: [{ field: 'region' }, { field: 'country' }, { field: 'sales', type: 'number' }],
  rows: [
    { region: 'EMEA', country: 'UK', sales: 10 },
    { region: 'EMEA', country: 'DE', sales: 20 },
    { region: 'AMER', country: 'US', sales: 30 },
  ],
  groupPanel: true,
});

// Order is nesting order, outermost first - exactly the order the chips show.
grid.columns.group(['region', 'country']);
const groups = grid.state.get().group;
grid.destroy();
return `grouped by ${groups.join(', ')}`;

kpis is the same idea for the tile every dashboard opens with. A createStat renders a KPI tile - a label, a value, its change against a baseline - reading the grid so it agrees with the grid; what it needs is a container and the wiring to place it. kpis is that placement done by the grid: an array of stat specs becomes a labelled band of tiles above the column header, and the grid creates the container for each and drives createStat itself. Each entry takes the fields createStat takes - of, fn, title, interval, footer, format and the rest - minus grid and container, which the grid supplies. The tiles follow the grid's filters, so the strip cannot disagree with the table beneath it. Off by default and free when absent; no kpis, no band.

const { createHeadlessGrid } = await import('../packages/core/src/index.js');

// `kpis` is chrome: with createGrid it renders a labelled band of stat tiles
// above the header, each following the grid's filters. The config is accepted
// everywhere, and each tile reads the same kernel the totals row uses - which
// is what a headless grid can show.
const grid = createHeadlessGrid({
  rowKey: 'region',
  columns: [{ field: 'region' }, { field: 'mrr', type: 'number' }],
  rows: [
    { region: 'EMEA', mrr: 1200000 },
    { region: 'AMER', mrr: 2400000 },
    { region: 'APAC', mrr: 600000 },
  ],
  kpis: [
    { title: 'Accounts', fn: 'count' },
    { title: 'Revenue', of: 'mrr', fn: 'sum' },
  ],
});

// The value the "Revenue" tile would read, from the kernel createStat uses.
const revenue = grid.statistics.reduce('mrr', 'sum');
grid.destroy();
return `revenue ${revenue}`;

{ field: 'capacity', type: 'number', total: 'sum' }
{ field: 'margin',   type: 'number', total: 'avg' }
{ field: 'lastSeen', type: 'datetime', total: 'max' }

{ field: 'weighted', total: (values, rows) => weightedMean(values, rows) }

Totals appear on every group row and on the grand total. grandTotalRow: 'bottom' pins the grand total below the rows instead of leaving it inline.

On a memory source totals are maintained incrementally: a cell update moves the running value by the difference rather than re-reducing the column, so a totals row costs the same on a million rows as on a thousand. This applies to both the grand total and each group subtotal, for sum, avg, countValues, min and max on numeric columns. A cell edit that moves a row between groups is subtracted from its old group and added to its new one; only the affected groups are touched. Everything else re-reduces, and so does the incremental path itself whenever it cannot reach the right answer:

Nothing has to be configured for this, and the reported number is the same either way, where a running value cannot be trusted, the column falls back to a full pass rather than reporting a value it is unsure of.

headerControls

The per-column header controls - the sort arrow, the filter funnel and the menu button - appear on hover by default, which keeps a wide header from reading as a row of identical icons. headerControls makes that a mode: 'hover' (the default, unchanged), 'always' to keep them visible, 'hidden' for a clean read-only heading that draws none of them and leaves them out of the tab order, and 'none' for a heading that shows its title and nothing else, whatever the grid's state. It is a grid-level default; a column's own headerControls overrides it for that column.

Each leaf heading carries the resolved mode as data-controls, and the theme keys the reveal off it, so 'always' shows the controls without a hover and 'hidden' removes them from the layout. This differs from showColumnFunctions: false, which also drops the furniture but keeps sorting, filtering and the menu reachable from the keyboard; 'hidden' is the read-only choice that takes them away outright.

'hidden' still shows one thing: a read-only sort arrow and multi-sort order number on a column that is actually sorted, so a read-only grid is not silent about its own order. 'none' removes that too - no sort badge, no filter badge, no group badge, ever, even when the column is sorted, filtered or grouped programmatically or from a saved view. aria-sort keeps reporting the true state either way; only the visual badge is gone under 'none'. It is for a heading that must read the same no matter what else happens to the grid around it - a fixed dashboard title, say.

const { createHeadlessGrid } = await import('../packages/core/src/index.js');

const grid = createHeadlessGrid({
  headerControls: 'hidden',                       // the read-only default for every column
  columns: [
    { field: 'name' },                            // follows the default: hidden
    { field: 'region', headerControls: 'always' }, // this column overrides the default
    { field: 'qty', type: 'number' },             // follows the default: hidden
  ],
  rows: [{ name: 'a', region: 'x', qty: 1 }],
  rowKey: 'name',
});

// Resolve each column the way the header renderer does: its own value wins,
// then the grid default. Only the overriding column shows its controls.
function shown(id) {
  const own = grid.columns.get(id).def.headerControls;
  return (own || 'hidden') !== 'hidden';
}
const visible = ['name', 'region', 'qty'].filter(shown).length;
grid.destroy();
return visible;

filterRow

Filtering came in three shapes and none of them was inline: the column menu's popup, the quick filter over every column, and the structured filter in the tool panel. A user who wants “just type in the column” had no row to type into. filterRow: true draws one, directly under the headings - below the column groups, above the pinned rows, in every pinned region.

Each cell is the inline form of that column's own filter. A text column gets a box with contains implied and a small picker for the other single-operand operators; a number column implies equals; a date column implies equality too, which on a date means on that day - the same day-wide half-open range the popup produces, because the row asks the column's own DateFilter for the condition rather than building one. A boolean column gets a tri-state box that cycles any → true → false. A set or multi column gets a chip that opens the column's own filter popup, because the value list is a virtualised checkbox list and it belongs in the popup rather than in an 80px cell. So does a condition the row cannot draw inline - a range, a relative date, two conditions joined: the cell shows a chip naming the operator, and pressing it opens the popup.

There is one filter model and the row writes it. Typing in the row is what the column menu shows when it is opened, an edit in the menu repaints the row, state.get() round-trips it because the filter tree is the state, and a pushdown source receives exactly the condition the menu would have sent - there is no second pushdown path. Clearing a cell clears that column's filter and leaves every other column's alone.

A column opts out with its own filterRow: false, which leaves that cell empty; so does filter: { enabled: false }, because there is nothing to type into. The grid-level key is what decides whether there is a row at all - a row drawn for one column and blank for the rest is not a thing to offer. headerControls: 'none' and showColumnFunctions: false do not take the row away: those govern the chrome a heading carries, and the filter row is data entry.

The row is one tab stop with a roving tabindex; left and right arrows move between cells, and inside a text box they only leave the cell once the caret is already at that end, so an arrow press is never stolen from the text the user is editing. Escape puts the cell back to what grid.filters holds. Every control names the column it filters (“Filter Region”), and the row itself carries role="row" and is counted in aria-rowindex, so the body rows are numbered past it.

It costs nothing on the scroll path. The row is appended inside the same three header region containers the headings live in, so the compositor transform that carries the header sideways carries the filter cells with it: measured on a 1,000,000-row, 42-column grid, a scrolled frame cost 16.55 ms with the row and 16.41 ms without it, and over sixty scrolled frames the worst gap between a heading and its own filter cell was 0 px. There is no debounce and none is needed: one keystroke's filter over a million rows settles in about 59 ms, well inside the gap between two keystrokes, so nothing queues behind the caret.

const { createTestDom, flushFrames, TestEvent } = await import('../packages/dom/src/renderer/testdom.js');
const { createGrid } = await import('../packages/dom/src/index.js');
const { branchFor } = await import('../packages/dom/src/filtermenu.js');
const { root, document: doc } = createTestDom({ width: 800, height: 300 });

const grid = createGrid(root, {
  filterRow: true,                                  // the row itself
  columns: [
    { field: 'name' },                                // text: a box, "contains" implied
    { field: 'region', filter: { type: 'set' } },     // set: a chip opening the popup
    { field: 'qty', type: 'number' },                 // number: a box, "equals" implied
    { field: 'note', filterRow: false },              // opted out: an empty cell
  ],
  rows: [
    { name: 'alpha', region: 'EU', qty: 1, note: 'a' },
    { name: 'bravo', region: 'US', qty: 2, note: 'b' },
    { name: 'alps', region: 'EU', qty: 3, note: 'c' },
    { name: 'delta', region: 'US', qty: 4, note: 'd' },
  ],
  rowKey: 'name',
});
flushFrames();

const cell = (id) => root.querySelectorAll('.lat-filter-cell')
  .find((c) => c.getAttribute('data-col') === id);
const kinds = ['name', 'region', 'qty', 'note'].map((id) => cell(id).getAttribute('data-kind'));

// Type into the name column, the way a user does.
const box = cell('name').querySelector('.lat-filter-row-input');
box.value = 'alp';
box.dispatchEvent(new TestEvent('input', { bubbles: true }));
flushFrames();
const typed = grid.rows.count();

// The column menu opens on the same model, so it shows what was typed.
grid.emit('column:filter:open', { colId: 'name' }, 'user');
const popup = doc.body.querySelectorAll('.lat-filter-popup')[0];
const seen = popup.querySelectorAll('input')
  .find((i) => String(i.className).includes('__value')).value;

// Emptying the cell clears that column's branch and nothing else.
box.value = '';
box.dispatchEvent(new TestEvent('input', { bubbles: true }));
flushFrames();
const cleared = grid.rows.count();
const gone = branchFor(grid.filters.get(), 'name') === null;
grid.destroy();

return `kinds ${kinds.join(',')} | typed ${typed} rows | menu sees ${seen} | `
  + `cleared ${cleared} rows${gone ? '' : ' (branch survived!)'}`;

showTotalInHeader

Under grouping or pivot, a totalled column's cells hold an aggregate rather than a row's own value. On by default, the heading says which, a small SUM line above Capacity, AVERAGE above Margin. The heading returns to the column's own title when grouping and pivot are both off.

showTotalInHeader: false

The reduction goes on its own line rather than reading Sum of Capacity across one. A header cell reserves width for its sort, filter and menu buttons whether or not they are showing, so the label gets well under half the column: on a default column, 54px of 129px. One line truncated to Sum o…, trading the column's identity for its reduction. Stacked, it costs no width at all.

Such a heading carries data-total on its header cell, naming the reduction, and its two lines are .lat-header-total-fn and .lat-header-total-name. --lattice-header-total-size and --lattice-header-total-color set the reduction line's size and colour; the full phrase is on the label's title for the pointer. Where a pivot has a single value column its leaf is titled with the pivot value rather than the column's name, and that heading is left alone: it is naming the category, not the measure.

A total beside the pivoted columns

pivot: { groupTotals: 'after' }        // or 'before'; omitted, none
pivot: { groupTotals: 'after', totalsLabel: 'All regions' }

totalFilteredOnly

totalFilteredOnly: false

By default a total describes what is on screen: filter the grid and every total moves with it. Setting this to false makes the filter a lens instead: totals report the whole dataset no matter what is filtered out. Both the grand total and each group total follow the setting, so a group row shows the total for every row belonging to that group, not only the ones currently visible.

A group whose every row the filter removed has no row to appear on, but its rows still count toward the totals above it. Editing a hidden row moves the totals, because it is part of the dataset they describe: under the default it does not, because it is not part of the view they describe.

The count shown on the grand total row follows the total, so it never reports fewer rows than the total covers. Group row counts stay as the number of rows a user can expand to see, which is what that number is for.

The unfiltered row set and its grouping are computed once and reused, so the cost lands when rows are added or removed rather than on every edit or filter change. Reducing over the full dataset is more work than reducing over a filtered subset: on 500,000 rows grouped and half-filtered, a single-cell update measured 2.1ms by default and 3.2ms with this off.

totalOnlyChangedColumns

totalOnlyChangedColumns: true

By default the total stage considers every totalled column on every change, even columns the change did not touch. Switching this on considers only the columns whose values actually moved, and an update that rewrites a field with the value it already held reduces nothing at all, which is what a feed resending unchanged fields looks like. This narrows which columns are looked at; the incremental grand total and group subtotals described above narrow how each one is brought up to date, and the two compound.

On a million rows in seven groups, a single-cell update with four totalled columns:

The saving is proportional to the totalled columns an update leaves alone, so there is nothing to gain when every totalled column changes on every update.

It is off by default because it is an assertion, not just an optimisation. Skipping a column assumes its total depends on nothing but that column's own values. That is true of every built-in reduction. It need not be true of a total supplied as a function, which also receives the row, the grid and config.context: such a total is only recomputed when its own column changes, so a function that reads application state outside the column will report the value from the last time that column moved. Leave the option off if any of your total functions work that way.

Anything that changes which rows a total covers: adding or removing rows, filtering, sorting, grouping, or changing which columns are totalled: reduces everything again regardless of the option.

grid.columns.group(['region']);
grid.columns.pivot(['statusId']);   // a column per distinct status