- 04 Nov 2022
- 3 Minutes to read
- Print
- DarkLight
Using Jinja to Filter a Dashboard
- Updated on 04 Nov 2022
- 3 Minutes to read
- Print
- DarkLight
- New
This article explains how to use Jinja Templating to create dashboard filters that interact with a SQL query. It consists of four steps: creating a virtual dataset, modifying the query to include Jinja, adding the chart to a dashboard, and configuring the dashboard filters. After creating the virtual dataset and modifying it with Jinja, you can add it to a dashboard and configure its filters. Finally, you can view your inner query displayed via Jinja Templating.
Overview
In this article, we will learn how to use Jinja Templating to create dashboard filters that can directly interact with a Virtual Dataset's inner SQL query.
The Process
This process consists of 4 steps:
- Create a Virtual Dataset.
- Modify the Virtual Dataset query to include the Jinja Templating structure.
- Create a Chart from the Virtual Dataset and add it to a Dashboard.
- Configure the Dashboard filters.
Let's have a closer look at each step.
Step 1: Create a Virtual Dataset
For this example, we'll use the Vehicle Sales
table from the examples
database.
Consider the query below:
WITH calculation as (
SELECT count(*), country
FROM "Vehicle Sales"
WHERE product_line in ('Classic Cars', 'Motorcycles')
GROUP BY country
)
SELECT * FROM calculation
If you create a Virtual Dataset using this query, it will have only two columns: count
and country
. As a consequence, you won't be able to create a dashboard filter for product_line
. That's why we'll use Jinja.
However, if you execute the query including the Jinja templating on the SQL Lab directly, it won't return any results as we don't have the filters in there to pass the values, which means that the executed query would end up being:
WITH calculation as (
SELECT count(*), country
FROM "Vehicle Sales"
WHERE product_line in ('')
GROUP BY country
)
SELECT * FROM calculation
That being said, let's create the Virtual Dataset without Jinja first:
In the Toolbar, hover your cursor over SQL Lab and, in the sub-menu, select SQL Editor.
In SQL Editor, select the following database, schema, and table:
...then, in the SQL content panel, copy & paste the following:
WITH calculation as (
SELECT count(*), country
FROM "Vehicle Sales"
WHERE product_line in ('Classic Cars')
GROUP BY country
)
SELECT * FROM calculation
Select Run and then Explore. The Save or Overwrite Dataset window appears.
In this example, we named our new virtual dataset "Filtering with Jinja" and then selected Save & Explore.
Step 2: Modify the Virtual Dataset to Include Jinja
A new tab will open in your browser featuring your new virtual dataset.
Our first step is to include the Jinja templating in the dataset:
In the Chart Source panel, select the vertical ellipses and, in the sub-menu, select Edit dataset.
The Edit Dataset window appears.
In the Click the lock to make changes field, select the lock icon to enable dataset editing.
In the SQL text-entry panel, replace product_line in ('Classic Cars')
with product_line in ({{ "'" + "', '".join(filter_values('product_line')) + "'" }})
and then select Save.
Lastly, in the Confirm Save window, select OK.
About the Jinja Templating structure...
- The beginning (
{{ "'" + "', '".join(
) and ending () + "'" }}
) parts are responsible for concatenating everything — this structure allows filtering with multiple values. - The
filter_values('Column-Name')
is the actual function that's going to listen to the filter value. In this case,Column-Name
isproduct_line
- we'll talk more about this in the coming steps.
Step 3: Add Chart to Dashboard
Now that we have properly modified the Virtual Dataset, we can execute our chart by selecting Run. Don't worry when nothing happens... the chart will not return any results because it is not yet configured.
Select Save to save the chart to a new or existing dashboard.
In this example, we titled the chart "Jinja Filtering Chart" and saved it to a new dashboard.
When done, select Save & Go to Dashboard.
Step 4: Configure the Dashboard Filters
The dashboard will launch in your browser. After it appears, let's start the final step of configuring the dashboard's filters.
Start by selecting the right arrow to expand the Filter area, and then select + Add/Edit Filters.
The Add and edit filters window appears.
In the Filter Name field, enter a name for your filter. In this example, we will create a filter that displays options from our Jinja-revised product line... so we will name the filter "Product Line."
In the Dataset field, let's switch from our virtual dataset to the actual source for the inner query, which is Vehicle Sales.
In the Column field, we will select product_line because this is what defines our column name in the Jinja structure.
Next, select the Scoping tab to ensure that the filter is mapped to your chart.
When done, select Save.
Great work! Now let's see the filter in action.
In the new Product Line filter, we selected Motorcycles, Planes, Trains, and Ships, and then selected Apply Filters.
Looks good! If you'd like to see your inner query displayed via Jinja Templating, go ahead and select the vertical ellipsis icon in the chart and then choose View query.
...notice the highlighted portion in the graphic below: