Pages

Wednesday, May 26, 2010

Pass Multivalue parameter to sub reports in SSRS

It has been quite a while since I have blogged. Obviously, cannot use that excuse that I was busy - probably not doing things which were not technical enough to be blogged.


Here I had a request from on of the teams that passing multi value parameters to the sub report is not possible and try to substantiate with this blog - http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/83d0c624-09a2-44c0-82ba-c2eb48571323. I was pretty sure this must be straight forward and hence checked this out and it may not be that simple.


The first step is to create the sub report and test it independent of the main report. Here ensure that the parameter has multi value enabled. All the other attributes of the parameter can be left to none since this will be a sub report and hence values will be sent from the main report. If you notice the parameter visibility has been set to visible. In theory, this is supposed to be internal but it keeps throwing up errors inconsistently when called from the main report if this is set to internal. Now, run this report and and ensure it works.

Now that we're done with the sub report lets work on the main report. Create the report and then add the sub report created as described above. Now, right click on the sub report within the report designer and choose sub report properties.


This should show up the dialogue shown below. Choose the parameters tab from the left menu. Add parameters and then choose the name of the parameter that you had created in the sub report. There seems to be a bug with SSRS that it does not show the name of the parameter in the drop down sometimes. Try saving the sub report and then click on the drop down. If this still does not work you can type the name of the parameter and then for the value choose the parameter that you had create in the main report
Now, the value from the parameter in the main report should get passed to the sub report. Another common mistake which should be avoided - in case you are using this multivalue parameter in the filters ensure you choose the 'IN' operate and not equal to operator.

Happy Reporting!

8 comments:

Anonymous said...

Looks like the way to successfully pass a multi-value parameter is by using an expression for the subreport parameter's value like so: =Split(Join(Parameters!MyParam.Value, ","), ",")

Chris said...

Yes! =Split(Join(Parameters!MyParam.Value, ","), ",") was the key to getting the subreport to work!

Anonymous said...

Cool!
Could not have done without this tip.

Musa

Anonymous said...

Yes..! it works. Thank you everyone :)

S. Kusen said...

The Split function worked like a charm. Thanks!

Anonymous said...

Thank You Very Much

Unknown said...

I read your blog.I thought it was great.. Hope you have a great day. God bless.

Rica
www.imarksweb.org

Oz said...

Thank you! I four hours today searching the web for this answer.