Smart Grid Pivot New

Pivot turns the Grid into a cross-tab. Flat records go in, and the Grid renders them aggregated — a row hierarchy down the side, a column hierarchy across the top, one or more measures at the intersections, with subtotals and grand totals. It is driven by the pivot property bag and switched on with grid.view = 'pivot'.

The important part is that pivot is a native view, like card — the Grid keeps rendering the result itself rather than handing the area to another component. Everything the Grid already does keeps working on the aggregated table: frozen columns, cell-range selection, clipboard, conditional formatting, column resize and reorder, and xlsx export. Row groups are ordinary tree rows, so the expanders come for free, and the column axis becomes collapsible banded headers.

Live Demo

Two financial years of software bookings — 4,224 records across eleven territories, four product lines, four sales channels and three customer segments — with the designer docked beside the Grid. Drag a field between the Rows, Columns and Values wells to re-pivot, right-click a field for the same moves without dragging, or use the Filters tab to narrow the bookings. Switch Pivot Mode off to see the same records one row each.

Open the demo in a new tab

Quick Start

Bind the Grid to your flat records, describe the pivot, and switch the view.

<smart-grid id="grid"></smart-grid>

<script type="module" src="source/modules/smart.grid.js"></script>

const grid = document.querySelector('#grid');

// The facts: one record per booking, no aggregation of your own.
grid.dataSource = bookings;

grid.pivot = {
    rows: ['region', 'country', 'productLine'],   // down the side, outermost first
    columns: ['year', 'quarter'],                 // across the top, outermost first
    values: [{
        dataField: 'revenue',
        summary: 'sum',
        label: 'Revenue',
        formatSettings: { formatString: 'c0' }
    }],
    grandTotalRow: true,
    grandTotalColumn: true,
    rowSubtotals: true
};

grid.view = 'pivot';

That is the whole minimum. values is the only required part — with an empty values array the pivot does not build, because there is nothing to put in the cells.

Setting view during page load can run before the Grid has rendered, so build once more when it has. refreshPivot() is idempotent — it rebuilds from the source facts every time, so calling it twice costs a rebuild and nothing else.

grid.whenRendered(() => grid.refreshPivot());

Required Modules

The designer is the same element smart-pivot-table docks. The Grid reuses it rather than growing a second one: it takes a field list in and emits one change event.

if (typeof grid.refreshPivot !== 'function') {
    throw new Error('smart-grid: the pivot module is not loaded.');
}

The pivot Settings

Measures (Values)

Each entry in values becomes one column per column-axis leaf.

values: [
    { dataField: 'revenue', summary: 'sum', label: 'Revenue',
      formatSettings: { formatString: 'c0' } },
    { dataField: 'margin', summary: 'sum', label: 'Gross Margin',
      formatSettings: { formatString: 'c0' } },
    { dataField: 'deals', summary: 'count', label: 'Deals' }
]

Aggregators

The names are the ones Smart.DataAdapter.summarize implements, so the pivot and the Grid's summaryRow share one set of aggregators and cannot disagree about what a sum is. Read the list at runtime from Smart.Utilities.PivotEngine.aggregators:

sum, min, max, avg, count, product, median, stdev, stdevp, var, varp, unique, filled, blank

A custom aggregator is a function receiving the raw values, the underlying records and the measure descriptor — enough to compute a weighted or ratio measure, which is the usual reason to write one:

values: [{
    dataField: 'margin',
    label: 'Margin %',
    formatSettings: { formatString: 'p1' },
    summary: (values, records) => {
        const revenue = records.reduce((sum, record) => sum + record.revenue, 0);

        return revenue ? records.reduce((sum, record) => sum + record.margin, 0) / revenue : null;
    }
}]

Show Values As

A post-aggregation transform on a measure, from Smart.Utilities.PivotEngine.showValuesAsOptions.

runningTotal and rank operate on leaf rows only — a running total that counted the subtotals would double every group.

The Generated Model

grid.getPivotModel() returns what the engine produced and the Grid is rendering: { rows, columns, columnGroups, valueColumnIds }. The columns are emitted depth-first, which is what keeps every column group's members adjacent — the Grid requires that.

Each row carries bookkeeping the pivot writes for itself, and which you can read:

Value columns are named pv__<encoded column path>__m<measure index>; the grand total column uses __total as its path. Do not build these strings by hand — take them from model.valueColumnIds, which is the list in render order.

grid.addEventListener('pivotChange', (event) => {
    // { rows, columns, valueColumnIds }
    console.log(event.detail.rows + ' rows, ' + event.detail.columns + ' columns');
});

If your aggregates are computed elsewhere — on a server, or by a warehouse query — run the engine yourself and hand the result over with setPivotModel(model), or point the Grid at a different set of facts with setPivotSource(records).

Styling Pivot Rows

Because every row carries _pivotKind and _pivotDepth, group rows, leaves and the grand total can all be styled apart. Use onRowClass — it is recycle-safe, unlike setting attributes during render.

grid.onRowClass = (visibleIndex, data) => data && data._pivotKind
    ? 'pivot-' + data._pivotKind + ' pivot-depth-' + (data._pivotDepth || 0)
    : '';

smart-grid .pivot-group.pivot-depth-0 { background: #eef2f7; font-weight: 700; }
smart-grid .pivot-group.pivot-depth-1 { background: #f7f9fc; }
smart-grid .pivot-grandTotal { font-weight: 700; border-top: 2px solid #5b9bd5; }

A colour scale over the value cells is the way people actually read a pivot, and conditionalFormatting is the supported route to one — see How the Demo Is Built for the working recipe and the trap to avoid.

The Pivot Designer

smart-pivot-panel is a field list plus Rows / Columns / Values wells and a Filters tab. createPivotDesigner(container) creates one, wires its change event to grid.pivot and appends it to your element, so a drag re-runs the engine:

<div class="pivot-demo">
    <smart-grid id="grid"></smart-grid>
    <div id="designerHost" class="pivot-designer-host"></div>
</div>

<script type="module" src="source/modules/smart.grid.js"></script>
<!-- smart-pivot-panel, the designer, is registered by the pivot table module -->
<script type="module" src="source/modules/smart.pivottable.js"></script>

const designer = grid.createPivotDesigner(document.getElementById('designerHost'));

if (!designer) {
    // smart-pivot-panel is not registered - the page must also load smart.pivottable.js
}

The panel sizes itself to its host, so the host is the element that needs a height. Passing no container falls back to the Grid's own side panel — that is also what pivot.designer = true does. The side panel is an overlay on top of the data, which is the wrong shape for a panel you drag fields in while watching the result, so the docked layout is the one worth typing three extra lines for.

Right-click a field in the panel for the same moves without dragging — send it to Rows, Columns or Values, change its aggregator, or move it up and down its axis. Every action rewrites the pivot settings and rebuilds.

Related methods: getPivotDesigner(), refreshPivotDesigner(), destroyPivotDesigner(), and pivotDesignerFields() for the field descriptors the panel is handed.

Filtering

A pivot filter runs before aggregation, so the totals are recomputed from the records that survive rather than masked. The Filters tab in the designer writes through this same API, and the evaluator is Smart.Utilities.FilterGroup — the library's own — so a filter means the same thing here as it does anywhere else, conditions, case sensitivity and date handling included.

const filters = grid.getPivotFilters();   // [{ dataField, filter }]

grid.setPivotFilters(filters);            // replaces and rebuilds
grid.clearPivotFilters();                 // removes every filter and rebuilds

grid.addEventListener('pivotFilter', (event) => {
    const filters = event.detail.filters;

    // Recompute anything of your own that summarises the same records - a KPI strip
    // that keeps stating the unfiltered total while the table shows a subset is the
    // one thing a summary must never do.
});

To summarise the same records the pivot is aggregating, run the filters through the same evaluator:

const active = grid.getPivotFilters().length === 0
    ? bookings
    : bookings.filter((booking) => grid.getPivotFilters()
        .every((entry) => entry.filter.evaluate(booking[entry.dataField])));

Leaving Pivot Mode

const setPivotMode = (on) => {
    if (on) {
        grid.view = 'pivot';
        grid.refreshPivot();
        return;
    }

    // One batch: view, columns, groups and data are four separate re-renders otherwise.
    grid.beginUpdate();

    try {
        grid.view = 'grid';
        grid.columns = flatColumns();
        grid.columnGroups = flatColumnGroups();
        grid.dataSource = bookings;
    }
    finally {
        grid.endUpdate();
    }
};

Keep the flat definitions in functions rather than in variables assigned once, so switching back always hands the Grid fresh definitions. What the Grid does keep for you is the record set: the pivot remembers the facts it was given, so a rebuild never aggregates its own output.

Methods

Statics on Smart.Utilities.Grid.Pivot:

And on Smart.Utilities.PivotEngine: build(records, config), sortByValue(rows, columnId, sortOrder), topN(rows, columnId, count, sortOrder), plus the aggregators and showValuesAsOptions lists.

Events

Saving and Restoring a Layout

getPivotLayout() returns exactly what survives a trip through JSON.stringify — axes, measures, totals flags, collapsed column bands and serialized filters — so a saved layout means the same thing when it is loaded back. Function-valued settings (a custom aggregator, a sort comparator) cannot be written down, so they are carried over from the live configuration by setPivotLayout() rather than cleared.

localStorage.setItem('pivot-layout', JSON.stringify(grid.getPivotLayout()));

// later, after the Grid and its pivot settings are configured
const saved = localStorage.getItem('pivot-layout');

if (saved) {
    grid.setPivotLayout(JSON.parse(saved));
}

How the Demo Is Built

Four parts of the demo above are worth lifting, and two of them are traps worth knowing about.

The heatmap

The colour scale over the value cells is built with conditionalFormatting. It takes discrete rules with one colour each, so a gradient is a run of narrow between bands — nine per column in the demo — with the range taken from the leaf rows only. Including the subtotals and the grand total would put the top of the scale an order of magnitude above any real cell, and every leaf would come out the same pale shade. It also means the totals match no rule at all, so they keep the row styling that marks them out.

const model = grid.getPivotModel();
const rules = [];

for (const id of model.valueColumnIds) {
    // min/max over model.rows where row._pivotKind === 'leaf'
    for (let band = 0; band < BANDS; band++) {
        rules.push({
            column: id,
            condition: 'between',
            firstValue: low,
            secondValue: high,
            highlight: '#5b9bd5'   // six-digit hex, see below
        });
    }
}

grid.conditionalFormatting = rules;

Expanding on load

A report that opens showing three region rows is not showing anything. Expanding needs to happen after the render that builds the tree, not straight after refreshPivot(): setPivotModel replaces the data source inside a beginUpdate/endUpdate, and endUpdate schedules the render. Defer by a frame, and only on the first build — doing it on every one would undo the user's collapses each time the designer changed something.

let expanded = false;

grid.addEventListener('pivotChange', () => {
    if (expanded) { return; }

    expanded = true;

    requestAnimationFrame(() => grid.expandAllRows());
});

Collapse state across rebuilds

You do not have to do anything here — it is worth knowing it happens. Collapsed column bands are carried onto the incoming model on every rebuild, so a measure change, a sort, a filter or a trip through the flat view does not silently unfold everything the user had folded away.

A summary strip that moves with the filter

The KPI figures above the Grid in the demo are computed from activeBookings() — the records that pass the current pivot filters — and re-rendered on pivotFilter, using the same FilterGroup.evaluate the Grid applies. The two cannot disagree.

Troubleshooting