1

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.

lezs76
  • 47
  • 7
  • does it work for a single value parameter? How does your query handle the multiple parameters? – Harry Nov 05 '19 at 21:56
  • Yes works fine as a single value parameter so don't understand why it doesn't work with multi. – lezs76 Nov 05 '19 at 21:58
  • 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 Nov 05 '19 at 22:10
  • 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 Nov 05 '19 at 22:17
  • 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 Nov 05 '19 at 22:26
  • have a look at this https://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services – Harry Nov 05 '19 at 22:27
  • Oh sorry there is an error message - Cannot add multi value query parameter '?' for dataset 'Dataset1' because it is not supported by the data extension. In the dataset I replaced the ? to the name of the multi select parameter and that also didn't work. The other parameters we use on the report have '?' in the dataset parameter options but they are single select. – lezs76 Nov 05 '19 at 22:33
  • please post your actual query.. \ – Harry Nov 05 '19 at 22:39

1 Answers1

2

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.

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • Late yesterday there was a change of requirements so no longer need the multi select. Saying that I'm going to try it for learning in case there is a next time. – lezs76 Nov 06 '19 at 17:43
  • It's good to know both for you and anyone else who stumbles on this :) – StevenWhite Nov 06 '19 at 18:28