Passing multivalue parameter to a subreport

Question

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 1

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)

Answer 2

Just created the report from scratch again and it worked. I must have forgotten something in the middle.

Anyway, just in case somebody needs it, the two parameters, the one in the main report and the one in the subreport , must be defined as multivalue. Then in your query you should use IN in your WHERE clase, something like this:

WHERE field IN (@parameter)

And nothing else is needed. I didn't need to do the following:

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

It just worked for me