Remote API Reference

The Remote Pivot API is a transport-agnostic JSON contract that allows RevoGrid to communicate with analytical backends, OLAP cubes, or custom SQL planners.

Use aggregationSemantics: 'server' to pin plugin/controller execution to the trusted backend. A pin prevents accidental engine switching; it does not guarantee identical local/server results. Choose null, count, distinct, totals and precision policies in the backend contract and validate them against independent facts. For local analysis, use the V2 numeric/scalar aggregation configuration.

Load Request

Interactive example

Remote API reference

Understand query/groupSummary/summary fields, counts and required response arrays

The example loads when visible.

Reset

The PivotLoadRequest is sent whenever the grid needs to materialize a visible window of analytical data.

Root Properties

• viewId: Semantic identifier for the dataset (e.g., sales_cube).
• fieldsVersion: Version of the field registry used to resolve selectors.
• loadOptions: The analytical query definition.
• viewport: Request windows for row and column axes.
• uiState: Optional hints for engine-side formatting.

Load Options

The loadOptions object defines the shape of the analytical query:

• rows: Array of group descriptors for the row axis.
• columns: Array of group descriptors for the column axis.
• totalSummary: Measures to aggregate.
• filter: Source predicate expression tree, evaluated before aggregation.
• having: Aggregate predicate tree, evaluated after aggregation.
• sort: Sorting directives (can include sort-by-summary).
• groupSummary: Measures to calculate at every group level.

Remote summary identities

The standard remote contract accepts sum, min, max, avg, count, and distinct. A server-side field registry must explicitly authorize each requested operation through allowedSummaries; a valid operation name is not field access authority. SqlPivotQueryPlanner validates total, group, and HAVING summaries against that registry.

distinct means the number of distinct non-null scalar values in the complete requested source scope. Empty text is a value. SQL backends should use COUNT(DISTINCT registered_column) with their documented collation policy. Grand totals must be recomputed over facts: a customer appearing in two groups counts once in the grand total, not twice. Register the operation only after the backend implements these semantics; older backends may reject it explicitly.

This does not enable arbitrary client aggregate aliases, versioned local aggregate operations, independent summaries, or derived field compilation on remote engines. See backend validation for the optional distinct-count conformance fixture.

Granular query capabilities

An adapter, remote store, or explicit engine.capabilities may supply a versioned query profile. The host obtains this runtime metadata from its trusted backend; portable V1/V2 reports do not carry this runtime authority. The version identifies the capability shape; null, precision and collation policies remain separate.

import type { PivotEngineCapabilities } from '@revolist/pivot';

const capabilities = {
  aggregation: true,
  query: {
    version: 1,
    summaryTypes: ['sum', 'count', 'distinct'],
    summaryTypesByField: {
      revenue: ['sum', 'count'],
      customer: ['count', 'distinct'],
      privateMeasure: [],
    },
    maxRowDepth: 3,
    maxColumnDepth: 2,
    allowedRowFields: ['region', 'country', 'city'],
    allowedColumnFields: ['year', 'quarter'],
  },
} satisfies PivotEngineCapabilities;

Global and per-field lists intersect; an empty list allows no operation, and an omitted field inherits the global list. Depth counts physical grouping fields, excluding the measures-on-rows presentation field. Zero disallows grouping on that axis. Omitted restrictions preserve the existing transport behavior; this does not enable custom client aggregate aliases.

allowedRowFields and allowedColumnFields constrain public physical selectors on their respective axes. An empty list permits no physical field; an omitted list adds no restriction. These lists intersect across engine and adapter profiles. They describe membership, not an ordered hierarchy or a required minimum depth. A backend with a fixed semantic view must still validate exact axis order and required fields.

Field keys are the backend’s public physical selectors. V2 physical-field reports project those restrictions to their UI selectors for authoring without changing the transport identifiers. Explicit engine.capabilities.query intersects the adapter’s inherited profile. Operation lists intersect and depth limits use the smaller maximum; omitted limits and explicit undefined retain inherited restrictions. Update configuration immutably when backend metadata changes.

The configurator and context-menu aggregation choices use supported operations. Layout actions reject unsupported axis fields, swaps and additional hierarchy levels while retaining removal and presentation edits as recovery actions. Query preparation and HTTP load/export reject unsupported summaries, including hidden group/HAVING summaries, or unsupported axis membership/depth before execution. This also covers application adapters, complete export and reused analytical results. Malformed profiles fail closed. Errors retain INVALID_SUMMARY_TYPE or HIERARCHY_UNAVAILABLE with details.

This profile is a preflight and authoring contract, not authorization. The server must still validate every request against its tenant-scoped registry and actual implementation. A depth limit does not advertise subtotals, expansion, date grouping or calculated fields. The SQLite reference publishes one physical field per configured axis through these allowlists, together with its actual registered SQL summary operations. Existing unsupported fields can be removed or reordered for recovery; a new unsupported membership cannot be introduced.

Local adapter requests

ClientPivotEngineAdapter.load(request, signal) uses the same aggregation model as the grid. An empty loadOptions keeps the constructor report. Supplied axes and totalSummary replace the corresponding fields; explicit empty arrays clear them. Stable measure IDs preserve matching labels and presentation metadata. Constructor member filters remain a source scope, combined with request filter before aggregation. Request HAVING boolean trees run independently at each returned aggregation grain; totals are not sums of the remaining visible rows.

Sorting runs before paging, preserving hierarchy branches and subtotal placement. Column windows contain analytical buckets with all requested measures, and the same window projects body, summary and group-aggregate carriers. Zero-size and exhausted windows return no value columns with full axis counts. Expansion paths are typed, validated and copied; row records remain logical model rows for the grid grouping renderer, while column expansion controls the materialized frontier. Already-aborted signals reject; synchronous local aggregation cannot be interrupted mid-computation by an event-loop callback.

Date/numeric group intervals and different group-level summary definitions are currently rejected by the local adapter rather than ignored. A HAVING target must resolve to a requested measure and materialized semantic bucket; missing values for an otherwise valid bucket evaluate as null. Custom constructor aggregators remain supported for loadOptions: {}. Request descriptors for local distinct must match an explicitly configured measure by stable ID, property, and aggregator. Choose the strict aggregate preset for non-null typed distinct values; default local coercion is retained for compatibility and is not the SQL distinct policy above.

Viewport

The viewport defines independent analytical windows:

• rowAxis: Contains offset, limit, and expandedPaths.
• columnAxis: Contains offset, limit, expandedPaths, and collapsedPaths.

Use typed analytical paths for expansion state, not visible column indexes. expandedPaths and collapsedPaths describe exceptions to uiState.columnGroupsCollapsedByDefault. This lets the backend calculate and return only the visible column frontier in either default mode.

Normalization Limits

Before planning/execution, requests are typically normalized against backend limits (PivotLimits) to keep payloads and query complexity bounded.

Common limits include:

• maxRowWindowSize: Maximum allowed rowAxis.limit.
• maxColumnWindowSize: Maximum allowed columnAxis.limit.
• maxExpandedPaths: Maximum combined number of expanded and collapsed frontier paths per axis.
• maxExpansionDepth: Maximum hierarchy depth of a single expansion path.
• maxDrilldownLimit: Maximum page size for drilldown requests.

For maxExpansionDepth, depth is the number of path segments. Example: ['Region', 'Country', 'City'] has depth 3.

Load Response

PivotLoadResponse returns only the data needed for the current visible window.

Root Properties

• data: Cell records with __rowPath and __columnPath, or already materialized row records using the configured generated value properties.
• summary: Required array of grand-total or pinned rows; use [] when there are none.
• rowAxis: Window metadata for the row axis.
• columnAxis: Window metadata for the column axis.
• groupAggregates: Optional grouped-row measures.
• meta: Diagnostic metadata.

Important response metadata:

• cacheStatus: ‘hit’, ‘miss’, ‘warm’, or ‘bypass’.

Axis Metadata

Both rowAxis and columnAxis use the same metadata structure.

Field	Role	Description
paths	Optional in the type	Array of analytical paths used to generate headers; supply it for an authoritative remote column frontier.
kinds	Optional	Bucket kinds aligned with paths: leaf, collapsed, subtotal, or grandTotal. Disambiguates literal member strings from synthetic total markers.
totalCount	Required (Rows)	Total members on server. Required for row-axis scrollbars and pagination.
expandedPaths	Optional	Expanded analytical paths echoed by the engine.
collapsedPaths	Optional	Collapsed analytical paths echoed by the engine.

Drilldown

Drilldown uses PivotDrilldownRequest and PivotDrilldownResponse.

The request identifies a visible summary cell by:

• rowPath
• columnPath
• optional dataIndex

The request carries its optional source/member filter explicitly. Intersect that WHERE scope with cell.rowPath and cell.columnPath, then return matching fact rows with offset, limit and totalCount. HAVING and analytical axis windows do not restrict the matching fact page. Validate customColumns against your published registry before exposing source fields.

Member choices and HTTP routes

PivotFilterValuesRequest identifies viewId, fieldsVersion, the field selector, source filter, optional search, and zero-based offset/limit. It returns values and totalCount. The source filter excludes that field’s own native member selection so users can change their choice.

The PivotEngineAdapter.filterValues and PivotRemoteStore.filterValues methods are optional. HttpPivotRemoteStore.filterValues posts the complete request to /api/pivot/filter-values. Implement this route in your application backend before advertising filterValues: true. Apply source filtering and search before distinct-member paging, and return the matching totalCount; do not enumerate only the currently loaded aggregate window.

The built-in HTTP store uses these fixed paths under its baseUrl:

Operation	Method and path
Load analytical window	POST /api/pivot/load
Drill through to facts	POST /api/pivot/drilldown
Search/page distinct members	POST /api/pivot/filter-values
Save state/report envelope	POST /api/pivot/state/save
Load state/report envelope	GET /api/pivot/state/{userId}/{viewId}

User and view path segments are URL-encoded. baseUrl prefixes the paths; it does not remap individual endpoints. Supply a custom store/adapter for another route layout. State writes for the same user/view are serialized in call order, including after a failed earlier write. JSON success responses and 204 empty responses are supported; non-success responses become PivotError with the HTTP status and response payload in details.

Pass credentials: 'include' to HttpPivotRemoteStore when your application uses cross-origin cookies; omission keeps the browser’s fetch default. The application owns CORS and CSRF policy. authProvider supplies fresh request headers for bearer authentication. Credentials and auth headers stay in runtime transport options and are not serialized into saved reports. Member-choice requests use the same auth, cancellation, telemetry and error handling as loads.

UI State Hints

uiState hints allow the backend to optimize its output format:

• rowHeaderLayout: ‘tree’ or ‘flat’.
• showTotalsPrior: Placement of subtotals.
• collapsedByDefault: Initial hierarchy state.
• columnGroupsCollapsedByDefault: Default state for generated column groups.

Calculation and descriptor policies

Ordinary HttpPivotRemoteStore.load, server-adapter load and SQL planning accept only the standard descriptor properties id, selector, summaryType, calculation. Unknown own enumerable properties, including an operation policy or host label/tracing metadata, reject with INVALID_SUMMARY_TYPE. Keep host metadata outside the descriptor. Local V2 operation aliases/result filters do not extend the remote vocabulary.

Post-aggregate calculations require the corresponding advertised calculations capability and a backend implementation over complete analytical scope. A Boolean or list capability does not execute the local formula/running/percentage code on a remote page. The flat SQLite baseline advertises no calculation support and rejects these requests. Its trusted analytical opt-in implements its declared complete-context calculation profile; see current reference profiles. The baseline supports standard flat summaries, including authorized distinct, with null-skipping SQL AVG/COUNT(column), null empty SUM/AVG/MIN/MAX and zero empty COUNT/DISTINCT. Empty text is a counted SQL value; collation belongs to the backend.

Versioned local operations, independent group/total overrides and nonphysical V2 fields are rejected by standard server preparation. The execution pin does not translate these operations. Full-export forwarding checks envelope, query capabilities, limits and the same summary-property allowlist as ordinary load before invoking a store or sending HTTP. Independently validate policies in the application backend before computing/exporting.