Passing multivalue parameter to a subreport

sql reporting-services parameters subreport multivalue
monican picture monican · Dec 5, 2012 · Viewed 23.1k times · Source

I'm having a problem when working with multivalue parameters between reports.

I have a main report in which I have defined a multivalue paramer, which I use to run a SQL query to populate its dataset. The parameter is used in the WHERE clause in the following way:

WHERE values IN (@parameter)

It's working fine and it retreives the expected data.

Then this main report passes this parameter to a subreport. The parameter is also defined as multivalue in the subreport and, as far as I can see in the parameter's dropdownlist it receives the values in the right way. Something like this: A, B, C

The thing is that the query that populates the subreport's dataset returns nothing. It also has a WHERE clause defined as in the main report (which is already working)

WHERE values IN (@parameter)

If I run the query manually, hardcoding the values to something like this:

WHERE values IN ('A', 'B', 'C')

it works, but when I try to use the parameter it doesn't. So, somehow it's losing the format or the values in the way.

I tried this solution in the subreport's dataset definition, which was proposed in another thread:

 =join(Parameters!<your param name>.Value,",")

But it doesn't work for me, the dataset is still empty.

Any ideas about what I'm missing?

Thanks! :)

Answer

Jamie F picture Jamie F · Dec 5, 2012

This should "just work." Make sure that the Parameter in the subreport is set up as multivalue, and I usually use the exact same query as in the parent report to provide "Available Values."

Check that you are passing the entire parameter to the subreport: In subreport properties on the parent report, the parameter's value should read [@MyParamName] not <<Expr>>. If it reads as the latter, edit the expression and make sure it doesn't have a (0) at the end. but =Parameters!MyParamName.Value is correct, not =Parameters!MyParamName.Value(0)

Related questions

Passing multiple values for a single parameter in Reporting Services

I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine. The …

Read More
SSRS Grey out Parameter based on result from other Parameter

I wondered if it was possible to grey out a parameter based on the the result from another parameter? In this case, if specify date is set to 'No' then Start Date and End Date should be greyed out. As …

Read More
SSRS - How to add a default value to a parameter with Available Values from a query?

I have an SSRS report which contains a parameter ID, which will display sales totals for a person with selected ID. I get the options for the parameter by selecting "Get values from a query" under the Parameter's "Available Values" …

Read More