sqlreporting-servicesparameterssubreportmultivalue

Passing multivalue parameter to a subreport


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


Solution

  • 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