Using Jinja to Filter a Dashboard

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:

1. Create a Virtual Dataset.
2. Modify the Virtual Dataset query to include the Jinja Templating structure.
3. Create a Chart from the Virtual Dataset and add it to a Dashboard.
4. 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.

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: