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.