Pivoting and Grouping

Create pivot tables with row/column grouping, aggregations, sorting, filtering, totals, drilldown, and slicer integration

Rowsncolumns Spreadsheet ships a full pivot-table authoring experience powered by DuckDB-WASM. Pivots run the same OOXML pivotCacheDefinition / pivotTable round-trip Excel uses, render through the existing CanvasGrid, and accept slicer-driven filtering on top of the regular sort + label-filter pipeline.

Features at a glance

• Row / column / value fields with drag-to-reorder and per-field hide

• Subtotals + grand totals toggles (per-pivot, separate row + column)

• Aggregation functions: sum, count, avg, min, max, var, stddev, median, product

• Show Values As — % of Grand Total, % of Row Total, % of Column Total, Running Total, Rank ↑ / Rank ↓

• Field grouping — date (year / quarter / month / week / day) or numeric (bucket size + optional offset)

• Sort — tri-state ↑ / ↓ / none on every field, including value-field sort

• Label filter — per-field popover with searchable checkbox list

• Top N filter — Top / Bottom × N by any value field

• Refresh button + auto-refresh on source mutation

• Drilldown — double-click any value cell to see the underlying source rows

• Slicer ↔ pivot wiring — slicer selection applies as a label filter to bound pivots

• GETPIVOTDATA() — formula-level lookup into a rendered pivot

• XLSX round-trip — full pivotCacheDefinition + pivotTable emission with refreshOnLoad="1"

Installation

npm install @rowsncolumns/pivot

DuckDB-WASM is bundled as a dependency.

Basic setup

usePivot returns the full callback surface; pass the same callbacks to CanvasGrid and to the PivotEditor component:

import {
  usePivot,
  PivotEditor,
  NewPivotTableDialog,
  NewPivotTableEditor,
  createPivotTableFormats,
} from "@rowsncolumns/pivot";

const SpreadsheetWithPivot = () => {
  const [pivotTables, onChangePivotTables] = useState<PivotTable[]>([]);

  const {
    activeCell,
    activeSheetId,
    sheets,
    sheetData,
    onChangeSheets,
    onChangeSheetData,
    onChangeBatch,
    getEffectiveValue,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    cellXfsRegistry,
    sharedStringRegistry,
  } = useSpreadsheetState({
    onChangePivotTables,
    /* ... */
  });

  const getGridValues = useCallback(
    (range: SheetRange) => {
      const headers: any[] = [];
      const rows: any[][] = [];
      for (let r = range.startRowIndex; r <= range.endRowIndex; r++) {
        const row = [];
        for (let c = range.startColumnIndex; c <= range.endColumnIndex; c++) {
          row.push(getEffectiveValue(range.sheetId, r, c) ?? "");
        }
        if (r === range.startRowIndex) headers.push(...row);
        else rows.push(row);
      }
      return { headers, rows };
    },
    [getEffectiveValue],
  );

  const {
    // Create / delete / edit
    onCreatePivotTable,
    onRequestCreatePivotTable,
    onRequestEditPivotTable,
    onRequestDeletePivotTable,
    activePivotId,
    onClosePivotSettings,

    // Field management
    addRowPivot,
    addColumnPivot,
    addValue,
    removePivotField,
    changeFieldOrder,
    setAggregationFunction,

    // Source range
    updatePivotSourceRange,

    // Totals toggles
    toggleSubtotals,
    toggleRowGrandTotals,
    toggleColumnGrandTotals,

    // Sort + filter
    sortPivotField,
    removeSortPivotField,
    addFilter,
    removeFilter,

    // Show Values As + grouping
    setShowAs,
    setFieldGrouping,

    // Refresh + drilldown
    refreshPivot,
    executePivot,
    onDrillDownAtCell,

    // Slicer integration
    applySlicerSelectionToPivots,

    // Transforms
    transformPivotResults,
    transformChildRows,
    expandRowPivot,
  } = usePivot({
    locale: "en-US",
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    sharedStringRegistry,
    onChangeSheets,
    onChangePivotTables,
    onChangeSheetData,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    getGridValues,
  });

  const activePivotTable = pivotTables.find((t) => t.pivotId === activePivotId);

  return (
    <>
      <CanvasGrid
        pivotTables={pivotTables}
        onRequestEditPivotTable={onRequestEditPivotTable}
        onRequestDeletePivotTable={onRequestDeletePivotTable}
        onFilterPivot={applySlicerSelectionToPivots}
        /* ... */
      />

      {/* Pivot editor sidebar */}
      {activePivotTable && (
        <PivotEditor
          pivotTable={activePivotTable}
          updatePivotSourceRange={updatePivotSourceRange}
          addRowPivot={addRowPivot}
          addColumnPivot={addColumnPivot}
          addValue={addValue}
          removePivotField={removePivotField}
          changeFieldOrder={changeFieldOrder}
          setAggregationFunction={setAggregationFunction}
          toggleSubtotals={toggleSubtotals}
          toggleRowGrandTotals={toggleRowGrandTotals}
          toggleColumnGrandTotals={toggleColumnGrandTotals}
          sortPivotField={sortPivotField}
          removeSortPivotField={removeSortPivotField}
          addFilter={addFilter}
          removeFilter={removeFilter}
          setShowAs={setShowAs}
          setFieldGrouping={setFieldGrouping}
          refreshPivot={refreshPivot}
          onRequestClose={onClosePivotSettings}
        />
      )}

      <NewPivotTableDialog>
        <NewPivotTableEditor onSubmit={onCreatePivotTable} />
      </NewPivotTableDialog>
    </>
  );
};

Authoring a pivot

Programmatic creation

await onCreatePivotTable({
  pivotId: uuid(),
  source: {
    sheetId: 1,
    startRowIndex: 1,
    endRowIndex: 100,
    startColumnIndex: 1,
    endColumnIndex: 6,
  },
  targetPosition: { rowIndex: 1, columnIndex: 8, sheetId: 1 },
  rows: [{ field: "Region" }, { field: "Salesperson" }],
  columns: [{ field: "Quarter" }],
  values: [
    { field: "Revenue", aggFunc: "sum" },
    { field: "Units", aggFunc: "sum" },
  ],
});

Right-click → "Create pivot table"

The NewPivotTableDialog is the in-app entry. Wire onRequestCreatePivotTable into the right-click context menu and the user picks the source range + target location.

Show Values As

Switch a value field's display mode without changing the underlying aggregate. The transform runs in JS after DuckDB returns the raw sum(...) so any aggregator works.

// Six modes ship:
await setShowAs(pivotId, "Revenue", "as_is");          // raw aggregate (default)
await setShowAs(pivotId, "Revenue", "pct_of_total");   // PERCENT format auto-applied
await setShowAs(pivotId, "Revenue", "pct_of_row");
await setShowAs(pivotId, "Revenue", "pct_of_column");
await setShowAs(pivotId, "Revenue", "running_total");
await setShowAs(pivotId, "Revenue", "rank_desc");      // 1 = highest
await setShowAs(pivotId, "Revenue", "rank_asc");       // 1 = lowest

PERCENT (0.00%) and NUMBER (0) cell formats are auto-derived from the chosen mode and applied to the value + total cells.

Field grouping

Group a date field by period or a numeric field by bucket size. Implemented as a DuckDB EXCLUDE-based SOURCE CTE rewrite — all downstream pivot logic sees the grouped value transparently.

// Group OrderDate column by month
await setFieldGrouping(pivotId, "OrderDate", {
  type: "date",
  period: "month",  // year | quarter | month | week | day
});

// Group Amount column into $1000 buckets starting at $0
await setFieldGrouping(pivotId, "Amount", {
  type: "numeric",
  bucketSize: 1000,
  startAt: 0,
});

// Clear the grouping (revert to raw column)
await setFieldGrouping(pivotId, "OrderDate", null);

Sort

Tri-state ↑/↓/none. Sort on any row/column field by its labels, or on any value field by its aggregate.

// Sort regions A→Z
await sortPivotField(pivotId, "Region", "asc");
// Sort by total revenue (largest first)
await sortPivotField(pivotId, "Revenue", "desc");
// Clear the sort on a field
await removeSortPivotField(pivotId, "Region");

Value-field sort works in both layouts: grouping-only (single value column) AND column-pivoted (multiple colVal_agg(field) columns) — the matcher accepts both alias shapes.

Label filter + Top N

Per-field set filter:

await addFilter(pivotId, "Region", {
  filterType: "set",
  values: ["North", "East"],
});

// Clear it
await removeFilter(pivotId, "Region");

Top N filter (the editor's "Top N filter" toggle does this for you):

await addFilter(pivotId, "Region", {
  filterType: "topN",
  direction: "top",      // "top" | "bottom"
  count: 10,
  byField: "Revenue",    // any value field
});

The filter is emitted as a subquery against the SOURCE CTE:

"Region" IN (SELECT "Region" FROM SOURCE GROUP BY "Region" ORDER BY sum("Revenue") DESC LIMIT 10)

Totals toggles

await toggleSubtotals(pivotId, false);          // Hide subtotals
await toggleRowGrandTotals(pivotId, false);     // Hide bottom Grand Total row
await toggleColumnGrandTotals(pivotId, false);  // Hide right Grand Total column

Default is true for all three (Excel default).

Refresh + auto-refresh

await refreshPivot(pivotId);  // Manual — re-run against current source data

Auto-refresh fires when sourceDataVersion bumps — typically when a cell in the pivot's source range changes. The Refresh icon in PivotEditor is a convenience for the manual case.

Drilldown

Double-click any value cell to see the underlying source rows. Wire onDrillDownAtCell to your own modal / sheet / panel:

const handleDrilldown = async (cell: { rowIndex: number; columnIndex: number }) => {
  const result = await onDrillDownAtCell(pivotId, cell);
  // result.rows is an array of source rows that contributed to that cell
  openDrilldownModal(result);
};

Slicer integration

Wire the pivot slicer's onFilterPivot callback directly to applySlicerSelectionToPivots:

<CanvasGrid
  onFilterPivot={applySlicerSelectionToPivots}
  /* ... */
/>

Slicer selection then routes through the pivot's filter pipeline — checking/unchecking values applies a filterType: "set" filter on the pivot's fieldName. Empty selection clears the filter (shows all values).

GETPIVOTDATA()

Look up a value cell in a rendered pivot from any formula:

=GETPIVOTDATA("Revenue", $H$1, "Region", "North", "Quarter", "Q3")

The function scans the pivot's row + column header bands for the supplied (field=item) tuples and returns the matching cell. See Formula evaluation for syntax details.

XLSX round-trip

Pivot tables export to a full xl/pivotCache/pivotCacheDefinitionN.xml + xl/pivotTables/pivotTableN.xml pair with refreshOnLoad="1" so Excel rebuilds the cache from the live source on open. Positions, row/column/value fields, aggregation function, sort state, and filter state all round-trip.

Calculated fields / calculated items and the pivot-backed slicer OLAP cube cache export are still deferred.

PivotTable type

type PivotTable = {
  pivotId: string | number;
  source?: SheetRange;
  targetPosition: SheetCoordinate;
  pivotRange?: SheetRange;  // current rendered range; the engine sets this
  rows: PivotGroup[];
  columns: PivotGroup[];
  values: PivotValue[];
  filters?: Record<string, any>;
  sortModel?: Array<{ field: string; sort: "asc" | "desc" }>;
  // Totals toggles — default true if omitted
  showSubtotals?: boolean;
  showRowGrandTotals?: boolean;
  showColumnGrandTotals?: boolean;
};

type PivotGroup = {
  field: string;
  displayName?: string;
  sortOrder?: SortOrder;
  grouping?:
    | { type: "date"; period: "year" | "quarter" | "month" | "week" | "day" }
    | { type: "numeric"; bucketSize: number; startAt?: number };
};

type PivotValue = {
  field: string;
  displayName?: string;
  aggFunc: "sum" | "count" | "avg" | "min" | "max" | "var" | "stddev" | "median" | "product";
  showAs?:
    | "as_is"
    | "pct_of_total"
    | "pct_of_row"
    | "pct_of_column"
    | "running_total"
    | "rank_desc"
    | "rank_asc";
};

Performance notes

• DuckDB-WASM runs the PIVOT operator entirely in the browser. Datasets up to a few million rows handle comfortably.

• Filter evaluation happens at the SQL layer (IN (SELECT …) subqueries) — no JS-side row scans for set or Top N filters.

• Show Values As + value-field sort run as post-aggregation JS transforms — they operate on the already-pivoted result set, not the raw source.

• refreshOnLoad="1" in the XLSX export means Excel rebuilds the cache from the live source on open — saved files don't carry stale aggregate values.