Query datasets

Aggregate dataset records, evaluate formulas and recalculate subtotals.

POST /v1/datasets/{key}/query

Query datasets

JavaScript

const options = {method: 'POST'};

fetch('https://api.dcycle.io/v1/datasets/{key}/query', options)
  .then(res => res.json())
  .then(res => console.log(res))
  .catch(err => console.error(err));

Queries use the authenticated organization’s accepted family scope. Use a catalog dataset key or elastic:<dataset UUID> for a custom dataset. The dataset schema describes available dimensions, metrics and row-level formula operands.

Formula evaluation

A formula with mode: "row" evaluates each record before applying its agg (sum by default; avg for a mean). A formula with mode: "aggregate" evaluates the grouped metric values. The distinction matters: SUM(value * weight) / SUM(weight) is a weighted mean; the mean of group means is generally a different result. Division by zero returns null.

This example assumes numeric fields value and weight, and dimension region in the dataset schema:

{
  "dimensions": ["region"],
  "metrics": [],
  "formula_metrics": [
    {
      "key": "weighted_sum",
      "label": "Weighted sum",
      "mode": "row",
      "agg": "sum",
      "expr": {"op": "*", "args": [{"field": "value"}, {"field": "weight"}]}
    },
    {
      "key": "weights",
      "label": "Weights",
      "mode": "row",
      "agg": "sum",
      "expr": {"field": "weight"}
    },
    {
      "key": "weighted_mean",
      "label": "Weighted mean",
      "mode": "aggregate",
      "expr": {"op": "/", "args": [{"field": "weighted_sum"}, {"field": "weights"}]}
    }
  ],
  "subtotal_dimensions": [[]],
  "limit": 100
}

The numerator and denominator must refer to the same valid observations. If value can be missing, exclude that record’s weight as well, using an appropriate row formula or input filtering. avg ignores null observations; null values are not automatically zero.

Recalculated subtotals

subtotal_dimensions is optional and defaults to []. Each entry is a distinct subset of the query’s selected dimensions. An empty subset requests the grand total. For dimensions: ["region", "product"], [[], ["region"]] requests both a grand total and regional totals. Requests support at most 16 subsets; raw sample queries reject this option.

The response includes subtotals, alongside the existing columns, rows and truncated fields:

{
  "subtotals": [
    {
      "dimensions": [],
      "rows": [{"weighted_sum": "1400", "weights": "100", "weighted_mean": "14"}],
      "truncated": false
    }
  ]
}

Each subtotal is recalculated from the source observations with the query’s filters, organization scope and dimension row restrictions. It is not a sum of the displayed leaf cells. The leaf result and subtotals run in one SQL statement and share a database snapshot and statement timeout.

limit applies independently to each grouping, with its own truncated flag. A complete grand total remains valid when the leaf table is truncated. A truncated subtotal grouping can omit groups; clients must not treat an absent group as zero. Request only the groupings needed by the visualization, as each adds aggregation work.

Temporal and display semantics

For datasets with prorated intervals, a subtotal that removes the period dimension retains the selected time buckets. Amounts are allocated to the selected overlap; a mean counts each source observation once. Aggregate formulas are evaluated after period aggregation.

For external intensity metrics, collapsing an existing period dimension retains its grain and period filters. Monthly and annual denominator records are not added together. Missing selected-period coverage produces a null denominator for division. On a prorated dataset grouped by period, each bucket’s allocated numerator is combined with the intensity value of that same bucket, cut on the same (fiscal) calendar as the period axis. A conditional attribute is evaluated on each source observation before it is allocated, so every bucket of an observation carries that observation’s label.

Clients should use returned subtotals for means, ratios and chart totals. Comparison windows must remain separate, since windows may overlap. Percentage shares require a complete additive partition; summing means or calculating shares from a truncated sample can produce misleading percentages. Subtotals do not perform unit conversion: incompatible units still require a separating dimension or a separate conversion policy.

Purchases physical quantity

The purchases dataset exposes non_currency_quantity (the optional physical quantity) separately from quantity (monetary amount for spend-based purchases). Its grouping hint is physical_unit, resolved from non_currency_unit_id; the existing unit dimension still describes quantity. Group or filter by compatible units before computing totals or unit prices. No unit conversion is applied.

Custom purchase columns can reference native.non_currency_quantity and native.physical_unit. A unit-price column divides native.quantity by native.non_currency_quantity. Missing quantities and zero denominators yield null, not zero. Visualization formulas use the schema keys without the native. prefix. To calculate an overall unit price, divide aggregated quantity by aggregated non_currency_quantity, using the same valid observations in both sums.

Custom-column period behavior and totals

For numeric custom columns on a native category with a period window, an omitted options.proration preserves the legacy amount behavior: values are apportioned to the overlap with the requested dates, including queries without a period axis. Set options.proration to rate explicitly for a factor or rate that must remain unchanged. Formula operands use the same declaration. This changes visualization calculations, not the values displayed in the source grid.

Elastic dataset detail responses include supports_proration, derived from the native host’s semantic period window. Editors should offer the temporal-role setting only when this capability is true. Standalone datasets and date-only categories such as purchases do not apportion values over a period window.

Resolved metric columns publish rollup independently of whether their SQL is a raw aggregate. Additive social FTE/contract sums publish sum; ratios and percentages remain non-additive. Prefer server-recalculated subtotals when available. A truncated chart may display the returned points with an incompleteness notice, but a truncated single-value result is not a complete total. A pie chart may display individually calculable slices while its overall total remains unavailable. Empty observations are not automatically zero.

Formula validation and query time limits

Conditional comparison operands must have matching types, including nested if() results. Invalid custom-column definitions are rejected with FORMULA_INVALID; legacy definitions that no longer compile read as null. Numeric type recasts preserve an explicitly stored proration role, including transitions to and from quantities with units. Recasting to a nonnumeric type removes that role.

Deleting, disabling or retyping a column used by a formula returns HTTP 409 with FIELD_REFERENCED_BY_FORMULA. Its params.column and params.formulas identify the visible column and blocking formula names for localized messages.

A cancelled PostgreSQL dataset query returns HTTP 504 with DATASET_QUERY_TIMEOUT, rather than an undifferentiated HTTP 500. Reduce the date range or filter the data before retrying.

Organization hierarchy

Set nest_organization_by_hierarchy: true with organization as the first grouping dimension to request expandable holding rows. Check the schema’s organization_hierarchy_supported capability first. Flat rows and subtotals retain their existing meaning for charts and CSV exports; never pool them with the overlapping parent rollups.

The optional hierarchy response contains nodes (id, name, parent_id, ambiguous, own_data), rows, subtotals, and truncated. Its organization cell values reference node IDs, not company names. Parent values are recalculated over original filtered observations from the parent and its descendants. Financial contributions keep the requesting holding’s participation factors. Parent direct observations have a separate own_data child when present. Empty companies have no observation rows; render them as blank, never zero.

Hierarchy groupings share the flat query’s SQL snapshot and timeout. Means, aggregate formulas and ratios are recomputed at each grouping. Each grouping has its own limit; do not infer totals from a truncated group. Identical company names remain separate; organizations reachable through multiple parents are assigned one deterministic display parent and marked ambiguous.

Organization filters continue to select the organizations owning the observations, before rollup, as in the old pivot. For the emissions dataset, scope-3 exclusions inherited through accepted investment relationships apply to both flat and nested results while hierarchy mode is active. Changing the first dimension or requesting raw samples leaves the hierarchy inactive.

The initial capability covers datasets exposing a direct organization scope, including invoices, purchases, energy and emissions. Social contracts/remuneration backed by pre-grouped CTEs do not advertise it. Their windowed percentage measures require a separate adaptation before hierarchy can be enabled safely. No feature flags are changed by this API option.