Array of Strings as value for Multi-value parameter in SSRS 2012?

Question

Is it possible to assign the values of a multi-value parameter to arrays? I want to be able to pass the value (array of strings) to my query so that I can filter like so:

INNER JOIN blah E   
ON E.EventKey = F.EventKey  
AND E.EventCd in (@ParameterArray)

In my Parameter I want to do something like this:

Label | Value  
1 | ('12323467', '12398723')  
2 | ('12938412', '12343211', '91TS1231')  
etc.

The goal here is so the users of my report won't have to select each code individually, they can just select the bin it's a part of in the multi-value dropdown and the query/report will do the rest. I've tried to use split/join in different ways but haven't found a solution to this.

In addition, if a user selects multiple values from the dropdown I want to append the arrays. I'm unsure if this is possible in SSRS 2012.

Answer 1

Since you have multiple values in your prameter values you can't get away with a simple IN statement. But you can still get this to work.

First, Join the values so that they are all passed to the query. So in the Dataset properties > Parameters tab the expression would be something like:

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

Note that there is no "(0)" after the word "Value". If you double-click the parameter name it will add that for you, but that would only return the first selected value.

Next, update the condition in the query to work with this string of comma-separated values.

AND ',' + @ParameterArray + ',' like '%,' + E.EventCd + ',%'

The extra commas ensure that you do not ever have a partial match.

Also, just to clarify, the parameter values should not have any quotes, parentheses, or spaces. You just want to have a comma separated string of characters. For example the Value expression could be:

="12323467,12398723"