Query Parameters

info

This feature is only available on Holistics 4.0 (Analytics As-Code). Please reach out if you want access to Holistics 4.0.

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.

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 (
      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).

TIP

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.

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')