Using Multi-Value Parameters in SSRS

Introduction

SQL Server Reporting Services (SSRS) is a robust reporting tool from Microsoft, renowned for its capability to generate a diverse range of reports. Recently, Microsoft announced the first release candidate of SQL Server 2019 Reporting Services, underscoring its continued relevance and strength in the market. SSRS remains an essential tool for organizations that need to develop custom and mobile reports.

Report Builder is a streamlined tool designed to facilitate report creation in SSRS. This article will explore the use of multi-value parameters in SSRS, focusing on how they can enhance the flexibility and user experience of reports. For a comprehensive guide on using Report Builder, consider referring to our detailed tutorial on the subject.

What Are Multi-Value Parameters?

Multi-value parameters in SSRS allow users to pass one or more values to a report, offering a “Select All” option to include all possible values. This functionality significantly enhances report flexibility and interactivity, enabling users to filter data based on multiple criteria. Multi-value parameters are particularly useful when you want users to be able to select several options from a list and see the filtered results accordingly.

Example Scenario

To illustrate the use of multi-value parameters, let’s consider a scenario from the AdventureWorks Human Resources department. They need a report that lists employees and displays their:

• Employee ID
• Birth Date
• Marital Status
• Gender

Additionally, they want to filter this list based on job titles. We’ll walk through the process of creating a report with multi-value parameters in SSRS using Report Builder.

Step-by-Step Guide

1. Create a Data Source in Report Builder

1.1 Launch Report Builder

Open Report Builder and select the “Blank Report” option from the Getting Started window. This choice provides an empty report designer screen where you can start from scratch.

1.2 Add a Data Source

• In the Report Builder main window, locate the “Data Sources” pane on the right side of the screen.
• Right-click on the “Data Sources” pane and select “Add Data Source.”

1.3 Configure the Data Source

• In the “Data Source Properties” window, select “Microsoft SQL Server” as the connection type.
• Choose “Use a connection embedded in my report” and name the data source, e.g., HRReportDataSource.
• Click the “Build” button to open the “Connection Properties” window, where you can either manually enter the connection string or use the “Test Connection” button to verify your credentials and settings.

2. Create a Dataset in Report Builder

2.1 Add a Dataset

• Go to the “Datasets” pane and right-click to select “Add Dataset.”
• Name the dataset, for example, EmployeeDataset.
• Choose the previously created data source (HRReportDataSource) and select “Use a dataset embedded in my report.”

2.2 Define the Query

• In the “Query” box, enter the SQL query to retrieve employee data:
  sql

  Copy code

  SELECT EmployeeID, BirthDate, MaritalStatus, Gender, JobTitle
FROM Employees

• Click “OK” to create the dataset.

3. Add a Multi-Value Parameter

3.1 Create the Parameter

• Go to the “Parameters” pane and right-click to select “Add Parameter.”
• Name the parameter, e.g., JobTitleParameter.
• Set the Data Type to “Text”.
• Check the box for “Allow multiple values” to enable the multi-value functionality.

3.2 Configure Available Values

• For the “Available Values” section, select “Get values from a query.”
• Choose the dataset that retrieves job titles:
  sql

  Copy code

  SELECT DISTINCT JobTitle
FROM Employees

• Set both the “Value Field” and “Label Field” to JobTitle.

3.3 Set Default Values

• Optionally, you can configure default values for the parameter if needed.

4. Filter the Dataset Using the Parameter

4.1 Modify the Dataset

• Edit the EmployeeDataset to apply a filter based on the selected job titles.
• In the dataset properties, go to the “Filters” tab.
• Add a new filter with the following settings:
  • Expression: =Fields!JobTitle.Value
  • Operator: IN
  • Value: =Parameters!JobTitleParameter.Value
• Click “OK” to apply the filter.

5. Design the Report

5.1 Add Data to the Report

• Drag and drop the fields EmployeeID, BirthDate, MaritalStatus, and Gender onto the report design surface.
• Use a table or matrix to display the data effectively.

5.2 Preview the Report

• Click on the “Run” or “Preview” button to test the report.
• Use the parameter dropdown to select multiple job titles and ensure that the report updates accordingly.

Conclusion

Multi-value parameters in SSRS offer a powerful way to enhance the functionality and user experience of your reports. By allowing users to filter data based on multiple values or select all options, you can create more interactive and flexible reports. The steps outlined in this article demonstrate how to set up and use multi-value parameters in Report Builder, enabling you to develop sophisticated and dynamic reports tailored to your organization’s needs.