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! :)

Bhakti

Answer 1

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

Bhakti

Answer 2

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)

Daciadacie

You are right, this should "just work", but it didn't. So, I deleted the report and created it from zero again, it worked. Apparently I must have forgotten something in the middle. Thanks anyway for your answer – Bhakti

Thanks, this helped me get mine working. Important: "Parameter in the subreport is set up as multivalue, and use the exact same query as in the parent report to provide Available Values.". Important: "In subreport properties on the parent report, the parameter's value should read [@MyParamName] not <<Expr>>". These tips did it for me. – Canady

I did what you proposed(check if everything is implemented as in the main report) and found the problem. I use Report Builder for creating reports and in my data source, instead of coding, I went to the Filters tab from DataSet Properties and added there my field, the operation I want to be used (IN), and the list of values which is the multiple value parameter. Thanks for the suggestions. – Barbi

Answer 3

I think I know what you did, as I was drawn here by having the same problem. The subreports were setup fine, but when entering the parameter binding in the parent report, the Value drop down did not offer my parameter, so I used expression builder to select it. This left the grey << Expr >> marker in the value and would only work when I had only one value selected from the list. When I replaced this with [@MyParam] it worked fine regardless of the number of values selected. When I had a look at the value expression builder had created a bit closer it had =Parameters!MyParam.Value(0). Removing the (0) also fixes it.

Haws