> ## Documentation Index > Fetch the complete documentation index at: https://code.dcycle.io/llms.txt > Use this file to discover all available pages before exploring further. # Query datasets > Aggregate dataset records, evaluate formulas and recalculate subtotals. Queries use the authenticated organization's accepted family scope. Use a catalog dataset key or `elastic:` 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: ```json theme={"theme":{"light":"github-light","dark":"github-dark"}} { "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: ```json theme={"theme":{"light":"github-light","dark":"github-dark"}} { "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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.