The query parametrisation allows for simpler SQL for a lot of common cases as well as creating reports that can be filtered or adjusted via controls on the Dashboard (See Interactive Dashboards).
The general form the parameters take is a parameter expression wrapped in curly braces, such as in this example:
SELECT {created_at:aggregation} AS "period", SUM(revenue)
FROM sales
WHERE {created_at=timerange} AND {branch=branches_filter} AND {sales_rep=sales_reps_filter}
GROUP BY 1
ORDER BY 1
In case you need to use a complex expression as a left-side of the parameter (e.g. COALESCE or a sub-query), wrap it in square brackets like this:
SELECT {[COALESCE(created_at, NOW())]:aggregation} AS "period", SUM(revenue)
FROM sales
WHERE {created_at=timerange} AND {branch=branches_filter} AND {sales_rep=sales_reps_filter}
GROUP BY 1
ORDER BY 1
There are several types of possible parameters:
1. Time aggregation
Time aggregation parameter allows to write compact queries that aggregate over a time period. When used in the non-hardcoded form ('aggregation'), it enables this control on the dashboard:
The general format of the parameter is:
{column_name:period_name}
where column_name refers to a valid column in SQL and period_name is one of the values below (parametrised or hardcoded intervals)
e.g.
SELECT {saletime:aggregation}, SUM(revenue)
FROM sales
GROUP BY 1
ORDER BY 1
The parametrised values are:
| Parameter | Description |
| {column_name:aggregation} | time value trimmed to time period as selected via Dashboard filter bar |
| {aggregation_interval} | A length of the selected aggregation as interval - will translate to something like INTERVAL '1 DAY' for postgres |
There are also variants with value that is hardcoded, i.e. using these would not make the dashboard parametrised, these are useful in case a query is always aggregated in a specific way:
Example that would always display revenue by week:
SELECT {saletime:week}, SUM(revenue)
FROM sales
GROUP BY 1
ORDER BY 1
The possible hardcoded interval values are:
| Period name | Description |
| second | time value trimmed to second |
| minute | time value trimmed to minute |
| hour | time value trimmed to hour |
| day | time value trimmed to day |
| week | time value trimmed to week starting on Monday |
| week_sunday | time value trimmed to week starting on Sunday |
| month | time value trimmed to month |
| quarter | time value trimmed to quarter |
| year | time value trimmed to year |
2. Time ranges
Time range parameter allows to filter the report by time. When used, it enables this control on the dashboard:
The general format of the parameter is:
... WHERE {column_name=timerange}
e.g.
SELECT SUM(revenue) FROM sales
WHERE {saletime=timerange}
Here is a list of all the parameters related to time ranges:
| Period name | Description |
| {column=timerange} | filters values of the specified column to the selected timerange |
| {column=timerange_previous} | filters values of the specified column to the same length of the selected timerange preceding the selected one (very useful for comparison e.g. as delta in the Number chart) |
| {column=timerange_compare} | (Coming soon) filters values of the specified column to a selected secondary timerange (useful to compare values between two selected timeranges) |
| {timerange_start} | {timerange_compare_start} | timestamp of beginning of the selected timerange, 01/01/0000 in case there is no start |
| {timerange_end} | {timerange_compare_end} | timestamp of end of the selected timerange, 23/12/9999 in case there is no end |
| {timerange_start_tz}, ... | a variant of the above with timezone adjustment according to the organization and datasource timezone settings |
| {timerange_seconds} | Number of seconds in the selected timerange (also for timerange_compare) |
| {timerange_interval} | Selected timerange as interval (also for timerange_compare) |
3. Custom value filters
See details on the Custom Filters page.