topic Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC in Data Engineering

Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

DataGirl — Fri, 09 Sep 2022 00:41:51 GMT

Hi All,

I'm wondering if anyone has had any luck setting up multi valued parameters on SSRS using ODBC connection to Databricks? I'm getting "Cannot add multi value query parameter" error everytime I change my parameter to multi value.

In the query section I've tried:

SELECT * 
 
FROM tmp_result
 
WHERE (profileArea IN (?))

I've also tried

SELECT * 
 
FROM tmp_result
 
WHERE (profileArea IN ('" & Join(Parameters!ProfileArea.Value, "', '") & "'))

As well as

SELECT * 
 
FROM tmp_result
 
WHERE (profileArea IN ("& Join(Parameters!ProfileArea.Value,",") &"))

and entering the below into the parameter expression.

=Split(Join(Parameters!ProfileArea.Value, " , "))

I've also tried

=Join(Parameters!ProfileArea.Value, " , ")

*Connection must be with ODBC, I cannot import connection into Power BI and use a power BI dataset instead.

**ODBC connection doesn't support @parameter syntax in the query section, I've tried.

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

-werners- — Thu, 12 Jan 2023 15:59:52 GMT

@Reen Zidaj have you pulled this off yet?

I will soon be in the same boat.

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

-werners- — Thu, 09 Feb 2023 12:50:22 GMT

Ok, so I managed to pull it off.

Basically you were on the right track, but there is some extra magic to it.

So instead of entering your query in the Query field (in Dataset Properties), you use the formula builder (click on the fx button next to the query field).

In the formula builder you enter your query as you already mentioned:

= "SELECT *

FROM tmp_result

WHERE (profileArea IN ('" & Join(Parameters!ProfileArea.Value, "', '") & "'))"

The trick is to put the whole query into double quotes and using the formula builder.

When you exit the formula builder, the query field will show "<<Expr>>", so you can't see the actual query.

But this approach works for me.

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

AEW — Thu, 13 Apr 2023 20:31:28 GMT

Hi there! I found this solution and it has helped a lot. I have a question -- how do you populate the "Fields" section of the dataset properties if you're using the formula builder? If I type the query directly into the query text field, it will auto-input the fields. Is there a good workaround?

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

-werners- — Fri, 14 Apr 2023 07:01:31 GMT

The formula builder itself also has the possibility to define fields (with an expression). I think that is the way to go, but I am no SSRS expert by far.

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

TechMG — Fri, 08 Dec 2023 14:56:33 GMT

Hello,

I am facing similar kind of issue. I am working on Power BI paginated report and databricks is my source for the report. I was trying to pass the parameter by passing the query in expression builder as mentioned above. However, I have end up with below error.

ERROR [42601] [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [PARSE_SYNTAX_ERROR] org.apache.spark.sql.catalyst.parser.ParseException:
[PARSE_SYNTAX_ERROR] Syntax error at or near 'and'.(line 1, pos 84)

My Query:

== SQL ==
="select * from table where Category='ABC' and (FiscalYear=Parameters!Year.Value)and (Month=Parameters!Month.Value) and (Program=Parameters!Program.Value)"

Thanks,

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

ssrsnat — Mon, 31 Mar 2025 16:14:50 GMT

Hi I am working on having SSRS reports access Databricks and facing similar challenges. I see you tried this back in 2022. Can you please advice the approach to handle the multi value parameters? Thanks Sam

Re: Multi value parameter on Power BI Paginated / SSRS connected to databricks using ODBC

kashti123 — Fri, 07 Nov 2025 09:58:44 GMT

Hi I am also trying to set multi value parameters using the dynamic sql expression. However, the report gives error that multi value parameters are not supported by the data extension. Any help on this would be highly appreciated. Thanks , Drishti