Pre-populating possible text variable values to display as a dropdown

Get Help
1

What I’m trying to achieve is having a dropdown with values like day/week/month/year for the following part of my custom SQL query:

SELECT 
  date_trunc({{Interval}}, created_at)
  ...
GROUP BY 1

I can set the variable to be text, but then I need to enter those values by hand. Would it be possible to somehow convert this to a dropdown with a set of pre-populated values?

2

Hi @pawel
You would have to create a table with the values and use a Field Filter on that table, joined with the query.
There’s a request open for what you’re asking for:
https://github.com/metabase/metabase/issues/5245 - upvote by clicking :+1: on the first post

1 Like
3

Lucky you. If I try to run a similar query with {{my_variable}}, it says must appear in the GROUP BY clause or be used in an aggregate function.
If I directly enter ‘myvalue’, it works.

4

Actually found the specific issue for this context:
https://github.com/metabase/metabase/issues/9875 - upvote by clicking :+1: on the first post

@abhilash2908 Which database are you querying? Please post your query, there’s workarounds for most databases.

5

It’s Vertica DB.

6

@abhilash2908 Well without seeing the query and since I don’t have a Vertica instance to test with, then I would guess something like this:

[[GROUP BY date_trunc({{Interval}}, created_at)]]

Or search the forum for “complex default value”, since that might be an option as well:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#default-value-in-the-query

7 
SELECT date_trunc({{revid}}, to_timestamp(s_{{clvidientId}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))  as cdate, 
sum(s_{{vid}}.engagementDetails_{{bid}}.re_int) AS "sum"
FROM s_{{vid}}.engagementDetails_{{bid}} 
WHERE ((cid = {{vid}}) [[AND t0.mid > {{mid}}]] AND (cdate BETWEEN {{startDate}} AND {{endDate}}))
GROUP BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))
ORDER BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))

[Vertica]VJDBC ERROR: Column “engagementDetails_2.cdate” must appear in the GROUP BY clause or be used in an aggregate function

8

If I replace {{revid}} with 'month', it works. But not otherwise.

9

I figured out the solution.
Replaced last 2 lines with

GROUP BY cdate
ORDER BY cdate
1 Like