Row Axis

Nested Rows

More than one entry in rows nests, the way grouping nests: the second field gathers inside the first. Nested rows start shut, so the grid opens on the outermost field and a reader goes deeper where they choose to.

pivot<Sale>({
  // Outermost first. Products gather inside each region.
  rows: ['region', 'product'],
  columns: ['quarter'],
  values: [{ field: 'amount', agg: 'sum' }],
  totalColumn: 'Total'
});

// Nested rows start shut. A region opens onto its products, and a
// product opens onto nothing: it is the innermost field.

A gathered row opens onto the next row field and onto nothing else. The innermost field does not open at all, and with a single row field no row does. That is deliberate: a pivot has put away every column that would say what the rows underneath are, so opening onto them would show a name repeated and a run of empty cells. The question a reader has at that point is answered by rowsBehind instead.

// Opens every tier at once. One pass per tier, since opening a tier is
// what reveals the next.
grid.api.expandAllPivotRows?.();
grid.api.collapseAllPivotRows?.();

// Whether any row is open. It reads the grid rather than a flag of its
// own, so one row opened by hand is enough to make it true.
grid.api.pivotRowsOpen?.();

// A single row is opened the way any expandable row is:
grid.expansion.toggle(node.id);

Keeping the Top Few

A crossing of forty regions is a page nobody reads. keepRows keeps the largest or the smallest few, ranked by what a measure makes of each gathered row. The ranking is measured from the rows, the way a total column is, and it is measured whether or not a total column is drawn.

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [{ field: 'amount', agg: 'sum' }],
  totalColumn: 'Total',
  totalRow: 'All regions',

  // The two largest regions, ranked by the first measure.
  keepRows: { top: 2 }
});

// What is left out is left out of everything. The total row now covers
// two regions, so the page adds up to what it shows.

// others gathers the rows that lost into one more row under that name,
// measured from themselves, and the totals cover everything again.
grid.api.setPivotKeepRows?.({ top: 2, others: 'Everything else' });

grid.api.setPivotKeepRows?.({ bottom: 2 });  // the two smallest
grid.api.setPivotKeepRows?.(undefined);      // every row

Watch the total row as you switch. The cut happens before anything is measured, so what is left out is left out of everything: with the top two kept, the total row covers two regions and the page adds up to what it shows. The rows that are kept come out in ranked order. others is the way to keep the whole. The rows that lost are gathered into one more row under that name, measured from themselves, and the totals return to where they were.

With more than one row field the cut applies to the outermost, which is the one the question is about, and a row that survived still opens onto every field under it. A reading set by showAs is left out of the ranking, since ranking by a percentage of its own row would put every row in the same place.

The Rows Behind a Cell

rowsBehind(node, columnId) answers which rows a cell was measured from, which is the first question a reader asks of a number they did not expect. It hands back the rows themselves, over the rows a filter left, so what comes back matches what is on screen. Click any figure below, a total included.

import { createDataGrid, getPivot, pivot } from '@sv5ui/datagrid';

const grid = createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter'],
      values: [{ field: 'amount', agg: 'sum' }],
      totalColumn: 'Total',
      totalRow: 'All regions'
    })
  ]
});

// Which rows was this cell measured from? It is the first question a
// reader asks of a number they did not expect.
function onClick(event: MouseEvent) {
  const cell = (event.target as HTMLElement).closest('[data-dg-cell]');
  if (!cell) return;

  // data-dg-cell holds the absolute row and column index.
  const [rowIndex, colIndex] = cell.getAttribute('data-dg-cell')!.split(':').map(Number);
  const node = grid.preWindowNodes[rowIndex];
  const column = grid.columns.visible[colIndex];
  if (!node || !column) return;

  // The rows themselves, over the rows a filter left. An empty array for
  // a column that is not part of the crossing, such as the row field.
  const rows = getPivot(grid)?.rowsBehind(node, column.id) ?? [];

  // The same thing on grid.api, where the rows come back as unknown[]:
  const untyped = grid.api.pivotRowsBehind?.(node, column.id) ?? [];
}

// policy() masks what the grid renders, copies and exports. rowsBehind()
// returns rows, and a row has not been through that mask.
const rows = getPivot(grid)?.rowsBehind(node, column.id) ?? [];

// So mask again before drawing them, with the same rule the grid used:
const safe = rows.map((row) => ({ ...row, profit: canSeeProfit ? row.profit : null }));

// or do not offer the drill-down on a grid that masks.

What Survives

What a reader opened survives a filter, and survives the crossing being turned off and on again, the way grouping and tree data keep it. It does not survive a saved view: a snapshot holds the axes, the measures and the settings, not which rows were open.

On a long nested axis, the row a reader is inside scrolls out of sight. Sticky Groups keeps it pinned under the header, and works over a pivot because a pivot's rows are groups.