Passing Multi Value Parameter to SSRS Report Using URL

  • Article

Introduction

This solution explains how to pass a multiple value parameter(SSAS based) from one report to another using the URL method.  The intention is to provide the second report in a pop-up window. We cannot use SSRS built in "Go To Report" action type, instead we are going to use "Go To URL" action type.

Solution

We have our original report and target report. We need to pass values as one string. In order to do that, pass all the values as string to temporary parameter in the target report and then split those values in that end.

In Original Report

Suppose we want to pass Status (which is a multi-valued parameter) parameter's values to our target report. Using Join we 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. We will meet param1 in target report.

="javascript:void(window.open(‘<ReportServerURL>?<ReportURL>&rs:Command=Render¶m1=" & Replace(Join(Parameters!Status.Value,","),"&","%2526")  & ",'_blank'))"

Explaining Replace function with %2526

After all most common seen problem is ‘&’ (ampersand) symbol cannot pass in URL. because SSAS dimension members contain ‘&’ symbol. (Assume we have MonthlyStatus dimension and it has member [DimMonthlyStatus].[MonthlyStatus].&[Active] that we want to pass)

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 contains 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 '))"

In Target Report

Then we come to target report. We have to set those values to parameter called MonthlyStatus  in our target report. Before that we need to add new parameter for target report. Name it as 'param1' ( which was used in above expression). param1 is a new parameter, which hasn't  any available values or any default values.  Set param1 as a hidden parameter.(Because no one wants 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.

https://altecnotes.files.wordpress.com/2012/01/parameterproperties.png?w=614

https://altecnotes.files.wordpress.com/2012/01/defaultvalueexpression.png?w=614

Make sure param1 is placed above the MonthlyStatus  in the parameter order. Because MonthlyStatus  using param1value 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.