Using Jinja to Manipulate Custom SQL Filters of a Chart

Overview

In this article, you will learn how to use Jinja Templating to dynamically change custom SQL filter applied on a chart:

You can download a ZIP file to import this example dashboard on your Workspace — choose the option according to your Workspace location:

The Process

This process consists of 3 steps:

1. Add Jinja syntax to the chart's custom SQL filter, to modify it using dashboard filters. 
2. Create a dataset with options to populate our dashboard filter.
3. Configure the dashboard.

Let's have a closer look at each step.

Step 1: add Jinja syntax to the chart's custom SQL filter

On below chart, we're using a Time-Series Line Chart V2 (connected to the Vehicle Sales dataset from the examples database), to calculate the sum of price values for each product line, for the year of 2003: 

Let's use Jinja to control a threshold value for the aggregation result. Add a custom SQL filter, using below syntax - note that, since we're filtering a metric, we should use the HAVING option:

sum(quantity_ordered) > 
{% if filter_values('threshold')|length %}
  {{ filter_values('threshold')[0] }}
{% else %}
  0
{% endif %}

The syntax has an if statement, checking if there's any value applied to the threshold filter:

• If so, the filter value would be added to the SQL syntax.
• If not, 0 would be used as the default value.

This is the final result:

Step 2: Create a dataset to populate the dashboard filter

There are some different ways to achieve this goal:

• Creating a table/view on the database level: could be useful so that you can manipulate the table values directly on your database;
• Using a Google Sheets file: useful when you don't want to handle this on the database level. Further details on connecting Google Sheets as a database to Preset on this article.
• Using an ad-hoc SQL query: fast approach that can be very useful when creating just a few options. 

We'll use the last approach for this example, creating a virtual dataset with below query:

select 500 as threshold
union all
select 750 as threshold
union all
select 1000 as threshold
union all
select 1500 as threshold
union all
select 2000 as threshold

Step 3: Configure the dashboard

After saving your chart to a dashboard, access it and create a new filter - use the dataset/column created on the previous step:

Important details:

• Since the chart is using another dataset, this filter wouldn't be automatically mapped to it. Access the SCOPING tab, and map the filter to the chart.
• Disable Can select multiple values, since we won't use multiple values for this example. 

You should now be able to interact with the chart's custom SQL filter using a dashboard filter.