topic Re: How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL in Warehousing & Analytics

How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL

Linglin — Thu, 14 Mar 2024 18:43:54 GMT

select {{user_defined_variable}} as my_var,

count(*) as cnt

from my_table

where {{user_defined_variable}} = {{value}}

for user_defined_variable, I use query based dropdown list to get a column_name I'd like in the selection.

for user_defined_value, I can only use text to pass one value

Since the value selection is based on my selection of user_defined_variable, I can't use dropdown list to predefine value. I tried to nest variable in the query used for user_defined_variable. It doesn't work.

I also tried {{user_defined_variable}} in ({{value}}), doesn't work as well.

I wonder if anyone knows a solution. thanks!

Re: How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL

Linglin — Mon, 18 Mar 2024 17:00:55 GMT

Thank you very much for your reply. I'm not sure if my databricks environment is MySQL. SET statement does't work for me. (I tried both dbx notebooks(SQL enviroment, I think it's spark sql) and in sql editor). In SQL editor, it shows PowerBI on the right corner and it supports dashboard rendering.

For me {{}} works well as variable setter. It worksWHERE {{my_var}} = {{some_value}}/where {{my_var}} in ({{some_value}}) when I pass in one value for {{some_value}} in both statements, but returns error message when I try to return multiple values. I wonder in your statement, if it works for multiple values.

Re: How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL

YuliyanBogdanov — Thu, 28 Mar 2024 16:00:09 GMT

It seems to work, try and create all your variables as a Drop Down List, with single value allowed and only the filter i.e. msp in my example as a multiple values allowed, no quotations in the quotation drop down.

Let me know if that helped.