Using Jinja to Filter a Dashboard
  • 04 Nov 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light

Using Jinja to Filter a Dashboard

  • Dark
    Light

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:

Jinja_Database_Selection

...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.

Jinja_Run_and_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.

Jina_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.

Jinja_Unlock_Data

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.

Jinja_Replace_SQL

Lastly, in the Confirm Save window, select OK.

Jinja_Confirm_Save

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 is product_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.

Jinja_Blank_Chart

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.

Jinja_Save_Chart_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.

Jinja_Select_AddEdit_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.

Jinja_Configured_Filter

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.

Jinja_End_Chart1

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.

Jinja_Select_View_Query

...notice the highlighted portion in the graphic below:

Jinja_Inner_Query



Was this article helpful?