Starting with a SQL query, I included variables in the WHERE clause and created filters on the dashboard. The dropdown function appears to show the correct values to filter on, but when I apply the filter, the results don't reflect it and I'm still looking at the same full set of results as if the filter is not being applied. Any idea what I'm doing wrong?
Hi @b.penn
Post "Diagnostic Info" from Admin > Troubleshooting.
And post your query.
If you use the filter widget on the question (not on the dashboard), does it work correctly there?
Hi flamber, thanks for the quick response. I tried the filters using the widgets in the question and still not working. Unfortunately I don't have admin access so I can't try the other solution.
@b.penn Post your query. Sounds like you are not using the Field Filter syntax:
https://www.metabase.com/learn/sql-questions/field-filters
select distinct on (projects.name) projects.name as "Project Name", sites.name as "Site Name", case when assigned_questions.question_id = 4277 then 'Yes' else 'No' end, projects.status as "Project Status",
from assigned_questions
left join sites on sites.id = assigned_questions.site_id left join projects on projects.site_id = sites.id
[[where sites.name = {{site_name}} and projects.name = {{project_name}} and projects.status = {{project_status}}]]
group by sites.name, projects.name, assigned_questions.question_id, projects.status
order by projects.name, assigned_questions.question_id desc, sites.name
@b.penn Since you are using one big optional clause, then you'll have to fill out all 3 filters before it takes effect.
And you are not using Field Filters, but just simple Text filters - or at least that's the syntax you are using.
For reference - you should read the entire page:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses
where true
[[and sites.name = {{site_name}}]]
[[and projects.name = {{project_name}}]]
[[and projects.status = {{project_status}}]]
Thank you, this was very helpful. Is it possible to create a filter widget based on the custom column created in the CASE clause within the SELECT? If so, any guidance on the syntax would be great, thanks again.
To clarify, I am using the field filters and have corrected the syntax for that.
@b.penn Field Filters are different from simple filters, since you cannot control the SQL that is generated, so you can only use it in the WHERE-clause. And you cannot use Filter Filters with table aliases.
This is described in the documentation.
If you are looking for something different, then I don't understand it and you'll need to provide more context/screenshots.