¶Pivot (cross-tab)

Drag dimensions onto Rows and Columns, pick Values, and get a server-side cross-tab: row and column subtotals, margins and a grand total, over as many rows as the database can group.

Available in all three clients:

Nothing new is asked of the server. A pivot is a re-presentation of the protocol's existing group + aggregates, so it works on every driver and inherits the same allow-list.

¶Livewire

use Elyra\DataGrid\Livewire\DataPivot;

class SalesPivot extends DataPivot
{
    public array $rows = ['region'];
    public array $cols = ['status'];

    protected function definition(): GridDefinition
    {
        return Grid::table('sales')->key('id')
            ->columns(['region', 'status', 'category'])
            ->column('revenue', type: 'number', aggregatable: true);
    }

    /** Fields offerable on either axis. */
    protected function dimensions(): array
    {
        return [
            ['field' => 'region', 'header' => 'Region'],
            ['field' => 'status', 'header' => 'Status'],
        ];
    }

    /** Measures offerable in the Values bucket. */
    protected function measures(): array
    {
        return [['field' => 'revenue', 'fn' => 'sum', 'header' => 'Revenue']];
    }
}

¶Vue / Svelte

Same GridOptions a DataGrid takes; dimensions come from the declared columns.

<DataPivot :options="options" :rows="['region']" :cols="['status']"
  :values="[{ field: 'revenue', fn: 'sum', as: 'rev' }]" />

<DataPivot {options} rows={["region"]} cols={["status"]}
  values={[{ field: "revenue", fn: "sum", as: "rev" }]} />

Headless, if you want your own rendering:

import { loadPivot } from "@elyra/datagrid-client-core";

const model = await loadPivot(
  { rows: ["region"], cols: ["status"], values: [{ field: "revenue", fn: "sum", as: "rev" }] },
  options.fetch,
);

model.cell("Nord", "paid", "rev");   // a body cell, or null
model.rowTotal("Nord", "rev");       // that row's Total column
model.colTotal("paid", "rev");       // that column's Total row
model.grandTotal("rev");

¶Subtotals are correct for non-additive measures

This is the part most pivot implementations get wrong. Every subtotal, marginal and grand total is its own aggregate query — nothing is derived by summing the visible cells.

It matters as soon as a measure is not additive. With avg(revenue) over Nord = {10, 20, 5} split by status:

The subtotal is 35 / 3. It is neither the sum of the cells (20) nor their mean (10). Summing cells would produce a confidently wrong number, so the model exposes no way to do it. The same holds for median, percentile and countDistinct.

¶What it costs

A pivot is rows + 2 requests — one for the body, one per intermediate row level (for that subtotal line's per-column cells), one for the column axis and its marginals, and one for the grand total. They are independent and issued in parallel.

Requests are not queries: the engine runs one query per grouping level.

Budget before adding dimensions. GROUPING SETS would fold all of it into one query, but only PostgreSQL and ClickHouse support it among the five drivers, so the portable shape is several queries.

¶Why the extra requests exist

group: [A, B, C] returns aggregates for the prefixes of that list — A, A×B, A×B×C. For rows [A, B] and columns [C] that already covers the body cells and every line's Total column. But a subtotal line for A needs a value per column, i.e. GROUP BY (A, C) — which is not a prefix of [A, B, C]. Hence one more request per intermediate row level.

¶Keep the column axis low-cardinality

Each distinct value on the column axis becomes a rendered column. Pivoting on something like customer_id produces a column per customer, and both the DOM and the GROUP BY scale with it.

Group rows are capped per level by GridLimits::maxGroupRows (10 000). When a level hits the cap the response sets groupsTruncated, the model reports truncated, and all three clients show a notice — a partial cross-tab is never presented as complete.

¶Paging the row axis

The row axis is paged by its outermost dimension, so a pivot is not limited to maxGroupRows row groups. Every descendant of a selected outer group comes with it, which is what keeps the row tree whole — a page is a set of outer groups, not an arbitrary slice of leaf rows.

All three clients page at 50 row groups by default (rowPageSize; 0 turns it off and lets the server's cap bound the axis instead). Headless:

const model = await loadPivot(
  { rows: ["region"], cols: ["status"], values: [...], rowSkip: 0, rowTake: 50 },
  options.fetch,
);
model.rowAxisTotal;   // outer row groups in total, for building a pager

Changing the layout resets to the first page — an offset into a different axis means nothing.

Paging only applies to the body and subtotal queries, never to the column axis: those all group by rows[0] first, so they select the same page and their cells line up.

¶Security

The layout is user-editable, so every field is checked twice:

• The component only offers what dimensions()/measures() (Livewire) or the declared columns (Vue/Svelte) list.
• The engine re-checks each field against the definition's allow-list. Grouping requires filterable, and a measure requires aggregatable, so a tampered request cannot pivot on an undeclared column or aggregate one that was never marked aggregatable.

¶Accessibility

Dragging is an enhancement, not the only path. Each bucket has a <select> that does the same thing, every chip has a real remove <button>, and the drop zones are marked so they are not announced as controls. See Selection, keyboard & clipboard.