Skip to main content

Queries

A query object is the SQL behind a report, together with its parameters and its column definitions. Queries are versioned and published independently of the reports that use them, so you can revise a query without disturbing a published report until you are ready.

Writing a query​

Go to Reporting → Queries → New, pick a datasource, and write SQL. If you have run schema discovery on the datasource, the editor offers table and column autocomplete.

SELECT
  o.region,
  o.product_line,
  SUM(o.net_amount) AS revenue,
  COUNT(*)          AS order_count
FROM orders o
WHERE o.order_date >= :date_from
  AND o.order_date <  :date_to
  AND (:region IS NULL OR o.region = :region)
GROUP BY o.region, o.product_line
ORDER BY revenue DESC

Parameters​

Named parameters use :name. Declare each one in the Parameters panel:

FieldPurpose
NameMatches the :name in the SQL
Typestring, number, date, boolean, or list
RequiredWhether a run may proceed without a value
DefaultUsed when a run supplies nothing
HiddenComputed or fixed; not shown to whoever triggers the run
SourceA static list, another query's results, or an expression

Parameters are bound by the driver, never string-interpolated. This is what stops a parameter value from becoming SQL injection.

The (:region IS NULL OR o.region = :region) pattern above is the idiomatic way to make a filter optional: pass a value to narrow the result, omit it to match everything.

Option lists from a query​

A parameter can draw its allowed values from another query object, which keeps pick lists current without hand-maintaining them. Point the parameter's source at a query that returns a value column and a label column.

Testing​

Test query runs the SQL against the live datasource through the agent and returns real rows. Provide parameter values in the test panel. Use this before publishing — it is the only way to see what the report will actually receive.

The agent enforces the datasource's queryTimeout, so a runaway test cannot hold a connection indefinitely.

Discovered columns​

After a successful test, the agent reports the result set's column names and types. These become the query's discovered columns, and they are what the report builder offers when you map data onto a template.

You can override a column's type or display name if the driver's inference is not what you want.

Versions and publishing​

Queries are versioned. Editing a published query creates a draft; the published version keeps serving existing reports until you publish again.

  • Publish promotes the current draft. Reports referencing this query pick it up on their next run.
  • Versions lists every published version.
  • Revert restores an earlier version as the current draft.
  • Bulk publish promotes several queries at once, which matters after a schema migration touches many at the same time.

Calculated columns​

Not everything belongs in SQL. Columns that are pure arithmetic over other columns — margin, variance, running totals, period-over-period change — can be defined as calculated columns on the query object using the expression builder, rather than complicating the SQL.

These are evaluated after the query returns, so they work identically across all five datasource types and they do not add load to the database.

Practical advice​

Aggregate in the database. The agent renders what the query returns. Returning 500,000 rows to sum them in a template is slow everywhere; GROUP BY is fast.

Name your columns. SUM(net_amount) AS revenue gives you a variable called revenue. SUM(net_amount) gives you something driver-dependent and ugly.

Order in SQL. Report templates render rows in the order they arrive.

Keep one query per report shape. Reusing a wide query across unrelated reports makes both harder to change later.