Using multi-value parameters in SSRS

This article covers the usage and detailed features of the multi-value parameter in SSRS.

Introduction

SQL Server Reporting Service also known as SSRS is a reporting tool of Microsoft that helps to develop various reports types. In addition, a few months ago Microsoft announced the first release candidate of SQL Server 2019 Reporting Service. This means that SSRS is still a powerful tool in the market and at the same time it plays a key role for companies who need to build custom reports and mobile reports.

Report Builder is a lightweight tool that helps to develop reports for SQL Server Reporting Service. In this article examples, we will use Report Builder. You can refer to SSRS Report Builder introduction and tutorial article for more detail about the SSRS report builder.

Now, let’s focus on the main topic that parametrized reports gain us more flexibility and provide an enhanced user experience. Starting from this point of view, we will learn the multi-value parameters in order to develop more advanced reports.

What is the multi-value parameter?

The multi-value parameter allows us to pass either one or more than the input value to the report. Also, it offers a “Select All” option that helps to select all parameter values. Now, we will create an example of the multi-value parameter in SSRS.

Example scenario

The Adventureworks human resources department required a report about the employees who are working in the company. They want to see the identity number, birth date, marital status and gender of the employee in the report. Furthermore, they want to filter the employees according to their job titles. They sent a draft about the report which is illustrated below:

Create a Datasource in Report Builder

As the first step, we will launch the Report Builder and then select the Blank Report option in the Getting Started window:

This option helps us to open an empty report designer screen quickly. A little disadvantage of this option is to set some options manually. For this reason, we will create a data source and dataset of the report manually.

In SSRS, data sources stored detailed information and credentials about the connections. In the Repor Builder main window, data sources are placed on the right side of the screen, we will right-click and select Add Data Source option to add a new data source:

On the Data Source Properties window, we can find various connection types that can be used in the reports. We will use the Microsoft SQL Server connection type for our report and select Use a connection embedded in my report and give HRReportDataSourcename:

We can either fill the Connection string text box manually or we can use the Build option in order to generate a connection string. We will click the Build button and set up the credentials of the connection string:

After setting up the connection string, we will click the Test Connection button to be sure that we have set the proper settings:

If we see the “Connection created successful” message, we can figure out that all options are properly configured for the data source.

Create a dataset in Report Builder

Dataset is used to represent the result set of the query in the Report Builder reports. It stores detailed pieces of information about the resultset such as query string, column names, data types of the columns and etc. However, the dataset never stores the actual resultset of the query.

For our example, we will right-click on the Datasets folder in the Report Data tab and click the Add Dataset option:

In the Dataset Properties window, we will choose the Use a dataset embedded in my report option so that the dataset which is created in the previous step will appear in the Data source combo box. We will select the HRReportDataSource data source for this dataset and will give a name which is HRReportReportDataset:

After these settings, we will use the following script in order to get data from SQL Server:

SELECT

[NationalIDNumber]

,[BirthDate]

,[MaritalStatus]

,[Gender]

FROM [HumanResources].[Employee]

WHERE JobTitle IN (@JobTitleParam)

As you can see in the above, the script contains a variable that is defined as @JobTitleParam. This parameter can be used to supply input for the report so that we can filter and control the query resultsets. Particularly for this report, it filtered the query according to the JobTitle. As the last step, we will click Refresh Fields and click OK:

After we complete this step, @JobTitleParam will appear under the Parameters folder and the report columns also appear under the Dataset folder:

The multi-value parameter in Report Builder

The @JobTitleParam parameter has been created automatically, however, we need to associate it to the JobTitle column values of the HumanResources.Employee table. To do this, we will create a new dataset and associate the returning values to @JobTitleParam so that we can filter the HRReportReportDataset query according to these values. We will add a new dataset whose name is HRReportParameterDataset and use the following query:

1	select distinct JobTitle FROM [HumanResources].[Employee]

We will right-click the @JobTitleParam parameter and choose Parameter Properties in the context menu:

We will click the Allow multiple values option in the General tab so that we can determine the parameter as a multi-value parameter and then change the Prompt field. This changing will affects the parameter’s showing name in the report. After these settings, we will click to the Available Values tab:

Through this tab, we will associate a relation between dataset query result values and parameters. At first, we will select the Get values from a query so that we can create a connection between dataset, and parameter. In the second step, we can determine the value field and label field for the parameter.

Value field specifies the values which are passed to the queries
Label field specifies the values which are displayed in the report

For our example, we will set these options as shown in the below image:

Designing report in Report Builder

In this part, we will design a very basic report that described in our scenario:

Click Insert in the Report Builder menu
Click the Table button and select the Insert Table option on the menu
Drag and drop the HRReportReportDataset columns to the table
Change the footer of the report as Employee detail report

Finally, we will click the Run button to see the report. If we click (Select All) options, it will select all parameter values or we can make individual parameter value selections. View Report option is used to execute the report:

The IN operator is used to specified multiple values in the query. The executed query can find out in the profiler and it will be like as below:

Setting default values for multi-value parameters

In some cases, we need to populate the parameter values with the defaults. For example, if we want to access the Accounts Manager value to be the default for the @JobTitleParam, we can determine in the Default Values tab. At first, we choose the Specify values option and then write it into the value field:

After these settings, the outcome of the report will be as below:

If we want to set Select All option as a default parameter we need to follow the steps below:

Choose Get values from a query under the Default Values tab
Set HRReportParameterDataset into the Dataset Combobox
Set JobTitle field into the Value field

If we run the report, we can see that all values are selected in the first execution of the report.

Display selected values of the multi-value parameter

Expressions are used to generate custom functions in the SSRS reports with the help of the built-in functions and custom codes. In order to display the selections of the multi-valued parameter, we will use expressions. Join function can be used to concatenate the selected values of the multi-value parameter. If we follow the below steps, we can display the selection of the multi-value parameter:

Add a textbox to the report
Right-click on the textbox and select the Expression menu item
Fill the value field with the below expression:

1

"Parameter Values: " & JOIN(Parameters!JobTitleParam.Value, ", ")

If we select more than one value in the multi-value parameter, the outcome of the report will be as below:

Conclusion

In this article, we learned to design a basic report in Report Builder, and we learned also how to use a multi-value parameter with advanced settings.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec