Skip to main content

Query Parameters


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

Importance Notice

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 (
FROM orders
orders.created_at > '2023-01-01' <-- we want to make this dynamic
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 (
FROM orders
{% filter(created_at_param) %} orders.created_at {% end %}
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 filterGenerated SQL (simplified)
before 2023WHERE u.created_at < 2023-01-01
between 2023-2024WHERE u.created_at ≥ 2023-01-01 AND u.created_at < 2024-01-01
user choose nothingWHERE 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:

  1. The Field input should refer to a normal dimension (not a parameter) to fetch the list of suggestions.
  2. 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.


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 filterGenerated queries (simplified)
is oneWHERE column = 'one'
one, two, threeWHERE column in ('one', 'two', 'three')

Let us know what you think about this document :)