Pass Multi Value Parameter to SSRS Report Using URL

        I have published a post that explaining about Passing Parameters to SSRS Report using URL few months ago. That post solves one problem but still have some other problems. When we going to pass a multiple value parameter then that is stuck. The reason is we cannot pass 1-dimensional array through an URL.

      When we  want to pass dynamically changing  multiple values to SSRS report through URL  try the following way.

       We have our original report and target report. The error says exactly cannot pass a string array. So I have to pass values as one string. What I’m going to do is pass all the values as string to temporary parameter in the target report and then split those values.


In Original Report

         I want to pass Status (which is a multi-valued parameter) parameter’s values to my target report. Using Join I can pass the parameter values separated by commas as one string. That’s the only thing we should do in our original report. Don’t bother about param1. For the time being use it as following.

=”javascript:void(window.open(‘<ReportServerURL>?<ReportURL>&rs:Command=Render&param1=” & Replace(Join(Parameters!Status.Value,”,”),”&”,”%2526″)  & “,’_blank’))”


(Above code is clearly described in Passing Parameters to SSRS Report using URL)



In Target Report

         Then we comes to target report. I have to set those values to parameter called MonthlyStatus  in my target report. Before that we need to add new parameter for target report. I named it as ‘param1‘ ( I think now you can get the idea about param1 which I used in above expression. Instead of directly passing to MonthlyStatus I use param1). param1 is a new parameter, which haven’t  any available values or any default values.  Set parma1 as a hidden parameter.(Because no one want to see that )


       Then we come to the simple part. Now we have all the values separated by commas in param1 and need to split each value and set to related parameter. We can do this by editing the default value expression of the related parameter (in this case MonthlyStatus)

Make sure param1 is placed above than MonthlyStatus  of the parameter order. Because MonthlyStatus  using param1 value as default value.

Note: You cannot see the report preview in BIDS. But after deploying the report you can see that multi-value parameter’s values are passing to target report.

There may be some better solutions for this ! If then please leave as comments.

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 !