Add filter only if the variable is given with values in Metabase

Question

In Metabase, one can add variables with {{variable}} in queries, such as:

select *
from dashboard_demographic_view
where clinic_id = {{clinic}}

, where dashboard_demographic_view is a view created in advance. In the query above, rows where clinic_id matches what is given in {{clinic}} will be returned.

Still, I am curious what if I need that filter only when I give {{clinic}} a value, or otherwise the query should return all the rows. I've referred to SQL add filter only if a variable is not null but it is not helping in my case.

select *
from dashboard_demographic_view
where ({{clinic}} is null or clinic_id = {{clinic}})

The query above gives Unable to substitute 'clinic': param not specified. Found: ("clinic") when I don't specify clinic.

Theoretically this can be solved by making the variable {{clinic}} a field filter. However, for some reasons that cannot be done for a view (#8800 in metabase).

I am seeking workarounds other than making a real table to fulfill my purposes. Any help would be appreciated.

attekei · Accepted Answer · 2018-10-31 10:16:21Z

2

Would optional clauses help you here? See the end of Metabase Documentation: Sql Parameters.

Your code would essentially become something like

select *
from dashboard_demographic_view
[[where clinic_id = {{clinic}})]]

(This question has probably been asked earlier too – maybe worth checking if there are duplicates inside Stack Overflow.)

answered Oct 31, 2018 at 10:16

attekei

5414 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add filter only if the variable is given with values in Metabase

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related