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

A Simple Introduction to Cisco CML2

0 3876 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Creating Dynamic DNS in Network Environments

0 638 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 720 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

Write a Comment

See what people are saying...

    Share your thoughts...

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