CODEWITHSUNDEEP
postgresqlreporting-services
lezs76
lezs76

Reputation: 59

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

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.

Upvotes: 1

Views: 2197

Answers (1)

StevenWhite
StevenWhite

Reputation: 6024

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.

Upvotes: 3

Related Questions