ssrs 2008 passing a parameter list to a subreport

Question

I have a report in which the user can select multiple items in a list for one of the parameters. Clicking on the report opens a sub-report, with parameters being passed to the sub-report. If only one item is selected, then this works fine. If multiple items are selected, then only one item in the parameter list is passed to the sub-report.

The parent report works fine, the sub-report only shows the first item. I am using SSRS 2008 R2. How can I get the sub-report to accept all the items for the passed parameter?

Solution 1

A few things to check:

1. Make sure that the parameter in the subreport is set to allow multiple values.
2. Make sure you are passing the parameter from the main report to the subreport as [@paramName] and not "=Parameters!ParamName.Value(0)" as this indicates to retrieve only the first value.
3. Set the subreport parameter to visible and ensure manually selecting/inputting multiple values returns appropriate results.
4. If all else fails, try using a formula like this =Split(join(Parameters!ParamName.Value,","),",") which creates a comma separated list of the parameter values and then splits it out into an array.

Solution 2

I use a workaround in sql for those, since it's a pain to use multiple parameters in ssrs. As mmarie says, start by concatenating the multiple values in the parameter (=Split(join(Parameters!ParamName.Value,","),",")) and then check the items in the subreport with a string comparison. It usually gives something like WHERE '%,'+subreport_item+',%' LIKE ','+parent_report_multiple_parameters+',' It's a quick dirty solution...

Comments

[Matthew Martin]

Thank you, very helpful.