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:
Thank you so much! This is exactly what I was looking for.
This is great. You're the only place I found that had this solution.
Likewise, I searched for ever and only found the answer here. Thanks!
Awesome man! Your post was the only place I found the solution to this!
I just spent the weekend trying to write a custom report viewing page then came across this. Solved all my issues. Thanks!
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
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
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¶1=59119¶1=59120
give me 59119,59120 but if you just edit it to 59119, 59120 it works. its like its missing the line feed. Thoughts?
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]) .
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.
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
This is an awesome solution. This is the only place I've found it. Thank you so much.
Ned
Glad I could help!
Just to point out: "watch out blanks!"... the value Y2K is not the same of Y2K%20 ;)
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.
Good idea when calling from another report... :)
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?
Post a Comment