Jinja template filter_values not working/not showing

[paridbushati]

Hello,

I am creating a virtual dataset in sql lab with the query:
SELECT
*
FROM table
WHERE 1=1
{% if filter_values('column1') %}
AND column1 IN ({{ filter_values('column1') | join("','") | prepend("'") | append("'") }})
{% endif %}
{% if filter_values('column_date') %}
AND column_date BETWEEN '{{ filter_values('column_date')[0] }}' AND '{{ filter_values('column_date')[1] }}'
{% endif %}
{% if filter_values('column2') %}
AND column2 IN ({{ filter_values('column2') | join("','") | prepend("'") | append("'") }})
{% endif %}

When i create the chart and click view_query, the template doesn't show up and the filtering in the dashboard is done outside of the virtual dataset :
SELECT columns...
FROM (SELECT
columns...
FROM table
WHERE 1=1

) AS virtual_table

I have tried a lot of things but i have run out of ideas. Template processing is also enabled. How can i access the filters in the dashboard using filter_values?

[dosubot[bot]]

Hello @paridbushati! I'm Dosu, a friendly bot here to help you solve bugs, answer questions, and guide you toward contributing. I'm ready to assist you while you wait for a human maintainer. Let's tackle this issue together!

I found a similar discussion that might help you resolve the issue with filter_values in your Jinja template. In the Dynamic SQL query thread, it was suggested to ensure that Jinja templating is enabled in your superset_config.py by setting the ENABLE_TEMPLATE_PROCESSING feature flag. Since you've already confirmed that template processing is enabled, here are a few additional steps you can take:

1. Check Filter Configuration: Ensure that the filter names used in filter_values('column1'), filter_values('column_date'), and filter_values('column2') match exactly with the filter components' names in your dashboard.

2. Dashboard Filter Application: Verify that the filters are correctly applied to the virtual dataset in the dashboard. Sometimes, filters might be applied at the dashboard level rather than within the SQL query, which could explain why the template doesn't show up in the query.

3. Debugging: You can add debug statements in your SQL query to print out the values of filter_values to ensure they are being captured correctly. For example, you can use {{ filter_values('column1') }} to see what values are being passed.

If these steps don't resolve the issue, consider reviewing the filter configuration in your dashboard to ensure it aligns with the query logic.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[paridbushati]

@dosu I have checked the filter names and they correspond to the filter columns that i am using in the filter. I don't understand why the jinja template isn't rendered when clicking view query in the chart or in the dashboard. I have tested simple templating logic and it works in sql lab. However something is wrong. The filter in the dashboard then is not applied inside the virtual dataset but in the wrapper outside of it.

[dosubot[bot]]

In Superset, Jinja templates in virtual datasets are rendered when the ENABLE_TEMPLATE_PROCESSING feature flag is enabled, which you've confirmed is the case. However, the behavior you're experiencing, where filters are applied outside the virtual dataset, might be due to how Superset handles filters at the dashboard level versus within a virtual dataset.

Superset differentiates between filters applied at the dashboard level and those within a virtual dataset. Dashboard-level filters are often applied as a wrapper around the virtual dataset query, which might explain why you're seeing the filters applied outside of your intended SQL logic.

To ensure that the filters are applied within the virtual dataset, you might need to explicitly handle them in your SQL query using Jinja templating. However, if the filters are still not being applied as expected, it could be due to the way Superset processes these filters in the context of dashboards, which might not fully integrate with the Jinja logic in virtual datasets.

Unfortunately, the specific conditions under which Jinja templates are rendered in SQL Lab versus in the dashboard are not detailed in the available documentation, which might be contributing to the confusion ^[1]. You might want to explore further how Superset's dashboard filter application logic interacts with virtual datasets to better understand this behavior.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}