Pivot Panel

The Panel

Grid.PivotPanel draws the four things a pivot is described by: the fields running down, the fields running across, the measures, and how the result reads. It holds no state of its own. It reads the grid and writes back through the same methods your code would call, so the two cannot drift apart.

<script lang="ts">
  import { columnOps, createDataGrid, DataGrid, Grid, pivot } from '@sv5ui/datagrid';

  const grid = createDataGrid<Sale>({
    columns,
    data: sales,
    getRowId,
    features: [
      // For the fold control the panel's "Fold groups" setting turns on.
      columnOps<Sale>(),
      pivot<Sale>({
        rows: ['region', 'product'],
        columns: ['quarter'],
        values: [{ field: 'amount', agg: 'sum' }]
      })
    ]
  });
</script>

<!-- The panel takes the grid as a prop, so it can sit anywhere on the
     page. Inside Grid.Root it reads the grid from context instead. -->
<Grid.PivotPanel {grid} />
<DataGrid {grid} />

The first three zones are ordered lists, because the order in them is the order of the tiers. Each chip has a button to move it either way and one to take it off, and each zone has its own button to add a field. There is no drag and drop, so everything is reachable from the keyboard and each zone is a labelled group to a screen reader. A measure's chip also carries its reduction and its reading, both named through the label table, so a translated grid translates them too.

The fourth zone appears once a crossing is on. It holds the total column, the total row, the subtotals, whether groups fold, whether a crossing can be sorted by, whether the measures run down the page, and how many rows to keep. The button that opens every tier is shown only while the row axis has more than one field, since a single field has nothing to open.

It is a starting point rather than the whole surface. The panel keeps the 5, 10 or 20 largest rows and offers no smallest; a measure added from it is a sum until its chip says otherwise; and per, header, orderColumns and maxColumns are set from code. It takes grid and class, draws nothing on a grid without pivot(), and is styled through the group panel slots.

The Column Menu

The column menu offers the two axes on any column that is on neither, and a way off for one that is. While a crossing is on, only the row fields are still drawn, so the menu is mostly how a pivot gets started rather than how it gets widened. The grid below opens flat. Open the menu on Region and pivot down, then on Quarter and pivot across.

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

const grid = createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    // The column menu is drawn by columnOps(). Without it the three
    // entries below have nowhere to appear.
    columnOps<Sale>(),
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter'],
      values: [{ field: 'amount', agg: 'sum' }]
    })
  ]
});

// A pivot has to be built with a field on each axis, so a grid that
// should open flat and let the reader start the crossing clears it first.
// The measures stay, ready for the fields the reader picks.
grid.api.clearPivot?.();

// On a column that is on neither axis, the menu offers
//   Pivot down this column     -> addPivotRowField(columnId)
//   Pivot across this column   -> addPivotColumnField(columnId)
// and on one that is,
//   Remove from pivot          -> removePivotField(columnId)

The menu is drawn by columnOps(), so that feature has to be registered. A pivot cannot be built with an empty axis, which is why this grid is built with one and cleared straight after.

Driving It From Code

Everything the panel does is on grid.api, twenty eight methods in four groups. All of them are optional there, since the grid that has them is the one that registered the feature.

// Every method is optional, because only a grid that registered pivot()
// has them. That is what the ?. is for.
grid.api.setPivotRows?.(['region', 'product']);
grid.api.setPivotColumns?.(['quarter']);
grid.api.setPivotValues?.([
  { field: 'amount', agg: 'sum', header: 'Amount' },
  { field: 'units', agg: 'sum', header: 'Units' }
]);

// A field lives on one axis. Adding it to the other moves it.
grid.api.addPivotColumnField?.('product'); // product leaves the rows

grid.api.setPivotTotalColumn?.('Total');
grid.api.setPivotTotalRow?.(undefined); // off

grid.api.isPivoting?.(); // what a toolbar reads to enable its buttons
grid.api.pivotRows?.();  // a copy, so changing it changes nothing

The Typed Way In

The api names its settings by their types, with one exception: a measure goes in and comes back as unknown, because a measure can carry a function over your row type and grid.api does not know that type. getPivot(grid) returns the state itself, with the row type kept and nothing optional, and it carries the measure editors the panel uses.

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

// grid.api types its settings, but a measure crosses it as unknown:
// setPivotValues takes unknown[] and pivotValues returns it. getPivot(grid)
// is the same state with the row type kept and nothing optional.
const state = getPivot(grid);
if (!state) throw new Error('pivot() is not registered');

state.rows;        // string[]
state.values;      // PivotMeasure<Sale>[]
state.keepRows;    // { top?, bottom?, measure?, others? } | undefined
state.isCrossing;  // a field on each axis and at least one measure

// The measure editors the panel uses, which grid.api does not carry:
state.addValue('units');                    // reduced with 'sum'
state.setValueAgg(1, 'avg');
state.setValueShowAs(0, 'percentOfRow');
state.removeValue(1);
state.moveRowField('product', -1);          // one step outward
state.moveColumnField('quarter', 1);        // one step inward

// The columns not yet on an axis, as { id, header }:
state.pivotableColumns();

In a Saved View

A crossing goes into the grid's snapshot with the rest of its state, so saved views and state persistence carry it without being told to. Save below, rearrange the crossing, and apply what you saved.

Nothing saved yet.

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

// A crossing rides in the grid's snapshot like any other feature state.
const snapshot = grid.api.getState();

snapshot.features?.pivot;
// {
//   rows: ['region'],
//   columns: ['quarter'],
//   columnGroups: 'fixed',
//   columnSubtotal: false,
//   sortableColumns: false,
//   valuesOn: 'columns',
//   totalColumn: 'Total',
//   values: [{ field: 'amount', agg: 'sum' }]
// }

grid.api.setState(snapshot);

// The slice is there while the crossing is off too, with both axes empty,
// which is how a view saved from a flat grid turns a crossing off.

// Not written: maxColumns, columnHeader, columnKeys, and which rows were
// open. Those belong to the page rather than to a reader's arrangement.

The slice is written whether or not a crossing is on, so a view carries the off state too. Try it: turn the crossing off, save, rearrange, and apply. The grid goes back to flat, because the view says both axes were empty.