Hierarchy and calculation semantics

Hierarchies, expansion and totals

A row axis ['region', 'city'] groups region first and city second. A column axis ['year', 'quarter'] forms ordered header levels. Enable groupAggregations to show group header aggregates and totals for explicit subtotal/grand rows and columns.

1
import type { PivotConfig } from '@revolist/pivot';
2
const config: PivotConfig = {
3
  rows: ['region', 'city'], columns: ['year', 'quarter'],
4
  values: [{ id: 'sales', prop: 'sales', aggregator: 'sum' }],
5
  collapsed: false,
6
  groupAggregations: true,
7
  columnCollapse: { collapsed: false },
8
  totals: { grandTotal: true, subtotals: true },
9
};

With North/A [10] and North/B [20,20,20], the North SUM is 70 and strict AVG is 17.5, whether North is expanded or collapsed. Its AVG is not the average of 10 and 20. For DISTINCT, a customer shared by two cities contributes once to the North distinct total.

Expanded Pivot column hierarchy showing nested headers and subtotal columns beside detailed values — Existing native-grid example with expanded column totals. Expansion changes the visible hierarchy while totals retain their original-fact grain.

Collapsed Pivot column groups retaining aggregate values and the row hierarchy in the financial example — The same native-grid example with collapsed column groups. Aggregate placeholders retain the branch’s fact scope instead of summing visible child summaries.

Run the downloadable hierarchy example to compare expanded and collapsed totals and round-trip the V2 report. The screenshot uses the existing financial demo dataset; the script uses six deliberately small facts with independently asserted results.

Use typed path keys through createPivotExpandedPathKey and report presentation.expandedKeyEncoding: 'semantic-v1' when persisting semantic expansion; avoid hand-built delimiter keys. For a multilevel row hierarchy, enable drill-down with Boolean collapsed, persisted expanded, or compact row layout. collapsed: true starts collapsed and collapsed: false starts expanded; explicit expanded entries override individual branches. Column collapse has its own policy. See drill-down and totals for per-level configuration and suppression. Single-child subtotal suppression follows source occurrences; duplicate facts at one leaf are not a single source occurrence.

The SQLite hierarchy opt-in accepts fixed authorized prefix axes up to eight fields per axis, with separate semantic kinds for group/subtotal/grand coordinates. Literal strings resembling total markers remain real members. The legacy flat reference is a separate profile. Depth capability metadata alone does not implement expansion, subtotals or authorization.

Explicit numeric and scalar policies

Profile	`[10, null]` AVG	DISTINCT meaning	Where selected
Legacy local numeric IDs	5	Finite `Number(value)` identities; strings such as customer names are not a text-distinct contract	Existing native configs and V1/default builtins
Strict local number	10	Eligible finite numbers; null/missing skipped	V2 field aggregators with `createPivotStrictAggregators({ input: 'number' })`
Strict local scalar	Not a numeric AVG catalog	Typed strings/numbers/Booleans, case-sensitive; `1`, `'1'`, `true` differ	V2 scalar preset
SQL	Backend AVG skips SQL NULL	Backend SQL type/collation rules	Authorized adapter/field operation profile

invalid defaults to 'error'; choose 'skip' deliberately. Numeric-string input accepts trimmed decimal/scientific syntax; plain number input rejects numeric text. Scalar input retains empty text and Booleans. Both presets skip null/missing. Default local count counts occurrences; strict count counts eligible values. For SQL row counts choose an authorized nonnull fact key. For local all-fact counts use a literal-one derived field.

1
import { createPivotStrictAggregators } from '@revolist/pivot/headless';
2
const amountField = {
3
  id: 'amount', source: { kind: 'physical' as const, prop: 'amount' },
4
  aggregators: createPivotStrictAggregators({ input: 'number', invalid: 'error' }),
5
};

These are portable V2 operation declarations, not native callback maps. Use reportVersion: 2 in the controller or explicit V2 compilation; use aggregationSemantics: 'local' to reject accidental server binding. A nonempty catalog replaces the field’s available operations. The complete strict example and downloadable executable show all required fields and visible totals.

Missing intersections can stay absent rather than becoming zero. Existing all-null buckets differ from absent buckets. Strict sum/count/distinct return zero for empty eligible input; strict AVG/min/max return null. Do not equate these with legacy callback empty results or SQL sparse-cell rules.

Calculations and formula references

Calculations transform an aggregate at its coordinate. Percentage denominators come from the complete applicable fact context; hiding totals, collapsing groups, windowing or applying HAVING does not redefine those denominators. Source WHERE/member filters do change the fact scope. Running/rank calculations use ordered analytical peers; presentation sorting does not define a business calendar. See aggregation policies.

1
const percent = { type: 'percentOfGrandTotal' as const };
2
const percentagePoints = {
3
  type: 'formula' as const,
4
  referenceValues: 'calculated' as const,
5
  expression: {
6
    type: 'binary' as const, operator: '*' as const,
7
    left: { type: 'measure' as const, id: 'share' },
8
    right: { type: 'number' as const, value: 100 },
9
  },
10
};

Give one measure stable ID share with calculation percent, and another measure calculation percentagePoints. For A=20/B=80, share is 0.2/0.8 and the formula is 20/80. Omit referenceValues (default 'raw') and the reference to that nonformula measure reads its underlying sum, giving 2000/8000. References to another formula evaluate that formula using its own reference policy. Definition order does not replace dependency resolution. Unknown IDs, cycles and unknown reference modes reject rather than producing a guessed result.

Zero denominators, missing comparisons and nonfinite derived results yield null. Arithmetic is IEEE-754 binary64; display formats and XLSX do not add decimal accuracy. Exact accounting arithmetic is deferred. The SQLite analytical opt-in supports its declared percentage/parent/index/difference/running/rank/formula DAG profile; the flat baseline rejects calculations. Local V2 operation descriptors, derived fields and independent group/total overrides are not standard server aggregate declarations.

If an execution fails, keep the committed report visible, inspect the error, repair the field/operation or formula dependency, and retry. Never silently convert a customer’s existing legacy policy to strict semantics during migration.