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 aboutparam1. For the time being use it as following.
=”javascript:void(window.open(‘<ReportServerURL>?<ReportURL>&rs:Command=Render¶m1=” & 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 caseMonthlyStatus)
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.
Multi-Value Parameters in SSRS 2014
In SQL Server Reporting Services, Multi-valued parameters allows the users to select More than one value from the List and then, SSRS will filter the Report data using the user selected values. OR Multi-valued parameters allows the users to Dynamically Filter the SSRS Reports using more than one value. In this article we will show you, How to add Multi-valued parameters in SQL Server Reporting Services 2014 with example.
We are going to use below shown report to explain, Multi-valued parameters in SSRS Reports. Please refer SSRS Table Report article to understand the creation of Table report. If you observe the below screenshot, It was a normal Table report with Country, Full Name, Occupation, Yearly Income and Sales Amount columns.
Below screenshot will show you the, Data Source and Dataset we used for this report.
SQL Command we used in the above screenshot is:
T-SQL QUERY
Adding Multi-valued parameters in SSRS 2014
Before we start creating the parameter. Let us create one more dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]
SQL Command we used in the above screenshot is:
T-SQL QUERY
Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.
- Name: Please specify the valid Parameter name as per your requirement. Here, We specified it as CountryParameter
- Prompt: The text you specified here will be displayed as label before the text box
- Data Type: Country name is a Text data type so, we kept the default text unchanged
- Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option.
Next, We have to select Available Values as shown in below screenshot. If you can specify the values manually, you can select specify values option and enter the values manually. In this example, We would like to use the CountryName dataset we created earlier so, selectGet Values from a query option and select the Dataset from the list.
Please specify the Value Filed and Label Field. Here, we have only one column so, both will be Country.
- Value Filed: This value will be sent to query. Report will be filtered using this value.
- Label Field: This value is shown to end-user.
Click Ok to finish configuring the Multi-valued parameters. Now, We have to apply filter condition on dataset. Please refer Filter at Dataset Level in SSRS article to understand the creation of Filters.
Select the Dataset from the Report data tab and Right click on it will open the context menu. Please select the Dataset Properties.. option from it
In this example, We are going to display the records whose [Country Name] is present in[Country Parameter] we just created so, Please select the Country as Expression and Operator as IN Operator and Value as Parameter name. If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.
Click Ok to finish configuring the Filters at Dataset Level. Lets Click on Preview Tab to preview the Data.
From the above screenshot you can observe that, It is displaying Blank Report allowing us to select Multiple values from the List of all the country names present in the Dataset we created. In order to display the records, We have to select the Country Name (s) from the list and Press Enter.
For now, We selected the Canada and Germany as Country Names.
Thank You for visiting Our Blog.
