Skip to content

Request Parameters & References

A template’s filters can leave values open for the requester. The requester then enters, for example, a date window, a member number or the time of an incident when they create a request. A filter can also take its values from another dataset in the same request, so one lookup drives the rest of the load.

Both live in raw filter conditions.

A parameter is a typed tag in square brackets:

filters.conditions = [
{ raw = "created_on >= DATE [from_date] AND created_on <= DATE [to_date]" },
{ raw = "warehouse_code = TEXT [warehouse_code]" },
{ raw = "logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },
]
Type Requester enters Rendered as
DATE [name] A date (YYYY-MM-DD) A date literal for the datasource’s engine
TEXT [name] Any text, up to 256 characters A quoted, escaped string literal
TIMESTAMP [name] A date, or a date and time A timestamp literal, optionally shifted (see below)

Rules for tags:

  • Names use lowercase letters, digits and underscores, and start with a letter: from_date, lookup_value, incident_at.
  • Every tag needs a type. A bare [from_date] is rejected, and the error suggests DATE [from_date], TEXT [from_date] or TIMESTAMP [from_date].
  • The same name used in several conditions, datasets or datasources is one input. Use [from_date] and [to_date] everywhere for a template-wide window, so the requester enters it once.
  • Parameters only work in dataset filters. They are not filled in inside virtual_table.sql. Filter the dataset instead, and the report reads only the filtered rows.
  • Parameters are not allowed inside IN (...) lists.

When a request is created from the template, the form shows one input per parameter name, labelled from the name (incident_at becomes Incident at). It also shows each filter with its tags highlighted, so the requester sees what the value controls. The request can’t be submitted until every parameter it still uses has a value. No tag ever reaches the datasource unfilled.

{ raw = "created_on >= DATE [from_date] AND created_on <= DATE [to_date]" },

A condition whose parameters are all DATE and that has no REF(...) gets a Don’t filter by date (load all rows) checkbox. Ticking it drops that whole condition from the request. So:

  • Keep a date window in its own condition. If you combine it with other rules (state = 'APPROVED' AND created_on >= DATE [from_date]), opting out of the date would drop state = 'APPROVED' as well.
  • A one-sided window works too: placed_on >= DATE [from_date].
{ raw = "(member_no = TEXT [lookup_value] OR username = TEXT [lookup_value] OR email = TEXT [lookup_value])" },

The value is always quoted as a string, whatever the requester types. One input can match several columns, as above: the requester enters a member number, a username or an email, and whichever column matches selects the row.

{ raw = "logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },

A TIMESTAMP tag can be shifted by whole hours (h) or minutes (m): - 2h, + 30m. Only TIMESTAMP accepts an offset.

What the offset does depends on what the requester enters:

  • A date and time (2026-03-14 09:05): it is shifted by exactly the offset. A value with a time zone is converted to UTC first.
  • A date only (2026-03-14): the sign alone picks an edge of that day. Any - offset becomes 00:00:00 and any + offset becomes 23:59:59, whatever its size. An incident reported with only a date still selects that whole day.

This is how a support template brackets one incident: the requester enters the incident time, and every log file loads only the lines close to it.

REF(Dataset.column) stands for the distinct values that another dataset in the same request loaded for that column. Nothing is entered by the requester.

[[templates.scope]]
datasource = "accounts-db"
[[templates.scope.datasets]]
source = { kind = "table", name = "member" }
columns = ["member_id", "member_no", "username", "email", "full_name"]
filters.logic = "AND"
filters.conditions = [{ raw = "member_no = TEXT [lookup_value] OR username = TEXT [lookup_value] OR email = TEXT [lookup_value]" }]
[[templates.scope]]
datasource = "events-db"
[[templates.scope.datasets]]
source = { kind = "table", name = "login_event" }
columns = ["member_no", "occurred_at", "outcome", "client_ip"]
filters.logic = "AND"
filters.conditions = [{ raw = "member_no IN REF(member.member_no)" }]

Here the requester enters one value. member resolves it to a member, and login_event (in a different datasource) loads only that member’s rows.

Rules:

  • REF(...) is only valid with IN and NOT IN.
  • Dataset is the referenced dataset’s source.name, matched without regard to case. It may be in any scope entry of the template, so in any datasource.
  • If two datasources in the template both have a dataset with that name, the reference is ambiguous. Prefix it with the datasource name: REF(accounts-db:member.member_no).
  • The column must be in the referenced dataset’s columns list.
  • A dataset can’t reference itself, and references can’t form a cycle.

At load time, datasets are loaded in dependency order. Each reference is filled from the referenced dataset’s loaded rows, once that dataset has landed.

  • Capped at 1,000 distinct values. If the referenced dataset holds more, the dependent dataset fails to load rather than pulling an unbounded set. Narrow the referenced dataset first.
  • An empty set is not an error. IN matches nothing, so the dependent dataset loads zero rows. NOT IN matches everything.
  • Integer, text and timestamp values are sent as strings, even when the source column is numeric. The database converts them to the column’s type.

References chain and combine freely with other conditions. In the example below, a log file is narrowed by two earlier datasets and a time window:

filters.conditions = [
{ raw = "(username IN REF(member.username) OR client_ip IN REF(login_event.client_ip)) AND logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },
]

member loads first (from the lookup value), then login_event (from member), then the log file (from both). A condition that contains a REF(...) never gets the date opt-out checkbox.

Use a join when Use REF(...) when
Both tables are in the same datasource The tables are in different datasources, or one side is a file
The narrowing set can be large The narrowing set is small (at most 1,000 values)
You want the database to do the work in one query You want to narrow by exactly what another dataset loaded