- 21 Dec 2022
- 2 Minutes to read
- Print
- DarkLight
Using Jinja to Manipulate Custom SQL Filters of a Chart
- Updated on 21 Dec 2022
- 2 Minutes to read
- Print
- DarkLight
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:
- Add Jinja syntax to the chart's custom SQL filter, to modify it using dashboard filters.
- Create a dataset with options to populate our dashboard filter.
- 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.