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

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 606 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

See what people are saying...

  1. Avatar Hieu Trung Ho

    Excellent subject.
    Love it !!!!

    Thanks a lot, Sir

  2. Avatar David Ma

    using this method works for a predefined parameter value list. is there a way for self defined list. ie, entering item1, item2, item3?

    Thank you,
    David

  3. Avatar Alicia

    Totally amazing fix that bleies belief, but nonetheless, it solved the 403 error I was receiving on the second run of replicating an ASP/SSRS application. The first run worked fine without the addition of this file, the second would not render a report because of this error. Adding this file into the Documents section did the trick.

  4. Peter Avila Peter Avila

    You are most likely running into the Network packet size limit. You might consider putting the values you want in a table instead and then change your SQL query to something like, "WHERE MyField IN (SELECT fieldName FROM TableContainingSelectedValues WHERE SelectedFlag = 1)". A custom interface written in, say, .Net, would allow you to control this nicely so that you can continue to have a dropdown for parameters. They would become part of the .Net interface you develop rather than a part of the report.

  5. Avatar ALR

    Great post! I do have a question though. I have a report with a multi-valued parameter that contains over 170 values (not ideal, I know, but the business users want it this way). Those 170 values equal over 2000 characters. I have deployed the report to my Report Server. When I choose ‘select all’ from the drop down list, I get this error:
    “An internal error occurred on the report server. See the error log for more details.
    String or binary data would be truncated. The statement has been terminated.”
    Is there some kind of character limitation in SSRS when using the ‘IN’ clause (i.e. a multi-valued parameter) that would cause my report to fail?
    Thanks for your help!

Share your thoughts...

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