Query Parameters
Concept
When creating analytics queries, we often want the SQL query to be more dynamic based on user input. This allows for building complex analytics queries, reusing queries, and improving query performance.
Holistics now offers the "Query Parameters" feature that supports this functionality. Query Parameters allow business users or end-users to apply values to a dashboard filter, which are then passed into the SQL definition of a model at runtime.
This feature is only available in Holistics 4.0.
How to set up Query Parameters
Query Parameters cannot currently be used in SQL definitions of Dimensions and Measures
.
Assume you have a model called orders_listing
with a fixed condition on the created_at
column, as shown below:
Model orders_listing {
query: @sql
WITH orders_partitioned AS (
SELECT *
FROM orders
WHERE
orders.created_at > '2023-01-01' <-- we want to make this dynamic
GROUP BY 1
)
SELECT *
FROM orders_partitioned
;;
dimensions { ... }
measures { ... }
}
Step 1: Define Parameters in the Model
Within your model, define a field called param
similar to how you define a dimension or measure. The param can be one of the following types:
- Text
- Number
- Date
- DateTime
- True/False
For example:
param created_at_param {
label: 'Created At'
type: 'date'
}
Step 2: Modify your Query to use Parameters
Incorporate the parameter into your query using the following syntax:
{% filter(param) %} column name {% end %}
Then replace the param
and column name
with your:
- param that defined in Step 1
- and the column to which you want to apply the filter.
For example, the orginal query:
WHERE orders.created_at > '2023-01-01'
will be rewritten as:
WHERE {% filter(created_at_param) %} orders.created_at {% end %}
The complete query will look like this:
WITH orders_partitioned AS (
SELECT *
FROM orders
WHERE
{% filter(created_at_param) %} orders.created_at {% end %}
GROUP BY 1
)
SELECT *
FROM orders_partitioned
Step 3: Use Parameters via a Dashboard Filter
The final step involve:
- Adding our model to a dataset
- Creating a report based on the model
- Creating a filter in the dashboard that maps to that parameter
Example: The “Created At” param in the “Orders Listing” report is mapped using this filter
The final result is a filtered report with the value "July 2023”
How SQL is generated?
When using the filter syntax in your query, Holistics automatically generates SQL based on the filter logic defined in the dashboard.
For example:
WHERE {% filter(created_at_param) %} u.created_at {% end %}
… will be translated to the following SQL when executed:
Dashboard filter | Generated SQL (simplified) |
---|---|
before 2023 | WHERE u.created_at < 2023-01-01 |
between 2023-2024 | WHERE u.created_at ≥ 2023-01-01 AND u.created_at < 2024-01-01 |
user choose nothing | WHERE 1 = 1 |
In the last case, if the user does not select any filter value, Holistics removes the filter and uses an always true expression (1 = 1).
The filter tag {% filter %}
is designed to be optional. If no filter values are provided, it will entirely skip the filter instead of requiring a value.
How to set up Suggestion Values for Parameters?
When creating a parameter, you might notice that the parameter does not have any suggested values. To provide a list of suggestions, you need to set up suggestion values at the dashboard-filter level.
To do this:
- The
Field
input should refer to a normal dimension (not a parameter) to fetch the list of suggestions. - Map the filter to the parameter in the
Update Reports
section.
Example: The suggestion list is derived from the "Product" dimension, and the chosen value is passed to the "Product Param" parameter.
FAQs
Can we make the behavior of {% filter %}
required by default, rather than optional?
This use case is currently under development. Please reach out to us for updates on its progress.
How can I use multiple values for the filter?
The {% filter %}
syntax automatically supports multiple values selection.
For example:
Dashboard filter | Generated queries (simplified) |
---|---|
is one | WHERE column = 'one' |
one, two, three | WHERE column in ('one', 'two', 'three') |