Esat Erkec
Setting the expession into textbox

Using multi-value parameters in SSRS

November 27, 2019 by

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:

Report draft

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:

Blank Report in the Report Builder

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:

Add data source to Report Builder

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:

Data source connection settings in the Report Builder

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:

Create connection setting in the Report Builder

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

the Connection created successful message

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:

Add a new dataset in the Report Builder

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:

Data source properties window

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

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:

Edit query in the data source

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

Report Builder Report Data tab

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:

A data set of the multi-value parameter in the Report Builder

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

Multi-value parameter properties

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:

Allow multiple values for multi-value parameter


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:

Get values from a query option for multi-value parameter

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

Designing a Report Builder 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:

Running the Report Builder report

Outcome of the Report Builder 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:

Multi-value parameter executed query details

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:

Setting default values of the multi-value parameter

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

Using multi-value parameter in a report

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

Setting default values of the multi-value parameter from a query

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

    Setting the expession into textbox

  • Fill the value field with the below expression:

    Displaying the multi-value parameter selections with help of the expression

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

Displaying the multi-value parameter selections in the Report Builder report

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.

Esat Erkec

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
Esat Erkec

Latest posts by Esat Erkec (see all)

203 Views