Intent reports: user parameters, XLSX export, and bucket columns (ageing)

[delchev]

Motivation

Intent reports: support fixed filter: expressions only, and the report page renders on screen only. Operational reporting in any business application needs (a) user-set parameters - a from/to date range, a counterparty, an amount threshold - and (b) export (XLSX at minimum; PDF/print next). Without them, every list a bookkeeper needs to hand to an auditor is a screenshot.

Proposal (discussion-first)

1. reports.parameters: - declared user parameters ({ name, type: date|number|string|relation, target: <dimension/field>, op: ge|le|eq|like }) rendered as inputs above the report; the generated query binds them.
2. Export: an XLSX (and CSV) download of the current result set - a generic endpoint on the generated report service + a button on the report page.
3. Computed bucket columns: a declarative bucket: column kind - bucket(CURRENT_DATE - due, [30, 60, 90]) - so ageing reports (receivables 0-30/31-60/61-90/90+) are report definitions instead of hand SQL.

Why upstream

(1) and (2) benefit every generated report immediately; (3) makes the standard financial ageing family expressible. All three are template/generator work with no model migration.

[delchev]

Scoping against what has since shipped — two of the three asks are effectively covered, so this issue reduces to (3).

(1) User-set parameters — substantially covered

• Declared parameters exist end-to-end: kind: balance emits .report parameters ({name, type, initial}), the generated repository binds them via baseParameters, the controller passes them through GET query / POST body, and the Harmonia report page renders each one as a first-class input above the filters (date → picker).
• Plus generic per-column filters on every report: the repository accepts conditions: [{column, operator, value}] validated against the report's own column aliases and types (EQ/NE/GT/GTE/LT/LTE/LIKE), and the report page renders a typed filter panel — date range, number range, boolean select, text contains — switching to POST /search / /count when active.

Between them, the motivating cases in this issue (from/to date range, counterparty, amount threshold) are already user-settable. The only residual gap is an authoring DSL for named parameters on a non-balance report with target:/op: binding. Worth its own issue if wanted; not a blocker for anything today.

(2) Export — covered for CSV and PDF; XLSX declined

POST /export returns the full filtered result set, and the Harmonia report page has both exportCsv() (CSV blob download, honouring the active filters and parameters — it downloads what the table shows) and printReport() (same full filtered set → print/PDF).

XLSX is explicitly out of scope: it would mean pulling in a heavy spreadsheet dependency, and CSV + PDF meet the "hand it to an auditor" requirement. Not planned.

(3) Computed bucket columns (ageing) — the real remaining work

Still open, and the only part of this issue that needs code. Note what exists is not this: month(field) / year(field) are date-grouping buckets for aggregation, whereas this asks for a value-bucketing column kind — bucket(CURRENT_DATE - due, [30, 60, 90]) — so the standard receivables ageing family (0-30 / 31-60 / 61-90 / 90+) becomes a report definition instead of hand SQL.

Suggestion: retitle this to the ageing/bucket column kind alone, and open a separate issue for the named-parameter DSL if it is still wanted.

[delchev]

Progress: (1) declared report parameters shipped with kind: balance (the .report parameters block + Harmonia first-class inputs, bound via the repository's baseParameters) and (3) ageing buckets shipped as the ageing(, [30, 60, 90]) dimension (date-boundary CASE, portable H2/PostgreSQL, null → n/a). Remaining open here: (2) XLSX export — the generated /export endpoint is CSV-shaped today — and generalizing authored parameters: to non-balance reports.

[delchev]

The last modeled part of this issue is now implemented: #6911 generalizes authored parameters: from kind: balance to any report.

reports:
  - name: Revenue
    source: SalesInvoice
    dimensions: [date, Customer.name]
    measures: ["sum(total)"]
    parameters:
      - { name: fromDate, target: date, op: ge }                 # From picker
      - { name: toDate, target: date, op: le }                   # To picker
      - { name: minTotal, target: total, op: ge, initial: "0" }  # amount threshold
      - { name: customer, target: Customer.name, op: like }      # name search

target: is a field of the source or a one-hop relation.field path, joining exactly like a dimension - so a parameter can filter by a column the report does not display, which is the one thing the generic per-column filter panel cannot do. Purely the authoring half: the generated repository (baseParameters), the controller (a @QueryParam per declared parameter) and the Harmonia parameter strip were already generic over the .report parameters block.

Two decisions worth recording here, since both are ways a report can be quietly wrong:

• A parameter is bound on every call, and each term is a plain binary comparison against a named marker (so it stays a structured conditions row and cannot park the report in the editor's free-style mode). The unset case therefore rides initial, which is required unless the comparison has a neutral "any value" default - a date ge/le bound, or a like search.
• An untouched parameter must not narrow the report - including rows holding no value in the target column, which a plain comparison would drop the moment a parameter was declared. A nullable target is read through its empty value; a required one keeps the plain comparison. A timestamp target compares as a date, so a le bound includes the whole chosen day.

Status of the three original asks

1. User parameters - done: declared parameters on any report (feat(intent): user-set report parameters on any report (#6357) #6911) on top of what already shipped with kind: balance and the generic per-column filter panel.
2. XLSX export - not planned, per the scoping comment above: POST /export + the page's CSV download and print/PDF already meet the "hand it to an auditor" requirement, and a spreadsheet writer is a heavy platform dependency for the delta.
3. Bucket columns (ageing) - done, as the ageing(<date field>, [30, 60, 90]) dimension.

So this issue is fully addressed once #6911 merges, with (2) declined rather than deferred. Follow-ups that came out of the work, both deliberately out of scope of #6911: a relation-valued parameter (a picker over the target entity's rows, rather than a raw FK input) and a translated parameter label (today it is humanized from the name, as the balance window already was).

Docs: dirigible-io/dirigible-io.github.io#214 (merged), plus the format-level proposal IntentFile/intent-specification#54 and page IntentFile/intentfile.github.io#41.

[delchev]

Closing: all modeled work here is on master.

1. User parameters — done. #6911 (merged, 46b5bfc8) generalizes authored parameters: from kind: balance to any report, on top of the balance-window parameters and the generic per-column filter panel that shipped earlier.
2. XLSX export — declined, not deferred. POST /export plus the report page's CSV download and print/PDF already meet the "hand it to an auditor" requirement, and a spreadsheet writer is a heavy platform dependency for the delta.
3. Bucket columns (ageing) — done, as the ageing(<date field>, [30, 60, 90]) dimension (date-boundary CASE, portable H2/PostgreSQL, null → n/a).

Docs: dirigible-io/dirigible-io.github.io#214 (merged); format-level proposal IntentFile/intent-specification#54.

Two follow-ups from the work remain unfiled, both deliberately out of scope of #6911 — a relation-valued parameter (a picker over the target entity's rows rather than a raw FK input) and a translated parameter label (today humanized from the name). Neither blocks anything; they can be opened on demand.