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

[DataGirl]

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.

[-werners-]

@Reen Zidaj​ have you pulled this off yet?

I will soon be in the same boat.

[-werners-]

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.

[AEW]

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?

[-werners-]

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.

[TechMG]

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,