---
title: "Using Jinja to Filter Data"
slug: "using-jinja-to-filter-data"
updated: 2023-12-01T20:48:52Z
published: 2023-12-01T20:48:52Z
canonical: "docs.preset.io/using-jinja-to-filter-data"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.preset.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Using Jinja to Retrieve Filter Values

## Overview

In this article, we will learn how to use Jinja Templating to retrieve filter selections that can be used directly in your [Virtual Dataset](/v1/docs/virtual-datasets)'s SQL query:

![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701462888539.png)

You can download a ZIP file to import this example dashboard on your Workspace:

[Jinja Example - Filter Values Macro.zip](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/Jinja%20Example%20-%20Filter%20Values%20Macro.zip)

---

## The Process

This process consists of 3 steps:

1. Create a Virtual Dataset.
2. Create a Chart from the Virtual Dataset and add it to a Dashboard.
3. 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:

```plsql
WITH calculation as (
	SELECT count(*) as 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 native dashboard filter to control the `product_line` options. This is just a simple example use-case in which your dashboard filter should be applied to the inner query (instead of the outer dataset query).

To achieve this implementation, we'll use **logical statements** and the `filter_values()` Jinja macro. Here's the updated SQL query:

```plsql
WITH calculation as (
	SELECT count(*) as count, country
	FROM "Vehicle Sales"
	WHERE 1=1
	{% if filter_values('product_line')|length %}
		and product_line in {{filter_values('product_line')|where_in}}
  	{% endif %}
	GROUP BY country
)
SELECT * FROM calculation
```

- The `filter_values()` macro returns an array of all options selected in a dashboard filter. `product_line` parameter is used to specify the column that's powering the filter we'll be using.
- The `if` statement is checking if this returned array has a length set (if `0` is returned, the statement condition is not met).
- The `|where_in` operator automatically formats this array into a SQL-compatible format (`["Classic Cars", "Vintage Cars"]` becomes `('Classic Cars', 'Vintage Cars')`).

To execute this query in SQL Lab:

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

![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701460162276.png)

This is the produced SQL query:

```plsql
-- 6dcd92a04feb50f14bbcf07c661680ba
WITH calculation as (
	SELECT count(*) as count, country
	FROM "Vehicle Sales"
	WHERE 1=1
	GROUP BY country
)
SELECT * FROM calculation
LIMIT 100001
-- 6dcd92a04feb50f14bbcf07c661680ba
```

Click on **CREATE CHART** to build a chart from this query.

---

## Step 2: Create a Chart from the Virtual Dataset and add it to a Dashboard

You should have been redirected to the **Chart Builder** view. For this example, we can use the table chart with the default configurations:

1. Click on **SAVE** in the top right corner. ![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701460222605.png)
2. Name your dataset, chart and also create a dashboard to it:  
![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701460270906.png)
3. Click on the **SAVE & GO TO DASHBOARD** button.

---

## Step 3: Configure the Dashboard filters

The dashboard will launch in your browser. To configure the filter:

1. Click on the right arrow to expand the Filter menu.![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701460437304.png)
2. Click on **+ Add/Edit Filters**.![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701460470654.png)
3. The Filter modal is launched. In the **Filter Name** field, enter a name for your filter.
4. In the **Dataset** field, select the dataset that actually contains the column with the desired options (in this case, the **Vehicle Sales** dataset).
5. In the **Column** field, select the column that contains the desired options (in this case, `product_line`). ![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701462621962.png)
6. Next, navigate to the **Scoping** tab, and map the filter to your chart. This process didn't happen automatically because the dataset powering the filter is different than the dataset powering the chart.![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701462649585.png)
7. Click on **Save**.

Great work! Now let's see the filter in action:

![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/image-1701462888539.png)