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