Column Axis

Nested Columns

More than one entry in columns nests. The second field's values become header groups inside the first field's, and the header grows a tier per field. A group holds only the keys its own rows have, so the groups need not be the same width.

pivot<Sale>({
  rows: ['region'],
  // Outermost first. Products become header groups inside each quarter,
  // so the header grows a tier per field.
  columns: ['quarter', 'product'],
  values: [{ field: 'amount', agg: 'sum' }]
});

// A quarter only holds the products it sold. Q3 below sold gadgets and
// nothing else, so it has one column where the others have two.

Keys are ordered by the values themselves, so a number axis counts and a date axis runs in time, and each key is drawn through the formatter of the column it came from. A blank value gets a column of its own, headed by the grid's label for a blank.

Naming a Header

columnHeader replaces the text of a generated header. It receives the key as text, which tier it sits on, and the value as the data holds it. A blank arrives as the key (blank).

const months: Record<string, string> = {
  Q1: 'Jan to Mar',
  Q2: 'Apr to Jun',
  Q3: 'Jul to Sep',
  Q4: 'Oct to Dec'
};

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter', 'product'],
  values: [{ field: 'amount', agg: 'sum' }],

  // key    the key as text
  // depth  which column field it came from, 0 being the first
  // value  the value as the data holds it, before it became text
  columnHeader: (key, depth) => (depth === 0 ? (months[key] ?? key) : key)
});

// Naming a key does not move it. The order comes from the values, so
// 'Jan to Mar' stays where Q1 sat.

Naming a key does not reorder it. The order comes from the values, so a quarter renamed to its months stays where the quarter sat.

Stating the Keys

Left alone, the axis holds the keys the data has, in the order they sort in. columnKeys states them instead, and it is for two things. A key with no rows behind it still gets a column, which is how a report shows every quarter of the year including the one nothing happened in. And it gives an order the values do not have on their own: Low, Medium and High sort into the wrong order under every rule except the one only the application knows.

pivot<Sale>({
  rows: ['region'],
  columns: ['channel', 'quarter'],
  values: [{ field: 'amount', agg: 'sum' }],

  // Asked once per column field. Answer undefined to leave a field to
  // the data.
  columnKeys: (field) => {
    // An order the values do not have on their own.
    if (field === 'channel') return ['Web', 'Store'];
    // A key with no rows behind it still gets a column.
    if (field === 'quarter') return ['Q1', 'Q2', 'Q3', 'Q4'];
    return undefined;
  }
});

// A key the data has and the list leaves out is dropped, so answering a
// short list is also how a field is narrowed.

Both are at work above. Web comes before Store, which is not how the two sort, and each channel has a Q4 the data has never held. A key the data has and the list leaves out is dropped, so answering a short list is also how a field is narrowed.

Ordering by a Measure

A crossing comes out in the order its keys sort in, which answers where a thing is and not which thing is biggest. orderColumns ranks each key by what a measure makes of the rows beneath it. The regions below run alphabetically until a measure orders them; the total row is there so the ranking can be read off the page.

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

  // Rank the regions by what the first measure makes of them, largest
  // first. Every tier is ranked among its own siblings.
  orderColumns: { measure: 0, direction: 'desc' }
});

grid.api.setPivotOrderColumns?.({ direction: 'asc' });
grid.api.setPivotOrderColumns?.(undefined); // back to the order of the values

// Where a field has columnKeys, those win. An order the application
// states outranks one computed from the data.

Every tier is ranked among its own siblings, and keys the measure cannot rank fall to the end in the order they had. A field with columnKeys keeps the order it declared: an order the application states is a statement about meaning, and that outranks one computed from the data.

One consequence is worth knowing before turning it on. The readings along a row follow the order the crossing is drawn in, so a running total now runs through the columns as ranked, and a difference is from the column now beside it.

A Ceiling on Columns

A field with a thousand distinct values makes a thousand columns. maxColumns is the ceiling, 100 unless you set it, and past it the grid refuses rather than truncates. A table nobody can read is worse than no table, and quietly cutting the list would present a wrong table as a right one.

The crossing below needs eight leaf columns and is allowed six, so the grid draws its rows unchanged and writes the reason to the console. Raise the ceiling and the crossing draws, without the grid being built again.

pivot<Sale>({
  rows: ['quarter'],
  columns: ['region', 'product'],
  values: [{ field: 'amount', agg: 'sum' }],
  // Counted in leaf columns, which is what lands on screen: two measures
  // over the same crossings count twice.
  maxColumns: 6
});

// This crossing needs 8, so the grid refuses it. It draws its rows
// unchanged and says why in the console. It does not draw the first six.

grid.api.pivotMaxColumns?.();      // 6
grid.api.setPivotMaxColumns?.(20); // the crossing draws

The count is of leaf columns, which is what lands on screen. Two measures over the same crossings draw twice the leaves and count twice, and subtotal columns count as well. Moving the measures down the page buys back as many crossings as there were measures.

Folding

With columnGroups set to 'open' or 'closed', a reader can fold a group of the axis away, which is how a wide crossing is read one branch at a time. Folded, a group shows its subtotal and nothing else. Without a subtotal there would be nothing left to show, so the group folds to a narrow labelled rail instead.

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

createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    // The control a reader folds with is drawn by columnOps(). Without it
    // the groups fold through the api and nothing else.
    columnOps<Sale>(),
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter', 'product'],
      values: [{ field: 'amount', agg: 'sum' }],
      columnSubtotal: (header) => `${header} total`,
      // 'open' and 'closed' both make the groups foldable, and say which
      // state they start in.
      columnGroups: 'open'
    })
  ]
});

// Folded, a group shows its subtotal and nothing else. With no subtotal
// there is nothing to fold down to, so it folds to a narrow labelled rail.
grid.api.setPivotColumnSubtotal?.(undefined);
grid.api.setPivotColumnGroups?.('closed');

The control in each group header is drawn by columnOps(), so register that too, or the groups fold only through the api. Only groups fold, so a single column field has nothing to fold: with one field every generated column is already a leaf.

Sorting by a Crossing

sortableColumns lets a reader sort by a generated column, which orders the gathered rows by what the crossing measured rather than ordering any data. It is off by default, because that click reorders whole groups and does something different everywhere else in the grid. It needs sorting() registered. Click Q1, or Total.

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

createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    sorting<Sale>(),
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter'],
      values: [{ field: 'amount', agg: 'sum' }],
      totalColumn: 'Total',
      // Off by default. A click on a crossing header reorders whole
      // groups, which is not what that click does anywhere else.
      sortableColumns: true
    })
  ]
});

Tier Heights

A pivot's column axis builds a tier per field plus one for the measures. Three tiers at the row height stand taller than the rows they label, over keys as short as Q1. groupHeaderHeight is an option of the grid rather than of the pivot, and sets the height of each tier in pixels. It is described with headerHeight on Theming.

createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  // Per tier, in pixels. Two column fields and two measures make three
  // header rows; at the row height they stand taller than the keys in
  // them need.
  groupHeaderHeight: 28,
  features: [
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter', 'product'],
      values: [
        { field: 'amount', agg: 'sum', header: 'Amount' },
        { field: 'units', agg: 'sum', header: 'Units' }
      ]
    })
  ]
});

Finding a Generated Column

The id of a generated column is built from the keys it stands for, so it cannot be written down before the data arrives, and its format is not something to depend on. Two helpers answer the questions an id would have: isPivotColumn(id) says whether a column was generated, and getPivot(grid).crossingMeasure(id) says which measure it holds.

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

// A generated column's id is built from the keys it stands for, so it
// cannot be written down before the data arrives. Ask instead.

// Is this column one the pivot generated?
isPivotColumn(column.id);

// Which measure does it hold, by its place in values? undefined for a
// total, a subtotal, and any column that is not a crossing.
getPivot(grid)?.crossingMeasure(column.id);

// Both are what a predicate is for:
flashCells<Sale>({ columns: (column) => isPivotColumn(column.id) });

// And a formatting rule names the measure rather than a column:
conditionalFormatting({
  rules: [{ kind: 'colorScale', column: { measure: 0 } }]
});