Saturday, May 19, 2012

Passing Parameters to SSRS Report using URL

      Parameters are very useful when you come to report customization. Its going to more useful, if those parameters can set before loading the report. you can do it easily using URL. With SharePoint Integrated mode, this is little bit confused.

There are two ways to do this task.

1]  using Report Viewer (this is the default way when you configured reporting service to SharePoint Integrated mode)

2] using HTML viewer (This is the most interesting way in SharePoint Integrated mode. I’m going to stick to this way mostly)

1.

When you passing parameters to  report viewer you have to follow this way.
ReportViewer URL&rp:parameter1=Value1&rp:parameter2=value2
nothing can be wrong with this except parameters displaying in right hand side pane, which i do not like. :)

2.


You can use the HTML viewer to view the reports .
here is the way to view the report in HTML viewer.

Your Report Server Virtual Directory ? Full Path of Report (where report is deployed in SharePoint site)

Example — > http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl

Using above example you can view the report in HTML viewer. When it comes to parameter passing  just append the parameter names and values to end of the URL with rs:command=render.

http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&rs:command=render&parameter1=Value1&parameter2=value2

You can pass multiple parameters using above format. Target report’s parameters affected by values that you passed in URL :)




Further Modifications …

This can be used in SSRS Report drill down. When you want to drill down report there is an option to set URL.






Simply can set the URL to target report using expression with available parameters. Following expression can use to pop up the target report in separate window.

=”javascript:void(window.open(http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&rs:command=render&parameter1=Value1&parameter2=value2‘,’_blank’))”



After all most common seen problem is ‘&’ (ampersand) symbol cannot pass in URL. because most of the time parameter values contain ‘&’ symbol. See the following example.

=”javascript:void(window.open(‘http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&ReportParameter1=” & Parameters!ReportParameter1.value & ” ‘,’_blank’))”


 Parameters!ReportParameter1.value containing a ‘&’ symbol but this cannot be passed. you can Replace the ‘&’ symbol with ‘%2526′ ( which is the url encoding of ‘&’)
 
=”javascript:void(window.open(‘http://Server1/ReportServer?http://server1/sites/Test/Reports/YearlyTrend.rdl&ReportParameter1=” & Replace(Parameters!ReportParameter1.value,”&”,”%2526″) & ” ‘,’_blank ‘))”




Post any problems that you have regarding this. Any thoughts will be welcomed !

No comments:

Post a Comment