Usage of field filters

Can we use field filters in CASE() function and WHERE clause in the same query as I am getting error of using field filters in where clause?

Hi @agrawalaastha98
Post “Diagnostic Info” from Admin > Troubleshooting, and the database you are querying, and the query.
Depending on what you are returning, the database you are querying and how you write your query, then it might be possible.
Field Filter variables are handled by Metabase and inserts SQL. It’s not the same a the other filter types.
For reference: https://www.metabase.com/blog/field-filters/index.html

Hi @flamber,
This is the Diagnostic info:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Linux x86_64”,
“userAgent”: “Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:78.0) Gecko/20100101 Firefox/78.0”,
“vendor”: “”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.5+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.5”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.5+10”,
“os.name”: “Linux”,
“os.version”: “5.4.0-1029-aws”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“redshift”,
“athena”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-04-10”,
“tag”: “v0.35.2”,
“branch”: “release-0.35.x”,
“hash”: “f3b2857”
},
“settings”: {
“report-timezone”: “Asia/Calcutta”
}
}
}
I am querying Redshift using Native query in Metabase.
The query is:
select store_key, revised_region, store_name, DIVISION_NAME, Department_name, Family_name, Class_name, Subclass_name, Sales_LY, Sales_LM, Sales_TY, (CASE WHEN Sales_LY > 0 THEN (((Sales_TY/Sales_LY)-1)*100) END ) as Sales_Growth_per_over_LY, (CASE WHEN Sales_LM > 0 THEN (((Sales_TY/Sales_LM)-1)*100) END ) as Sales_Growth_per_over_LM from (select location_dimension.store_key, location_dimension.revised_region, location_dimension.name as store_name, item_dimension.division_name as DIVISION_NAME, item_dimension.department_name as Department_name, item_dimension.family_name as Family_name, item_dimension.class_name as Class_name, item_dimension.subclass_name as Subclass_name, sum(CASE WHEN sale_fact.date_key between {{TY_from_date}} and {{TY_today}} THEN sale_fact.net_sale ELSE 0.0 END) Sales_TY, sum(CASE WHEN sale_fact.date_key between {{LY_from_date}} and {{LY_to_date}} THEN sale_fact.net_sale ELSE 0.0 END) Sales_LY, sum(CASE WHEN sale_fact.date_key between {{LM_from_date}} and {{LM_to_date}} THEN sale_fact.net_sale ELSE 0.0 END) Sales_LM, from sale_fact left join location_dimension on location_dimension.store_key = sale_fact.store_key left join item_dimension on item_dimension.item_key = sale_fact.item_key where {{format}} and {{store_key}} group by location_dimension.store_key, location_dimension.revised_region, location_dimension.name, item_dimension.division_name, item_dimension.department_name, item_dimension.family_name, item_dimension.class_name, item_dimension.subclass_name )

Also, I am using store_key as a field filter but when I click the filter no values are shown to me. I have to manually write the values. How to fix this issue?

@agrawalaastha98

I don’t understand. Are you getting an error or are you just not seeing a dropdown list on the filter widget?
If error, then please post the error.
If missing dropdown, then make sure you have read the article I linked to, which describes in more details the need to set the Field Type in Data Model to one of the ones listed in the documentation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

Latest release is 0.37.2.
And you should migrate away from H2:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

Hi @flamber,
At first I was getting the error, then I looked into it and saw I was using table alias. That’s why field filter was not working with WHERE clause.

Below is the specifications for the store_key field filter:


Is something wrong in this?

@agrawalaastha98 Then post the error.

Hi @flamber,
I have created a view using SQL with field filters.I want the feature that if no value is selected in field filter then that column should not be used for grouping of measure to enable drill down support in the view. Is this possible?

@agrawalaastha98 That is currently not supported:
https://github.com/metabase/metabase/issues/13148 - upvote by clicking :+1: on the first post

Hi @flamber,
Is it possible to connect questions in Metabase internally? Like in first question I want to show total sales of stores. Then to drill down at specific item category it will connect to another question? Does Metabase support this kind of drill down?

@agrawalaastha98 Yes, with Click Behavior since 0.37:
https://www.metabase.com/blog/custom-destinations/index.html

@flamber, The blog you have suggested connect questions more on a dashboard level. I am not showcasing my question on dashboard. I am directly accessing the question from Our Analytics. Will click behaviour work for SQL queries?
Also, does Metabase support ROLLUP function in SQL queries?

@agrawalaastha98 Click Behavior is currently only available from the dashboard. You can use any SQL functions in SQL.

@flamber, I was using ROLLUP function in Metabase SQL editor and the error was raised no function found. What to do now?


2) Also, I am having issues in displaying a tabular report with 40 columns on dashboard. error displaying chart raises. No solution is there for it. What to do?

@agrawalaastha98
But you are querying H2 (Sample Dataset) - that database does not have a rollup function.

Have you upgraded to 0.37.3? I cannot reproduce the 40 columns problem. I have just added a table with 57 columns. Post errors from the log and browser console.

@flamber,
I am using Metabase- Athena driver from the link given below which has not been upgraded to 0.37.3 version.

@agrawalaastha98 Correct, but it is available for 0.36

@flamber,
I have not upgraded Metabase version as I cannot lose my data inside database at this point of time.

@agrawalaastha98 Metabase does not lose data during upgrades - unless you are using H2 in a container that isn’t persistent, but that’s why I wrote that you need to migrate away from H2.

@flamber,
Will do it. Thanks.