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:

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 Line Chart (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(price_each) > 
{% 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 a dashboard filter powered by the threshold column:

• 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:

Save your chart and add it to a dashboard.

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

To create a virtual dataset powered by this SQL query:

1. Access your Workspace.

2. Navigate to SQL > SQL Lab.

3. Make sure the examples connection is selected.

4. Paste the query and click on RUN.

5. To save it as a dataset, click on the SAVE dropdown and choose Save dataset.

6. Provide a name for your dataset and click on SAVE & EXPLORE.

Note that the column name (in this case defined as threshold), is used on the Jinja syntax, by the filter_values() macro. Make sure to update your Jinja syntax if using a different column name.

Step 3: Configure the dashboard

Access the dashboard created with your chart 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.