I’m having a lot of trouble integrating field filters with my SQL queries, which leads me to believe it’s probably a case of user error. In any event, I attempted three separate field filters, and all three ran into issues as enumerated below. My apologies if these are repeat problems; I looked around and found issues which were similar, but not entirely the same:
My first field filter just didn’t show up. In my SQL code I wrote “WHERE {{filter_1}}” and none of the widget types were able to find it. In other words, I tried Time, ID, every type of Location, as well as Other Categories and none of them could detect “filter_1”.
My second field filter caused one of my queries to return “There was a problem displaying this chart”. Interestingly, it only had this problem in the dashboard while working fine in the individual question mode*. I’ll note that I was using this filter in both a query as well as its sub-query in order to ensure that their data matches up.
My third field filter only showed up as an ID while it was most decidedly not an ID. I’m not sure why this would be, it would seem to me that I should be able to select any filter as “Other Category” in any and all situations, regardless of whether or not it is an ID.
*Aside from the fact that I couldn’t filter on it, see my first suggestion below.
Any and all help with these three would be most appreciated, as I believe my goal in each case is within Metabase’s current capabilities. That being said, here are a few suggestions I have to improve field filters:
Most importantly, field filters should show up in an individual question’s parameters so that the user can test out their query–with the appropriate filters–and see if it’s working properly.
Filters should be able to be re-ordered in the dashboard; I recommend a drag and drop method.
If the width of the filters exceed the width of the dashboard page, local horizontal scrolling should be enabled. Currently, anything past the end of the page is simply cut off and inaccessible.
A somewhat larger tweaks, but filter categories should be able to refer to columns that were generated in my SQL query. Specifically, when grouping, I use aggregate functions to pick which specific date I wish to apply to the group; I would like to be able to then filter by those dates.
Relative timing should be using for both date ranges as well as specific dates. For example, I may want my specific date to default to today regardless of when I run the query. Alternatively, I may want my date range to default to ending today.
I’m sure there’s more information I should be providing, so please let me know what I’m missing. Sorry if this post is too long or too many issues all bundled together. Thanks to anyone who can help me with any of these issues, especially the first 3!
A note to the Metabase team: Overall really loving your product. Great work, thank you so much!
re: 1) what underlying field was filter_1 linked to?
re: 2) can you share the query for 2, or somehow replicate the error using the dataset?
re: 3) I’m not following what you mean by “showed up as an ID”? Do you mean the field is only shown as an option when using an ID filter widget?
Re: 1+5, strictly speaking, we’re distinguishing between a parameterization of a saved report, and adding a dimension to a SQL backed question so that it can be used in other places like a GUI build question. We’ll be adding other SQL tags to the template language, but I’d encourage you to think of number | date | string template variables as parameters for a given report, and the field filter as a way to create a rich object that can be embedded in other places and Do Smart Things. More is definitely coming on this front =)
filter_1 was linked to an underlying varchar field (I assume that's what you were asking)
sure, here it is:
SELECT
CASE
WHEN product_views.campaign_name IN
(SELECT DISTINCT email_campaigns.name AS campaign_names
FROM personalics_prod.email_campaigns email_campaigns
-- WHERE email_campaigns.user_id = {{user}})
WHERE {{user}})
THEN 'personalics'
ELSE 'customer'
END AS personalics_flag,
product_views.campaign_name AS campaign_name,
(CAST(COUNT (purchases.order_id) AS FLOAT) / COUNT (product_views.sku)) AS orders_over_clicks,
MODE() WITHIN GROUP (ORDER BY DATE(product_views.at)) AS most_clicks
FROM tracker.product_views product_views
LEFT OUTER JOIN tracker.purchases purchases
ON product_views.user_id = purchases.user_id AND product_views.random_id = purchases.random_id AND product_views.campaign_name = purchases.campaign_name
WHERE {{user}}
--WHERE product_views.user_id = {{user}}
AND length(product_views.campaign_name) > 0
[[AND product_views.email_campaigns = {{campaign}}]]
-- [[AND {{campaign}}]]
GROUP BY product_views.campaign_name
HAVING COUNT(product_views.sku) > 50
[[AND MODE() WITHIN GROUP (ORDER BY DATE(product_views.at)) >= {{start_date}}]]
[[AND MODE() WITHIN GROUP (ORDER BY DATE(product_views.at)) <= {{end_date}}]]
ORDER BY most_clicks
;
Only an ID filter widget would detect its presence (i.e. on the dashboard, after creating the widget, in the drop down options of applicable variables)
RE suggestion 2: Sure thing
RE 3: OK, that makes sense, thanks
RE 5: I understand. I was suggesting a hybrid. For example, in the query I pasted above, I may want {{end_date}} to default to 'today'; however, a relative date field does not pick up the variable {{end_date}}. (This relates back to my fourth suggestion, as using greater and less than is my workaround to ensure that I select the dates I want to select.)
Hello,
I am having the same issue with field filter and my sql questions. I am using an oracle database. I kept on getting ora-…missing keyword error. When that was resolved, in my dashboard i got the error “There was a problem displaying this chart” when i linked my filter to my dashboard. (I am using the date-range field filter. As i noticed it wasn’t selecting date-ranges for single date field filters).
Please help with resolving this issue.
(PS. Pardon the typos i am in a bit of a hurry)