Filtering SQL Lab queries used in visualizations. Using Jinja templates for filters

2.1k Views Asked by At

I'm a newbie with Superset, and I'm struggling with Jinja templates to create custom filters. I have superset 0.37.2 installed in an Pop! OS 20.04 (an Ubuntu variant) machine, with python 3.8.5. Our dashboards are mostly created over the results of some complicated queries, so we will need to create visualizations on SQL Lab saved queries. Sometimes I'll need to customize the filters, and I thought of using Jinja Templates, but I'm missing steps or misusing them.

So say, I have this SQL Lab query on the flights table of the example schema (I'm going to name it Q_FLIGHTS_PER_DAY):

SELECT tfl.AIRLINE, tfl.DAY_OF_WEEK, COUNT(1) num_flights
FROM flights tfl
GROUP BY tfl.AIRLINE, tfl.DAY_OF_WEEK

And I want to create a custom filter to select the airline, so I create a second SQL Lab query:

SELECT DISTINCT AIRLINE p_airline FROM flights

I name this query Q_FILTER_AIRLINES, and create a Filter box chart using this query (I name the chart V_FB_P_AIRLINE): Filter box creation

Filter configuration

So I once with my filter box created, I go to my original query Q_FLIGHTS_PER_DAY and add the Jinja template to use the values selected in p_airline to filter my query:

/* Template to filter */
{% set param_airline = filter_values('p_airline', 'AS')[0]  %}
/* Filtered query */
SELECT tfl.AIRLINE, tfl.DAY_OF_WEEK, COUNT(1) num_flights
FROM flights tfl
WHERE tfl.AIRLINE = '{{cache_key_wrapper(param_airline)}}'
GROUP BY tfl.AIRLINE, tfl.DAY_OF_WEEK

And using this saved query I create a bar chart V_FLIGHTS_PER DAY, and add both charts to a new dashboard, the filter box and the bar chart: Dashboard with fixed param value

Is there something else I should do for this to work or what I'm trying to do is not possible?

0

There are 0 best solutions below