SSRS - Concatenate Multiple Parameter Values in a Text Box

Question

I have an SSRS report with several parameters the user selects at runtime. One of the parameters allows multi-select. I'm using the values to populate a text box and am having a problem using the parameter values when multiples are selected. Below is when the user selects one value and this works:

=Switch(Parameters!ID.Value(0) = 5, "Location 1", Parameters!ID.Value(0) = 9, "Location 2") & " Status Report"

I have another case, though. Since it's multi-select, if the parameter carries values of 5 and 9, I want to have have it say "Location 1 and Location 2" & " Status Report"

I'm not sure how to accomplish that.

I tried:

=Switch(Parameters!ID.Value(0) = 5, "Location 1", Parameters!ID.Value(0) = 9, "Location 2", **Parameters!ID.Value(0) = 5 AndAlso Parameters!ID.Value(0) = 9, "Location 1 and Location 2"**) & " Status Report"

Thoughts?

Answer 1

What I do when trying to find a value in a multi-value parameter is JOIN all the values together into a string.

"|" & JOIN(Parameters!ID.Value,"|") & "|"

If parameter values 1, 2, and 3 are selected, the string would be |1|2|3|. The pipes are added so 1 isn't found when 11 is selected.

If you want to check for a certain value, use the INSTR function with your value enclosed in Pipes. INSTR will return 0 if not found and the character position if it is found.

This will search for a 5 from the parameter string above:

INSTR("|" & JOIN(Parameters!ID.Value,"|") & "|", "|5|")

So your expression would end up like

=Switch(INSTR("|" & JOIN(Parameters!ID.Value,"|") & "|", "|5|") > 0 AndAlso INSTR("|" & JOIN(Parameters!ID.Value,"|") & "|", "|9|") > 0, "Location 1 and Location 2", 
INSTR("|" & JOIN(Parameters!ID.Value,"|") & "|", "|5|") > 0, "Location 1", 
INSTR("|" & JOIN(Parameters!ID.Value,"|") & "|", "|9|") > 0, "Location 2", 
1 = 1, "Locations " & JOIN(Parameters!ID.Value,", ")) & 
" Status Report"

I moved your criteria with AND to the first position because a SWITCH will choose the first one and never get to your 3rd one unless neither 5 nor 9 was selected. I also added the 1 = 1 as a fallout if neither 5 not 9 are selected.

Answer 2

Assuming your ID parameter either

1. Take its available values from a query or
2. Has its available values set manually

You should therefore have two properties available in your parameter. Its value (the bit that is actually passed to queries/filters ect) and a label (the bit you normally see as a user)

Lets say then that you have 3 IDs in your parameter list like

Value    Label
5        Location 1
9        Location 2
10       Some other location

Then all you need to do is reference the labels of the parameters collections in your expression like this.

= JOIN(Parmeters!ID.Label, " and ") & " Status Report."

That's it.

Answer 3

I have used the following and it worked for me:

=CStr(Parameters!Month.Label) &", " &CStr(Parameters!Year.Label)

You need to convert numbers to strings using CStr, so that you can add other characters like comma.