SSRS multi value parameter - can't get it to work

Question

First off this is my first attempt at a multi select. I've done a lot of searching but I can't find the answer that works for me.

I have a postgresql query which has bg.revision_key in (_revision_key) which holds the parameter. A side note, we've named all our parameters in the queries with the underscore and they all work, they are single select in SSRS.

In my SSRS report I have a parameter called Revision Key Segment which is the multi select parameter. I've ticked Allow multi value and in Available Values I have value field pointing to revision_key in the dataset.

In my dataset parameter options I have Parameter Value [@revision_key]

In my shared dataset I also have my parameter set to Allow multi value.

For some reason I can't seem to get the multi select to work so I must be missing something somewhere but I've ran out of ideas.

does it work for a single value parameter? How does your query handle the multiple parameters? — Harry
Yes works fine as a single value parameter so don't understand why it doesn't work with multi. — lezs76
How do you handle the multiple values supplied by the parameter in your query? Doesn't work = No results returned or is there an error message? — Harry
If I put in the query bg.revision_key in (1, 2, 3, 4) for example I get the correct results. I must not have set the parameter to pass multi values correctly. — lezs76
of course hard coded values are going to work.. for the report to work.. you pass in the parameter.. how do you do that? bg.revision_key in (@revision_key)??? — Harry

StevenWhite StevenWhite · Accepted Answer · 2019-11-05T23:02:29

Unlike with SQL Server, when you connect to a database using an ODBC connection, the parameter support is different. You cannot use named parameters and instead have to use the ? syntax.

In order to accommodate multiple values you can concatenate them into a single string and use a like statement to search them. However, this is inefficient. Another approach is to use a function to split the values into an in-line table.

In PostgreSQL you can use an expression like this:

inner join (select CAST(regexp_split_to_table(?, ',') AS int) as filter) as my on my.filter = key_column

Then in the dataset properties, under the parameters tab, use an expression like this to concatenate the values:

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

In other words, the report is concatenating the values into a comma-separated list. The database is splitting them into a table of integers then inner joining on the values.

SSRS multi value parameter - can't get it to work

1 Answers