Appearance
Report text: dates, numbers and query parameters
Text in a report band (a label, a text field, a binding on any property) can format numbers and dates, and a report's parameters reach its SQL queries. This page says what each form does and which time zone a date lands in.
Dates
format(value, pattern) and dateFormat(value, pattern) print a date or time. The pattern language is the same one the dateFormat function uses in a screen's expression transform, so a pattern means the same on a screen and in a report.
| Letters | Meaning | Example |
|---|---|---|
yyyy, yy | Year | 2026, 26 |
MM, MMM, MMMM | Month number, short name, full name | 10, Oct, October |
dd | Day of month | 28 |
EEE, EEEE | Weekday, short and full | Wed, Wednesday |
HH, hh | Hour on a 24-hour and a 12-hour clock | 14, 02 |
mm, ss, SSS | Minutes, seconds, milliseconds | 30, 05, 250 |
a | AM or PM | PM |
'text' | Text printed as written | 'at' |
=format(row("sampledAt"), "yyyy-MM-dd HH:mm")
=dateFormat(param("start"), "EEEE, MMMM d, yyyy")
=format(row("sampledAt"), "HH:mm", "America/Chicago")row("field") is the current row's field inside a repeating band, the same value {row.field} gives, in a form that can be passed to format.
The value is either ISO 8601 text (2026-10-28, 2026-10-28T14:30:00, 2026-10-28T14:30:00Z, 2026-10-28T14:30:00+02:00) or, for dateFormat, a number of milliseconds since 1 January 1970 UTC. Anything else comes back unchanged. A number given to format with a number pattern (f:2, %.2f) is still formatted as a number.
Time zone
- A value with a zone (
Zor an offset such as+02:00, or a number of milliseconds) is an exact moment. It is shown in the gateway machine's own time zone. Name another zone as the last argument, as an IANA name such asAmerica/ChicagoorUTC, to show it there instead. UTC is never used unless you ask for it. - A value without a zone (
2026-10-28,2026-10-28T14:30:00, or2026-10-28 14:30:00as SQLite'sdatetime()returns it) is a wall-clock time and is printed as written, with no conversion. A date alone never moves to the day before or after. If a query stores UTC without a zone and you want it shown in local time, return it with aZ(strftime('%Y-%m-%dT%H:%M:%SZ', t)). ThetagHistorysource already returnsZtimestamps.
now() and today() return the gateway's local time without a zone, so they print as they are.
Query parameters
A report's parameters bind to the :name placeholders in its SQL, the same way a named query's parameters do. The value is always sent to the database as a bound value and never pasted into the SQL text.
- Query data source. Write
:namefor each report parameter you want, for exampleSELECT * FROM readings WHERE day >= :start AND day < :end. Every:namein the SQL must be a report parameter that has a value, or the report refuses to run and says which one is missing. - Named query data source. A report parameter with the same name as one of the named query's parameters replaces that parameter's default. A query parameter the report does not have keeps its own default. The query must still declare every parameter its SQL uses.
- Parameter values are converted to the declared type first, so a number parameter binds as a number. Dynamic values in a schedule (
{today},{weekStart},{monthStart}) are resolved before they are bound, and a scheduled run binds them the same way a run from a screen does.
A named query runs on the database connection it is configured with. A report no longer has to use the default connection.