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.
Request Parameters
Section titled “Request Parameters”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 suggestsDATE [from_date],TEXT [from_date]orTIMESTAMP [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.
What the requester sees
Section titled “What the requester sees”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.
DATE parameters
Section titled “DATE parameters” { 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 dropstate = 'APPROVED'as well. - A one-sided window works too:
placed_on >= DATE [from_date].
TEXT parameters
Section titled “TEXT parameters” { 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.
TIMESTAMP parameters and offsets
Section titled “TIMESTAMP parameters and offsets” { 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 becomes00:00:00and any+offset becomes23: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.
Filtering By Another Dataset: REF(...)
Section titled “Filtering By Another Dataset: REF(...)”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 withINandNOT IN.Datasetis the referenced dataset’ssource.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
columnslist. - 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.
INmatches nothing, so the dependent dataset loads zero rows.NOT INmatches 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.
Chaining references
Section titled “Chaining references”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.
REF(...) or a join?
Section titled “REF(...) or a join?”| 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 |