Reports & Charts

Every Dazzle app reports data. A bar chart of tickets by status, a pivot of revenue by (region, product), a KPI tile showing total open incidents — all go through the same primitive: Repository.aggregate.

This page is the agent-discoverable entry point. Read this before writing a chart region.

Mental model

DSL → IR → Repository.aggregate → SQL (one query) → AggregateBucket[] → Region template

One DSL declaration compiles to one GROUP BY SQL statement that evaluates the scope predicate once, returns every bucket + label + measure in a single round-trip, and hands the result to a region template for rendering. No N+1 queries, no enumeration phase, no RBAC leaks.

Decide the shape first

display: auto (#1492, UX-maturity 1a) — instead of picking, let the framework infer the form from the source's data shape: aggregate + 2-D grouping → pivot_table, aggregate + 1-D → bar_chart, scalar aggregate → summary, a state-machine entity → kanban, a meaningful (non-created_at) temporal field → timeline, else list. Resolution is dazzle.page.runtime.auto_display.resolve_auto_display. This is now the default (#1492 default-flip): a region with no display: at all is inferred the same way — you only write display: when you want to override the inferred form. An explicit display: list stays authoritative (it is never re-inferred). When you know the form you want, name it explicitly per the table below:

Before you write DSL, pick the cardinality × visualisation:

Visual	display:	Dimensions	Typical use
Inferred	auto	—	let the framework pick the form from the data shape (#1492)
KPI tile	metrics / summary	0	"42 open tickets"
Bar chart	bar_chart	1	Tickets by status
Pivot table	pivot_table	1..N	Alerts by (system, severity) with counts
Kanban	kanban	1	Tickets by status as columns
Line chart	line_chart	1 (time)	Alerts per day
Radar	radar	1 (≥3 values)	Profile shape across categories
Comparison	comparison	1	Ranked league with outlier flags

Each consumes a different IR shape. bar_chart reads group_by: <field>. pivot_table reads group_by: [<field>, <field>]. line_chart reads group_by: bucket(<ts>, <unit>). metrics reads only aggregate:.

Single-dimension (most common)

ticket_breakdown:
  source: Ticket
  display: bar_chart
  group_by: status          # enum on Ticket
  aggregate:
    count: count(Ticket)

The runtime: 1. Reads group_by: status as a single Dimension(name="status"). 2. Emits SELECT status, COUNT(*) FROM "Ticket" WHERE <scope> GROUP BY status ORDER BY status. 3. Returns one bar per distinct status.

FK dimensions auto-resolve. If status were instead ref TicketStatus, the runtime adds a LEFT JOIN "TicketStatus" fk_0 ON ... and picks the first display field it can probe — display_name, then name, title, label, code. The bar label shows the human-readable value; the filter uses the FK id.

Multi-dimension (cross-tab / pivot)

alert_pivot:
  source: Alert
  display: pivot_table
  group_by: [system, severity]   # FK + scalar enum
  aggregate:
    count: count(Alert)

One row per (system, severity) combination. FK columns LEFT JOIN with indexed aliases (fk_0, fk_1, ...) so two FKs to the same table don't collide. Count for each cell computed in the same query. No kept example declares a multi-dim pivot; for working single-dim bar_chart references see examples/invoice_ops (audit_mix, group_by: status) and examples/hr_records (salary_mix, group_by: reason).

Time bucketing (v0.60.0)

Wrap any timestamp field in bucket(<field>, <unit>) to group by calendar unit instead of distinct value. The runtime emits date_trunc('<unit>', <field>) in the SQL. Valid units: day, week, month, quarter, year (whitelist enforced at parse time).

# Single-dim time series → line_chart
alerts_timeseries:
  source: Alert
  display: line_chart
  group_by: bucket(triggered_at, day)
  aggregate:
    count: count(Alert)

# Two-dim (time + categorical) → pivot_table cross-tab
alerts_weekly_by_severity:
  source: Alert
  display: pivot_table
  group_by: [bucket(triggered_at, week), severity]
  aggregate:
    count: count(Alert)

Time buckets are chronologically ordered (ASC), not alphabetical. Labels format per unit: 2026-04-23 (day), 2026-W17 (week), Apr 2026 (month), Q2 2026 (quarter), 2026 (year). The raw ISO timestamp survives as bucket for the bar click / filter downstream.

Time-bucketed dims are mutually exclusive with FK joins — a timestamp column cannot be a foreign key. Combining bucket(...) with a scalar or FK dim in the same group_by_dims list is fine (and common for pivot_table).

No gap-filling in v0.60.0 — days with zero rows don't appear in the result. Line charts handle the gap visually (straight segment between adjacent buckets).

Supported measures

Spec	SQL	Notes
count	COUNT(*)	Always supported, no column arg
sum:<col>	SUM("col")	Numeric columns only
avg:<col>	AVG("col")
min:<col>	MIN("col")
max:<col>	MAX("col")
ratio:<num>:<den>	SUM(num) / NULLIF(SUM(den), 0)	Elapsed-hours kW (#1674); prefer derived kwh / hours in DSL

Correlated subqueries like count(Child where parent = current_bucket) go through a slow path (per-bucket queries) and are subject to N+1 cost. Prefer same-entity measures when you can.

Fast vs slow path

The runtime picks automatically:

Expression	Path	When to use
count(<source>)	Fast	Distribution on the source entity — this is the case #847–#851 fixed
count(<source> where status = 'open')	Fast	Filter before count, still single query
count(<other> where fk = current_bucket)	Slow	Cross-entity count per bucket; keep cardinality low
avg:score	Fast (single-dim only in v0.59.3)	Numeric measure on source

The fast path is scope-safe by construction — one WHERE clause, one GROUP BY, no possibility of the enumeration-vs-count divergence from #847.

Derived metrics (#1359)

A metric line may be arithmetic over metric names declared earlier in the same aggregate: block instead of an aggregate call:

aggregate:
  total: count(Task)
  done: count(Task where status = done)
  completion_rate: round(done / total * 100)

Operators + - * /, parentheses, number literals, and the functions round, abs, nullif, coalesce. References resolve strictly to earlier names in the block (forward references are a parse error naming what is declared); a derived metric may reference another derived metric. Evaluation happens in Python after the scope-filtered aggregate queries return — zero extra queries, so the scope-safety contract above is untouched. Division by zero yields 0 (a ratio over an empty set reads as 0%, not an error).

Derived metrics work on KPI tiles (display: metrics/summary) and per bucket in grouped charts (group_by: + display: bar_chart etc.) — each bucket's derived values compute over that bucket's own metric values. dazzle db explain-aggregate shows derived expressions as the Python post-aggregation step (pass them in --measures, e.g. -m 'total=count,done=count,rate=done/total*100').

Period-over-period arrows are opt-in (#1678). An unset metrics tile is the number and its tone — never ↑ +N vs prior 30 days. Declare delta: when the comparison is real:

aggregate:
  open: count(Finding where status = open)
delta:
  period: 30 days
  sentiment: warning

If the prior window is empty, the arrow is omitted even with delta: set.

Last-reading windows (#1674)

Meter-book boards (BioChart daily_board) need numeric KPIs relative to the last reading, not now(), and a UK production week that starts Monday.

week_energy:
  source: Reading
  display: metrics
  aggregate:
    kwh: sum(Reading.value_delta window week_from_monday on taken_at)
    elapsed_h: sum(Reading.elapsed_hours window week_from_monday on taken_at)
    avg_kw: kwh / elapsed_h
    last_week_kwh: sum(Reading.value_delta window previous_week on taken_at)
    last_day_kwh: sum(Reading.value_delta window last_complete_day on taken_at)

Kind	Bounds (as-of = max(<on>) in the same scope)
last_complete_day	Calendar day of the last reading (the last day in the book, not wall-clock yesterday)
week_from_monday	Monday 00:00 of that week → last reading (partial week, inclusive)
previous_week	Previous Monday 00:00 → this week's Monday 00:00 (complete Mon–Sun)

Windows compile to one WITH _anchor AS (SELECT MAX(<on>) …) CTE plus per-measure CASE WHEN so this-week kWh, elapsed hours, and last-week kWh still share a single scope-safe round-trip. Average kW is sum(value_delta) / sum(elapsed_hours) via a derived metric — not avg(value_delta) and not / 24. date_trunc('week') is ISO Monday (Postgres); this does not follow locale.week_start.

where still works inside the same call (sum(Reading.value_delta where meter_kind = export window week_from_monday on taken_at)). Metrics that share entity + on + where batch; a different where is a second query.

Plant-total vs legal-entity (#1675)

Two Sites can share an ops board and still be separate legal entities (two Xero orgs, never one blended P&L). Tag the grain:

entity Site "Site":
  legal_entity
  name: text

Then:

• Plant total — ungrouped numeric sum(Reading.value_delta) on a command_center is allowed. kW is not money.
• Books — ungrouped sum(Invoice.amount) where Invoice refs a legal_entity Site is a dazzle validate error. Pin it: group_by: site, where site = current_context, or a workspace context_selector on Site.

dazzle validate looks at FieldTypeKind.MONEY, not field names. A decimal kWh field is never fenced. This is a reporting grain, not tenancy — do not model Site as archetype: tenant just to split P&L.

Scope

Scope always applies, pre-aggregation. The runtime threads the user's __scope_predicate into the query's WHERE before GROUP BY runs. A persona who can only see rows from their own department will see counts that reflect exactly those rows — never a leak via a "total" metric or an "Other (N)" bucket.

The region filter: applies too (#1773). Every aggregate over the region's own source: entity — metric tiles, bar_chart / comparison buckets, pivot_table cells — is ANDed with the compiled region filter: on top of scope, so the numbers count the rows the region lists. Scope is never replaced by the filter. An aggregate over a different entity (count(Note) on an Invoice region) can't take the filter (its columns live on the source); dazzle lint warns (W_REGION_FILTER_NOT_ON_AGGREGATE) — give it its own where. Aggregate where clauses compile to typed SQL, so x != null is IS NOT NULL (#1774).

Every scope shape _resolve_predicate_filters emits is aggregate-safe: direct field equality, FK-path subqueries, EXISTS / NOT EXISTS junctions, boolean compositions. Predicates that reference the GROUP BY relation itself (post-join) would need a separate code path — not supported today.

Debugging: dazzle db explain-aggregate

When a chart renders wrong or empty, run:

dazzle db explain-aggregate Alert --group-by system,severity --measures count=count

Prints the exact SQL the framework would emit. Copy-paste into psql / sqlite3, run it manually, compare the result to what the chart shows. Three common mismatch causes:

1. Scope narrower than expected — explain shows no WHERE, your live session has one
2. FK display-field probe picked a different column — code when you expected name, for example
3. Zero source rows in user's scope — the chart is correct; the data isn't there

All three have been root-cause patterns; explain-aggregate lets authors diagnose without reading framework source.

What NOT to do

• Don't compute aggregates in the view layer. Summing rows in the rendered output instead of in SQL loads every row into RAM, ignores scope, and breaks at scale. Use aggregate: with measures.
• Don't group on high-cardinality columns without a limit. group_by: created_at with a million rows returns a million buckets. Use bucket(created_at, day|week|month) or limit: explicitly.
• Don't mix group_by: and group_by_dims:. They're mutually exclusive; group_by_dims wins when both are set but the DSL intent is confusing. Pick one form per region.
• Don't use count(OtherEntity where field = current_bucket) if the source-same path works. The sentinel-based slow path is for cross-entity measures only — same-entity counts should use count(<source>) and get the fast path automatically.
• Don't reach for raw SQL. The aggregate primitive covers the 90% case; if you need something it doesn't, file a DSL extension rather than bypass the scope contract.

Related

• Workspaces — where chart regions live
• Access Control — the scope predicate contract
• Entities — FK fields and display_field
• CHANGELOG entries for v0.59.0 (primitive), v0.59.3 (multi-dim), v0.59.4 (explain), v0.60.0 (time bucketing). See CHANGELOG.md at the repo root.