july 27, 2023
Output different dataset fields in one table column depending on the filter value in Apache Superset
It is not uncommon for analysts in their work to use flat tables to output metrics grouped by different attributes. Output of flat tables with a set of metrics is not problematic in Apache Superset. However, if there are many such tables, there may not be enough space in the information part of the dashboard. There is a solution using tabs, but you have to create a custom chart for each tab, which also increases the number of queries.
We found a solution that allows us to use only one chart to display the same type of tables with grouping by different columns using internal filter and Jinja Templates.
1. Let’s create a test query:
select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
union all select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
The table should output column_1 or column_2 and the corresponding value. In the final dataset we will use it as a nested query. To begin with, let’s query column_1 as column and value and create a chart with the table for it.
select column_1 as column, value
from (
select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
union all select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
)
2. Let’s create a dataset for the filter with column names and the filter itself:
Query the column directory for the filter
select ‘column_1’ as column_by
union all select ‘column_2’ as column_by
3. Now you need to add the selected filter value to the table dataset.
You can use the Jinja template to pull the filter value into the query. To make the templates work, you need to enable the ENABLE_TEMPLATE_PROCESSING function flag in superset_config.py. Python code can then be embedded into virtual datasets.
To query the filter value, use the following construct {{ filter_values(‘column_by’, )[0] }}.
From the dashboard, go to the chart editing area:
The dataset editing window will open. In it you need to click on the lock to open the edit option, replace «column_1» with the jinja template and click «Save».
Final Request:
select {{ filter_values(‘column_by’, )[0] }} as column,
value
from (
select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
union all select ‘col1_param1’ as column_1, ‘col2_param1’ as column_2, 1 as value
union all select ‘col1_param1’ as column_1, ‘col2_param2’ as column_2, 2 as value
union all select ‘col1_param2’ as column_1, ‘col2_param3’ as column_2, 3 as value
)
Let’s go back to the dashboard and update it. The result we get:
Notes:
1. Jinja templates do not work without context, i.e. when editing a chart outside the dashboard, filter values must be manually prescribed in the filter area of the chart.
2. If you have done everything correctly, but the filter does not work, check the Set filter mapping settings in the dashboard:
