---
title: "Using Jinja to manipulate custom SQL filter applied on a Chart"
slug: "using-jinja-to-manipulate-custom-sql-filter-applied-on-a-chart"
updated: 2023-12-08T20:18:30Z
published: 2023-12-08T20:18:30Z
canonical: "docs.preset.io/using-jinja-to-manipulate-custom-sql-filter-applied-on-a-chart"
---

> ## 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 Manipulate Custom SQL Filters of a Chart

## Overview

In this article, you will learn how to use Jinja Templating to dynamically change custom SQL filter applied on a chart:

![](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/Jinja%20example.gif)

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

[](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/Jinja example - threshold filter using Jinja.zip)Jinja example - threshold filter using Jinja17.90 KB[](https://cdn.document360.io/4749ddf8-aa05-4f3f-80e1-07a5d2d0f137/Images/Documentation/Jinja example - threshold filter using Jinja.zip)

---

## The Process

This process consists of 3 steps:

1. Add Jinja syntax to the chart's custom SQL filter, to modify it using dashboard filters.
2. Create a dataset with options to populate our dashboard filter.
3. 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 **Line Chart** (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:

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

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:

```sql
sum(price_each) > 
{% 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 a dashboard filter powered by the `threshold` column:

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

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

Save your chart and add it to a dashboard.

---

## 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](https://docs.preset.io/docs/google-sheets-connection).
- **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](https://docs.preset.io/docs/virtual-datasets) with below query:

```sql
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
```

To create a virtual dataset powered by this SQL query:

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**.
5. To save it as a dataset, click on the **SAVE** dropdown and choose **Save dataset**.
6. Provide a name for your dataset and click on **SAVE & EXPLORE**.

Note that the column name (in this case defined as `threshold`), is used on the Jinja syntax, by the `filter_values()` macro. Make sure to update your Jinja syntax if using a different column name.

---

## Step 3: Configure the dashboard

Access the dashboard created with your chart and create a new filter - use the dataset/column created on the previous step:

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

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.