SQL in Widgets

Table, KPI, Chart, Heatmap, Text and Planning board load their data from the Data Lake with an SQL query. The Filter container (type "Record via SQL"), the Timer (references via SQL) and Time Entries (additional filter) use SQL as well. This page sums up what applies to all of these queries – so that a query works on the first test run and returns the right numbers.

The three most common mistakes

A date field is compared with plain text: '2026-01-01' instead of DATE '2026-01-01'.
A date field is converted although it already is a date: from_iso8601_date(...), date_parse(...) or CAST(... AS VARCHAR).
A placeholder has the wrong quotes: '@periodStart' or '$(customer)'.

This also applies to suggestions from the SQL assistant or an AI assistant: a quick check of these three points before applying them saves time.

Tables and columns

Every data schema is a table. The table name is the technical name of the data schema, for example urlaubsantraege, or for Marketplace apps app_invoices and app_contacts.
Derived data schemas: If a data schema has derived data schemas, there is an additional table <name>_only. It contains only the records of the data schema itself, without those of the derived schemas (see Inheritance and hierarchies).
Columns are named like the technical field names, in lower case. Every table also has:

Column	Content
`id`	id of the record
`data_schema_id`	id of the data schema
`document_name`	document name of the record
`created_at`, `updated_at`	when the record was created and last changed (timestamp)
`created_by`, `updated_by`	who created and last changed the record

Names of result columns: A result column is named exactly as its alias is written. The column mapping of Chart, Heatmap and Planning board, the summary row of the Table and the {{name}} placeholders of the Text widget need exactly this spelling. Aliases are best written in lower case, without spaces or special characters, for example AS gross.

Words such as start, end or row are not suitable as aliases because they have a meaning of their own in SQL. The examples in this documentation therefore use start_day, end_day and row_key.

Column types

Columns have real types, depending on the field type in the data schema. A date field already is a date, a number field already is a number – nothing needs to be converted.

Field in the data schema	SQL type	Example
Date	`DATE`	`app_invoice_date >= DATE '2026-01-01'`
Date with time, `created_at`, `updated_at`	`TIMESTAMP`	`created_at >= TIMESTAMP '2026-01-01 00:00:00'`
Number	`DOUBLE`	`app_gross_total > 1000`
Text, selection	text	`status = 'Genehmigt'`
Reference	text: the id of the linked record	`LEFT JOIN app_contacts c ON c.id = i.app_recipient`
Lists and objects	`ARRAY` or `ROW`	expand with `UNNEST(...)` or output as text with `CAST(x AS JSON)`

Only a date stored in a text field needs from_iso8601_date(...) or date_parse(...).

Comparing dates and timestamps

Goal	Right	Wrong	What happens with the wrong way
Date field from a fixed day	`app_invoice_date >= DATE '2026-01-01'`	`app_invoice_date >= '2026-01-01'`	Error `TYPE_MISMATCH`: a date cannot be compared with text.
Using a date field	`app_invoice_date` as it is	`from_iso8601_date(app_invoice_date)`, `date_parse(app_invoice_date, '%Y-%m-%d')`	Error `FUNCTION_NOT_FOUND`: these functions expect text, not a date.
Comparing two dates	`app_due_date < current_date`	`CAST(app_due_date AS VARCHAR) < CAST(current_date AS VARCHAR)`	Needless detour via text; compare dates directly.
Date field relative to today	`app_invoice_date >= current_date - INTERVAL '30' DAY`
Date field since the start of the year	`app_invoice_date >= date_trunc('year', current_date)`
Timestamp from a fixed day	`created_at >= TIMESTAMP '2026-01-01 00:00:00'`	`created_at >= '2026-01-01'`	Error `TYPE_MISMATCH`.
Timestamp on one day	`date(created_at) = current_date`	`created_at = current_date`	No error, but only entries from exactly 00:00 are found.

A date field can also be compared directly with a timestamp expression, for example app_invoice_date >= date_trunc('year', current_timestamp). For the selected period of a widget, date(@periodStart) is the clearest form (see Placeholders).

Where the error message appears: When a query fails, the database names the cause, for example TYPE_MISMATCH or FUNCTION_NOT_FOUND. On the dashboard little of it is visible at the moment: the test run in the dialogs often only reports "Query failed", the KPI shows only an error icon, and the Text widget shows its text with the {{…}} not replaced. In case of an error, check the date comparisons and placeholders on this page first.

A complete query with fixed limits:

SELECT app_invoice_no AS invoice_no,
       app_invoice_date AS invoice_date,
       app_gross_total AS gross
FROM app_invoices
WHERE app_invoice_date >= DATE '2026-01-01'
  AND app_invoice_date < DATE '2027-01-01'
ORDER BY app_invoice_date

Placeholders

Placeholders are replaced as plain text at exactly their position before the query runs. Whether the result is valid SQL therefore depends on what is written around them. Upper and lower case of the placeholder do not matter.

Placeholder	Replaced by	Written correctly
`@currentUserEmail`	e-mail address of the person viewing the dashboard. Inserted without quotes, so write the quotes yourself	`WHERE antragsteller = '@currentUserEmail'`
`@currentUserSub`	user id of that person. Inserted without quotes, so write the quotes yourself	`WHERE user_sub = '@currentUserSub'`
`@currentDate`	today's date, e.g. `2026-11-02`, in UTC. Inserted without quotes	`WHERE app_due_date < DATE '@currentDate'` or simply `current_date`
`@currentYear`	current year, e.g. `2026`	`WHERE year(app_invoice_date) = @currentYear`
`@currentMonth`	current month, always two digits, e.g. `03`	`WHERE date_format(app_invoice_date, '%Y-%m') = '@currentYear-@currentMonth'`
`@currentTimeStamp`	current point in time as text, e.g. `2026-11-02T09:15:00.000Z`	`WHERE created_at < from_iso8601_timestamp('@currentTimeStamp')` or simply `current_timestamp`
`@periodStart`, `@periodEnd`	start and end of the selected period as a ready-made timestamp expression; `@periodEnd` is the first moment after the period	Timestamp: `created_at >= @periodStart AND created_at < @periodEnd` Date: `app_invoice_date >= date(@periodStart) AND app_invoice_date < date(@periodEnd)`

Typical mistakes with placeholders:

'@periodStart' in quotes becomes text – comparing it with a date fails with TYPE_MISMATCH.
DATE '@periodStart' does not give a valid date because @periodStart is an expression, not a date text.
@currentUserEmail without quotes gives invalid SQL because the e-mail address must be text in quotes.

@currentDate follows UTC: shortly after midnight in Central Europe it still returns the previous day. For "this month", app_invoice_date >= date_trunc('month', current_date) is usually simpler than @currentMonth.

Where the period comes from

Widget	`@periodStart` and `@periodEnd` come from
Table, Chart, Heatmap	the time range of a surrounding filter container; otherwise the widget's own time filter; without both, the current month
KPI, Text	only the time range of a surrounding filter container; otherwise always the current month – these widgets have no time filter of their own
Planning board	the board's period filter or the time range of a surrounding filter container
Time Entries (Additional filter), Timer (reference SQL query)	currently always the current month, not the selected period

Table, Chart and Heatmap offer their own time filter in the dialog only once the query contains both placeholders. "All" reaches from year 1 to year 9999 and therefore also includes entries in the future. With a custom calendar range the selected last day counts; @periodEnd is then the day after.

Values from the filter container

If a widget lies completely inside the area of a Filter container, its filters are available as $(key). Unlike the @ placeholders, $(…) is replaced by a complete SQL value including quotes. Never write quotes of your own around it.

Filter type	Placeholder	Becomes
Record, Record via SQL	`$(customer)` or `$(customer.id)`	id of the selected record, in quotes
Record, Record via SQL	`$(customer.document_name)`	document name, in quotes
Record	`$(customer.fieldname)`	a field of the selected record: text in quotes, numbers without
Time range	`$(period.start)`, `$(period.end)`	start and end (exclusive) as a timestamp expression – like `@periodStart` and `@periodEnd`
Select list	`$(status)`	the selected value, in quotes
Yes/No	`$(open_only)`	`TRUE` or `FALSE`
"All" selected	every placeholder of this filter	the text `'__ALL__'`
empty, without the "All" option	every placeholder of this filter	`NULL` – a comparison `column = NULL` never matches

So that "All" really shows everything, the condition is written in this pattern:

SELECT app_invoice_no AS invoice_no,
       app_invoice_date AS invoice_date,
       app_gross_total AS gross
FROM app_invoices
WHERE ($(customer) = '__ALL__' OR app_recipient = $(customer.id))
  AND app_invoice_date >= date($(period.start))
  AND app_invoice_date < date($(period.end))
ORDER BY app_invoice_date DESC

A Yes/No filter is used like this: WHERE ($(open_only) = FALSE OR app_open_amount > 0).

Typical mistakes:

app_recipient = '$(customer.id)' – the extra quotes give invalid SQL.
$(period) without .start or .end becomes NULL.
A $(…) in a widget that does not lie inside a filter container stays as text – the query fails. Timer and Time Entries never fill in $(…).

Empty values (NULL)

Empty values affect each widget differently. COALESCE sets a replacement value:

Situation	Result without `COALESCE`	Solution
KPI with `SUM(...)`, but no matching records	The sum is empty, the KPI shows "No data".	`COALESCE(SUM(app_gross_total), 0)`
Table whose first row is empty in a column	The column is missing from the whole table.	`COALESCE(c.document_name, '-')`
Text widget with an empty value	The placeholder becomes empty text.	`COALESCE(...)` with a sensible replacement
Chart with an empty value	The value is drawn as 0, not as a gap.	decide in SQL which value should apply
Dividing by a count that can be 0	error or unusable value	`NULLIF(COUNT(*), 0)` as divisor

Formatting numbers and dates

Widgets show values as the query returns them, for example 1234.5 or 2026-11-05. Exception: the KPI formats values itself when the unit is "€" (German format 1.234,56 €) or "$" ($1,234.56). For Text widgets, the format is built in SQL:

format('%,.2f', x) returns 1,234.56 (English format).
replace(replace(replace(format('%,.2f', x), ',', ' '), '.', ','), ' ', '.') swaps the separators and returns the German format 1.234,56.
date_format(x, '%d.%m.%Y') returns a date as 05.11.2026; date_format(x, '%m/%d/%Y') as 11/05/2026.

Example for a Text widget with the content "Open receivables: {{open_total}} from {{invoices}} invoices (as of {{as_of}})":

SELECT COUNT(*) AS invoices,
       format('%,.2f', COALESCE(SUM(app_open_amount), 0)) AS open_total,
       date_format(current_timestamp, '%d.%m.%Y') AS as_of
FROM app_invoices
WHERE app_open_amount > 0

Formatted values are text. In a table they are therefore sorted as text (05.11.2026 before 12.01.2026), and the summary row cannot add them up. In tables, numbers and dates stay unformatted wherever sorting or sums matter.

Permissions

Every query runs with the permissions of the person viewing the dashboard. Only what this person may read is counted and shown. The same KPI can therefore show different numbers for two people – this is intended. Queries only read; they do not change any data.

Check before saving

Try the query in the dialog with Test, Test Run or Test run.
Compare date and timestamp fields without converting them; write fixed values as DATE '…' or TIMESTAMP '…'.
Put @currentUserEmail and @currentUserSub in quotes; never @periodStart, @periodEnd or $(…).
Protect sums and columns that can be empty with COALESCE.
Write aliases exactly as they are used in the mapping, the summary row or {{…}}.

Related pages: Filter container, Table, KPI, Planning board.