SSRS 2008 - Subreport for each value from multi-valued parameter

Question

using SSRS 2008 R2 and wondering, if is possible to generate sub-report for each value selected in multi-valued parameter?

Lets imagine you have multi-valued parameter "parameterA" with four values selected (Value1, Value2, Value3 and Value4). I'd like to generate sub-report for each of these values (passing the value as single parameter). Sure, I can change the procedure to handle multi-valued parameter, but because of performance and caching I really don't want to.

Thanks for any advice.

Answer 1

To do this you would have to put the subreport into a list or tablix. As far as I know there's no easy way to have that list or tablix iterate over the values in a multi-valued parameter. It will only accept a dataset.

So the only workaround I can think of involves splitting the parameter into a set of rows in a dataset, which is possible but not trivial with SQL. However, if the available values for the parameter come from a dataset the situation would improve: you can just hook the tablix/list to your dataset and filter items that aren't selected in the parameter.

Edit: I've found a slightly hackish solution to expanding a multi-valued parameter into a dataset, by building the dataset query as an expression. Assuming a parameter @MultiParamX this expression will create a query that outputs all selected values in one column:

="SELECT '" 
&
 Join(Parameters!MultiParamX.Value, "' MyParam UNION ALL SELECT '")
&
"' MyParam"

This may generate a query such as the following (reformatted for readability):

SELECT 'A' MyParam
UNION ALL
SELECT 'B' MyParam
UNION ALL
SELECT 'C' MyParam
-- Etc. for all selected values

This will generate a result set such as:

┌─────────┐
│ MyParam │
├─────────┤
│ A       │
│ B       │
│ C       │
│ Etc.    │
└─────────┘