Having a dashboard filter that is not obliged to be used

I have a dash board filter on the merchant names. I want to connect it to a sql query with postegresql. Have a condition that if i don't select a specific merchant name it outputs all of them and when i select a merchant name it filters. Is this possible?

Hi @louisc
If you don't set a filter widget value, then all results are shown by default.
https://www.metabase.com/docs/latest/users-guide/08-dashboard-filters.html

select *
from status_amount_cms
where merchant_name = '{{merchant}}'

I put it like that but I have an error

@louisc Use Optional Clauses: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses

select *
from status_amount_cms
where
[[merchant_name = '{{merchant}}' ]]

I did this but still got an error

'The column index is out of range: 7, number of columns: 6.'

@louisc Because everything within the optional clauses will not be created, which means you just have an empty WHERE-clause, which is invalid syntax.
Either:

[[where merchant_name = '{{merchant}}' ]]

Or:

where true
[[and merchant_name = '{{merchant}}' ]]

select *
from status_amount_cms
[[where merchant_name = '{{merchant}}' ]]

I did like that and when I dont input anything in the filter it outputs the default which is good but when i want to filter it has again an error.

'The column index is out of range: 7, number of columns: 6.'

@louisc You should not enclose variables with quotes, Metabase does this. Please read the entire documentation: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

select *
from status_amount_cms
where true
[[merchant_name = {{merchant}} ]]

yes sorry I had forgotten to take them out but still have an error when i input in the filter.

ERROR: syntax error at or near "merchant_name" Position: 4336

Here is my full code if it helps

with desired_cohort as (
select
id
from orders_order
where 1=1
and is_order_authorized = 'true'
and is_order_using_elyn = 'true'
and order_date >={{start_range_date}}
and order_date <={{end_range_date}}::date + interval '1 day'
)

, status_desired_cohort as (
select distinct
order_id
, status
, created_at as start_date_status
, coalesce(lead(created_at,1)OVER (PARTITION BY order_id ORDER BY created_at), current_date) as end_date_status
from orders_orderstatus
join desired_cohort on desired_cohort.id=orders_orderstatus.order_id)

,status_desired_cohort_status as (
select *
from status_desired_cohort
where 1=1
and start_date_status <={{selected_date}}::date + interval '22 hours'
and end_date_status >={{selected_date}}

)

,status_desired_cohort_status_filter as (
select *
from (
select order_id,
status,
start_date_status,
end_date_status,
max(start_date_status) over (partition by order_id) as max_status
from status_desired_cohort_status
) t
where start_date_status = max_status)

,status_desired_cohort_status_filter_tenant as (
SELECT distinct
status_desired_cohort_status_filter.*,
orders_orderstatus.tenant_id
FROM
status_desired_cohort_status_filter
inner JOIN
orders_orderstatus ON status_desired_cohort_status_filter.order_id = orders_orderstatus.order_id
)

,status_desired_cohort_status_filter_tenant_name as (
SELECT distinct
status_desired_cohort_status_filter_tenant.*,
tenants_tenant.merchant_name
FROM
status_desired_cohort_status_filter_tenant
inner JOIN
tenants_tenant ON status_desired_cohort_status_filter_tenant.tenant_id = tenants_tenant.id

)

,status_desired_cohort_status_filter_tenant_name_total as (
SELECT distinct
status_desired_cohort_status_filter_tenant_name.*,
orders_orderamount.amount_total_cart,
orders_orderamount.created_at,
orders_orderamount.updated_at
FROM
status_desired_cohort_status_filter_tenant_name
inner JOIN
orders_orderamount ON status_desired_cohort_status_filter_tenant_name.order_id = orders_orderamount.order_id

)

,status_desired_cohort_status_time as (
select distinct status_desired_cohort_status_filter_tenant_name_total.*,
status_desired_cohort_status_filter_tenant_name_total.created_at as start_date_status_cart
, coalesce(lead(status_desired_cohort_status_filter_tenant_name_total.created_at,1)OVER (PARTITION BY order_id ORDER BY status_desired_cohort_status_filter_tenant_name_total.created_at), current_date) as end_date_status_cart
from status_desired_cohort_status_filter_tenant_name_total

)

,status_desired_cohort_status_vrai as (
select *
from status_desired_cohort_status_time
where 1=1
and start_date_status_cart <={{selected_date}}::date + interval '1 day'
and end_date_status_cart >={{selected_date}}
)

,orders_orderamount_total_cart as (
select *
from (
select *,
max(start_date_status_cart) over (partition by order_id) as max_date
from status_desired_cohort_status_vrai
) t
where start_date_status_cart = max_date)

,status_with_max_captured as (
SELECT distinct
orders_orderamount_total_cart.*,
orders_orderamount.amount_captured,
orders_orderamount.amount_previously_captured,
orders_orderamount.id
FROM
orders_orderamount_total_cart
inner JOIN
orders_orderamount ON orders_orderamount_total_cart.order_id = orders_orderamount.order_id

)

,status_with_max_captured_amount as (
SELECT
status_with_max_captured.*,
last_value(status_with_max_captured.amount_captured)
over (
partition by order_id

   )

FROM
status_with_max_captured
)

,max__amount_id as (
select *
from (
select *,
max(id) over (partition by order_id) as max_order_amount_id
from status_with_max_captured_amount
) t
where id = max_order_amount_id)

,max__amount_id_addition as (
select *,
amount_captured + amount_previously_captured as total_amount_captured
from max__amount_id
)

,status_amount_cms as (
SELECT distinct
max__amount_id_addition.*,
orders_order.initial_amount_total_pulled_from_cms
FROM
max__amount_id_addition
inner JOIN
orders_order ON max__amount_id_addition.order_id = orders_order.id

)

,merchant_filter as (
select *
from status_amount_cms
where true
[[merchant_name = {{merchant}} ]]
)

select *
from merchant_filter
where (status = 'closed'or status = 'closed_by_admin')

@flamber do you think its solvable?

@louisc Here's the trick. Replace {{variable}} with whatever it is supposed to represent (eg. 'text') and remove the optional clauses. If the query works, then you know it's correct.
Please read:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
https://www.metabase.com/learn/sql-questions/sql-variables.html
https://www.metabase.com/learn/sql-questions/field-filters.html

They will teach you a lot about how variables works.

1 Like

i get this error: "ERROR: syntax error at or near "merchant_name" Position: 4336"
when i run my code.

select *
from status_amount_cms
where true
[[ merchant_name = {{merchant}} ]]

It works when i dont input anything the filter works as default but when i select a merchant i have the error

Is where true merchant_name = 'some value' valid SQL? You are missing AND

where true
[[ AND merchant_name = {{merchant}} ]]

It works perfect when i input directly a text( so a merchant name directly) so the problem comes from the variable.
I have done

select *
from status_amount_cms
where true
[[ AND merchant_name = {{merchant}} ]]

and when I select the variable merchant name it brings up an error :

ERROR: syntax error at or near "=" Position: 4399

@louisc I have absolutely no idea which variable type you are using, so post a screenshot showing that.

It's a field filter

@louisc Pretty please; read all the articles I've linked several times previously: Having a dashboard filter that is not obliged to be used

@flamber thanks for your help and I have tried as a text filter and works fine but only for field filters. I have tried everything else but still an error message.

select *
from status_amount_cms
where true
[[ AND merchant_name = {{merchant}} ]]

i get: ERROR: syntax error at or near "=" Position: 4399

@louisc You are not reading the pages:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#field-filter-syntax
https://www.metabase.com/learn/sql-questions/field-filters.html#omit-the-direct-assignment-in-the-where-clause

Field Filter are not using the same syntax as simple variables.
[[ AND {{merchant}} ]]

1 Like