Monday, October 15, 2007

Passing SSRS Report Parameters in an URL

There is a lot of discussion on the forums about passing parameters to an SSRS report using an URL however one of the items that you rarely read about is the passing of multi valued parameters in an URL.

To do this, just pass the name/value pair, separated by the ampersand.

For Example: http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

As long as the parameter Parm1 is set to multi value, and it contains those three values (VALUE1, VALUE2, VALUE3) all three will be selected.

The only exception to this rule is if one of the name/value pairs is a null (Parm1:IsNull=True)
That causes SSRS to ignore the rest of the values passed.

Remember to pass the Value, and not the Label.

Peace

18 comments:

Anonymous said...

Thank you so much! This is exactly what I was looking for.

Alain Theriault said...

This is great. You're the only place I found that had this solution.

cdejager said...

Likewise, I searched for ever and only found the answer here. Thanks!

Sam Schutte said...

Awesome man! Your post was the only place I found the solution to this!

Pete Brown said...

I just spent the weekend trying to write a custom report viewing page then came across this. Solved all my issues. Thanks!

kali-charan-tripathi said...

How to pass Multivalued parameters(Dynamically) through URL in SSRS 2005

Hi All,

I have main matrix report and I want to navigate my sub report from main report by
Jump To URL:(Using below JavaScript function) method.

="javascript:void(window.open('http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fKonsolidata_Data_Exporting_Project%2fEXPORT_REPORT_TEST&rs:Command=Render&RP_cntry="+Fields!STD_CTRY_NM.Value+"&RP_cll_typ_l1="+Join(Parameters!RP_cll_typ_l1.Value,",")+"'))"

It is ok for the Single valued but giving exception for the multivalued

Like
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the data set DS_GRID_DATA. (rsErrorReadingNextDataRow)
Conversion failed when converting the nvarchar value '1,2,3,4' to data type int.
Basically I have defined Parameters!RP_cll_typ_l1 as multivalued into my subreport as per ssrs multivalued parameter passing method.
The value is going on sub report as '1,2,3,4' (not understandable by data set)
It should be like as '1’,’2’,’3’,’4' or 1,2,3,4
How can I resolve this please help if any have solution?

Thanks

Kali Charan Tripathi(India)

tripathi_soft@yahoo.co.in

kalicharan.tripathi@in.schneider-electric.com

Anonymous said...

Please read the article above. That is what it is explaining.

To pass multi-valued parameters, include the name/value pair for each value. ie RP_cll_typ_l1=1&RP_cll_typ_l1=2&RP_cll_typ_l1=3

Anonymous said...

this was a great post and I completed it correctly but the report fail to load though I can manually edit the parameter to get it to work. http://www.osmanager4.com/CustomReports/Viewer.aspx?p=%2fdigitalcards%2fxa_inst_p&n=print+cards+Report&sessionid=36da1323-1698-44e0-9a84-342f2f84a21c&para1=59119&para1=59120
give me 59119,59120 but if you just edit it to 59119, 59120 it works. its like its missing the line feed. Thoughts?

Kali Tripathi said...
This comment has been removed by a blog administrator.
Anonymous said...

This is very dumb method of passing multi values parameter, what if i want to pass 1000 values do i have enter 1000 values in the URL link and there is a character limit in the URL....and if the datasource is MDX it will take & as new parameter from the value([Division].[Name].&[US]) .

Bob Pearson said...

While this is true, it's not the only way to pass parameters. You could always use the web services to retrieve a report. Then you do not have the URL limitation.

Anonymous said...

I am using ssrs 2008. I am having a group expression where I have one multivalued parameter. I just want to know how can I use a multivalued parameter in ssrs expression.

Example:

iif(Fields!myfield.value = Parameters!myparameters.value(0),"0","")

I tried using Join, Instr its not working. Can you please help me out with this problem

Anonymous said...

This is an awesome solution. This is the only place I've found it. Thank you so much.

Ned

Bob Pearson said...

Glad I could help!

Anonymous said...

Just to point out: "watch out blanks!"... the value Y2K is not the same of Y2K%20 ;)

Anonymous said...

Good start, but as someone mentioned, passing many values is a pain. Try this instead

"&Parameter_Destination=" & Join(Parameters!Parameter_Origin.Value,"&Parameter_Destination=")

This is shorter and does not have an issue with nulls.

Sorry for the late posting, but was trying to figure this out, learned from the fact that you could specify the parameter multiple times, and then combined that with the join.

Bob Pearson said...

Good idea when calling from another report... :)

Anonymous said...

This may work when the available values for the parameter are static. But if the available values in the calling report are from a query, doesn't work. Any idea on how to that?