Server Pivot
On a server grid the crossing goes out with every request and the column axis comes back with the rows. The client never crosses a server grid for itself.
pivot(). GetRowsRequest gains pivot and GetRowsResult gains pivotColumns, and the grid fetches again when the crossing changes.Why the Client Refuses
A grid on rowModel: 'server' holds the page or the blocks it has loaded, not the data. If it crossed those, every cell
would be a total over whatever happened to be loaded, and a table measured that way looks
exactly like a correct one. So it does not try. With serverRowModel() registered, it sends the crossing to the server and draws what comes back.
The grid below pages through seventeen countries, six at a time. Each figure is measured by the server over all 4,000 sales, not over the six rows on screen. Sort by a crossing, change the page, or put a second field across, and watch what is sent.
No data
request.pivot, as last sent
Nothing sent yet.
The answer
Nothing answered yet.
import {
createDataGrid, pagination, pivot, serverRowModel, sorting,
type DataSource
} from '@sv5ui/datagrid';
// A crossed row is not a sale. It carries its row fields, plus the two
// properties the grid reads its generated columns from.
interface CountryRow {
id: string;
country: string;
pivot?: Record<string, unknown>;
pivotTotal?: (number | null)[];
}
const source: DataSource<CountryRow> = {
async getRows(request) {
const response = await fetch('/api/sales', {
method: 'POST',
headers: { 'content-type': 'application/json' },
// request.pivot is the crossing. Pass it on with the rest.
body: JSON.stringify(request)
});
const body = await response.json();
// pivotColumns has to come back with the rows. It is where the grid
// learns the column axis from.
return { rows: body.rows, rowCount: body.rowCount, pivotColumns: body.pivotColumns };
}
};
const grid = createDataGrid<CountryRow>({
columns, // still declares every field the crossing names; country is pinned left
data: [],
getRowId: (row) => row.id,
rowModel: 'server',
features: [
sorting<CountryRow>(),
pagination<CountryRow>({ pageSize: 6 }),
serverRowModel<CountryRow>(source, { mode: 'paged' }),
pivot<CountryRow>({
rows: ['country'],
columns: ['quarter'],
values: [
{ field: 'amount', agg: 'sum', header: 'Amount' },
{ field: 'units', agg: 'sum', header: 'Units' }
],
totalColumn: 'Year',
sortableColumns: true
})
]
});What Goes Out
The crossing rides on every getRows request as pivot,
beside the sort and the filter. It is three lists and, while the reader has sorted by a
crossing, a fourth.
// GetRowsRequest gains one optional member.
interface PivotRequest {
/** Column ids gathered down the page, outermost first. */
rows: string[];
/** Column ids gathered across the page, outermost first. */
columns: string[];
/** What each cell holds, in the order the grid will draw the measures. */
values: { field: string; agg: string; per?: string }[];
/** Present only while the reader has sorted by a crossing. */
sort?: { keys: string[]; measure: number; direction: 'asc' | 'desc' }[];
}
// agg is always one of the named reductions. A measure reduced by a
// function, or by a percentile or weighted average, cannot cross the wire,
// so the grid sends no pivot at all rather than one missing a measure, and
// says so once in the console.
// per asks for one reduction over another: profit summed over amount
// summed. It is the reductions that divide, not the rows.PivotRequest
The type to give a handler. Exported from the package root.
| Member | Description |
|---|---|
rows | Column ids gathered down the page, outermost first |
columns | Column ids gathered across the page, outermost first |
values | What each cell holds, in the order the grid draws the measures. agg is one of the named reductions |
sort | The crossings the reader sorted by, outermost sort first. Absent when they sorted by nothing, or by a column that is not a crossing |
What Comes Back
The answer is rows that are already gathered, one per combination of the row fields, plus the keys of the column axis. A client grid reads that axis from the data. A server grid holds only part of the data, so the server has to say it.
// GetRowsResult gains one optional member.
const answer = {
rows,
rowCount: 17,
// One key path per generated column, outermost first. A single column
// field sends single-element paths.
pivotColumns: [['Q1'], ['Q2'], ['Q3'], ['Q4']]
};
// Each row carries its crossings under pivot, nested one level per column
// field and ending in one entry per measure, in the order values asked.
const oneField = {
id: 'Argentina',
country: 'Argentina',
pivot: { Q1: [12237, 498], Q2: [15690, 630] }
};
// With two column fields the nesting goes one deeper:
const twoFields = {
id: 'Argentina',
country: 'Argentina',
pivot: { Gadget: { Q1: [7462, 281] }, Widget: { Q1: [4775, 217] } }
};
// A crossing the row has nothing for is left out, and its cells draw empty.
// A totalColumn reads pivotTotal, one entry per measure. It is a property
// of its own because the total is measured over the row's own data, so it
// is not always the sum of what sits beside it.
const withTotal = { ...oneField, pivotTotal: [56082, 2248] };The crossings are nested rather than keyed by a joined path, so a key that happens to contain
a separator cannot be read as two. The row type gains pivot and pivotTotal as properties of your own declaring: the grid reads them by name and exports no type for
them.
pivotColumns each time an answer arrives. Send the keys of the page instead of the keys of the data, and
the columns move under the reader as they page or scroll. In the Answer
| Property | Description |
|---|---|
pivotColumns | On the result. One key path per generated column, each holding one key per column field. Required: without it no generated column is drawn |
pivot | On each row. One level per column field, ending in an array with one entry per measure |
pivotTotal | On each row, when totalColumn is set. One entry per measure |
Sorting
With sortableColumns on, a click on a generated header asks the server for that order. The client never reorders a
server pivot itself: it would be ordering the rows it holds, and the largest of six is not the
largest of seventeen.
// The reader clicks the Q2 Amount header.
request.pivot.sort; // [{ keys: ['Q2'], measure: 0, direction: 'asc' }]
// keys names the crossing the way pivotColumns does, and measure is its
// place in values. An empty keys is the total column:
// { keys: [], measure: 1, direction: 'desc' }
// A sort by a crossing is taken out of sortModel, so the two never say
// the same thing twice. A sort by the row field still arrives there:
request.sortModel; // [{ columnId: 'country', direction: 'asc' }]Changing the Crossing
The server row model fetches again when the crossing changes, the way it does for a sort or a
filter. Put a field on an axis, take one off or change a measure, from code, from the panel or the column menu, and a new request goes out carrying the new crossing. The buttons above the grid on this
page do nothing more than call setPivotColumns.
What triggers it is anything the request carries: the two axes and the measures. A reading
set with showAs never travels, so changing one asks for nothing. The grid says what happened through a pivotChanged event, which you can listen for as well.
// Each of these moves the crossing, and each is followed by a request.
grid.api.setPivotColumns?.(['product', 'quarter']);
grid.api.addPivotRowField?.('channel');
grid.api.setPivotValues?.([{ field: 'units', agg: 'sum' }]);
// Emitted whenever the axes or the measures change, on any grid.
grid.events.on('pivotChanged', ({ rows, columns }) => {
track('crossing', { rows, columns });
});What the Server Owns
Each of these needs every row, so the grid leaves it to the server, and says so once in the console when asked for it on a server grid.
| Asked for | What happens |
|---|---|
totalRow | Not drawn. Return the total as a row of its own, the way every other row is returned, and leave the option off |
valuesOn: 'rows' | The measures stay on the column axis, a leaf per measure |
orderColumns | The order the server sent in pivotColumns is kept |
keepRows | Every row the server sent is kept |
rowsBehind() | Answers an empty array. The client holds the crossed rows, not the rows they were measured from |
Telling the Two Apart
There are two ways to end up looking at unpivoted rows on a server grid, and serverHandled is what a toolbar reads to know which.
import { getPivot } from '@sv5ui/datagrid';
// On rowModel: 'server', is there a serverRowModel() to carry the crossing?
getPivot(grid)?.serverHandled;
// false on a server grid means pivot() is registered beside hand-written
// server wiring. The grid crosses nothing, draws its rows unchanged, and
// warns once: it will not total the rows it happens to hold.
// A backend that ignores request.pivot is the other way to end up with
// unpivoted rows. serverHandled is true then, and the grid draws what it
// was sent, since pivotColumns never arrived to build an axis from.