Working with Multi-Valued Parameters in SSRS

Home > Blogs > SQL Server > Working with Multi-Valued Parameters in SSRS

Working with Multi-Valued Parameters in SSRS

2 5 Peter Avila
Added by April 25, 2012

In one of my SQL courses; 6236: Reporting Services, we learn how to create an “All” option in the values dropdown list of a parameter and see how to “wire up” the All option so that, when selected, the report runs for all values.

As an example, imagine a Sales Report that shows sales by region with a Country parameter that allows users to run the report for different countries. After adding the All option, the Country parameter dropdown list looks like this:

All option Country parameter SQL Using parameters with multiple values in Reporting Services

The All option is useful, but it has an important limitation; it only allows us to select either a single country or all countries, but if we want to select a few countries at random, let’s say the United States, Mexico, and Canada, for the same report run, this approach doesn’t allow us to do that.

In order to allow random, multiple-value selections in a parameter’s dropdown list, we must first enable multiple values for that parameter.

  1. In the Report Data pane of the report, right-click on the parameter in the Parameters folder and select Parameter Properties.
  2. Put a check in the Allow multiple values checkbox.
  3. Click OK.

Parameter Properties SQL Using parameters with multiple values in Reporting Services

 

By simply doing the above, our parameter list looks very different when we preview the report. In the example below, three countries have been selected at random (note also the Select All option that is built into this approach):

SQL Using parameters with multiple values in Reporting Services

 

 

 

 

 

 

 

 

This is a good start, but we’re not done. We still have to tie the parameter into the report’s dataset; in other words, we have to restrict the data coming from the dataset to only those rows that contain the selected states.

In order to accomplish that, we need to keep in mind that a multi-valued parameter looks like a list of values when the user selects multiple values. With the selections shown in the example above, the parameter would look like this: Albania, Algeria, Andorra. We need to tell our query in the report’s dataset that it should select rows where the country is one of those. We do this with an IN operator and we must also delimit the parameter in parenthesis:

  1. In the Report Data pane of the report, right-click on the dataset of the report and select Dataset Properties.
  2. Add a WHERE clause to the query like this: WHERE Country IN (@Country)

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Cable Testers and How to Use them in Network Environments

0 692 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

How to use AWS CloudFormation templates to automate solutions inside Amazon AWS

0 380 0

  In the AWS Class here at Interface, we actually build fully automated solutions with AWS CloudFormations. Here’s an overview of using AWS templates using CloudFormation. CloudFormation is basically an “infrastructure-as-code tool” where you can go into a declarative document and define all the resources that you want and feed the document into the CloudFormation … Continue reading How to use AWS CloudFormation templates to automate solutions inside Amazon AWS

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 249 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

See what people are saying...

    Share your thoughts...

    Please fill out the comment form below to post a reply.