Easy multi-value parameters in SQL Server Reporting Services

I recently came across an application where I needed to use a multi-value parameter in SQL Server Reporting Services (SSRS).  I always seemed to have trouble with these in the past.  This time I found them very easy to use because I found (re-discovered?) a simple way.

The basic idea is to set up a parameter as if it were going to be a single value parameter with the topmost row selected by default.  I will walk through the steps of this scenario.

I chose the AdventureWorks database to do this sample and focused on June 2004 Sales to get an easy dataset to work with.

1. Create the basic dataset:

Sales By Territory Dataset

2. Set up the layout for the report:

3. Preview the report with no parameters:

4. Create a new dataset for the territories list:

5. Set up the parameter, TerritoryID, like a normal single-value parameter.  Be sure to set the default value to make it default to the topmost row in the list.  In this case, the topmost is “Australia”, TerritoryID of 9.

6a. Apply the filter to the table.  Notice that I am not editing the dataset, but simply adding a filter to the table properties.  If you are usually the type to edit the dataset (like myself), just hang with me for a second.  Right-click the edge of the table and then choose Properties:

6b.  Modify the table properties to add the filter.  Since you can’t read it all, it says

=Fields!TerritoryID.Value = Parameters!TerritoryID.Value

7. Check the finished result.  We now have a report with a single parameter.  The topmost selection, Australia, is automatically selected and our filter is applied by default.

8. So far, so good–there is nothing extraordinary about what we’ve done so far.  Now, we simple make the parameter multi-value by changing the report parameters and checking the Multi-value box:

9. Don’t rush off just yet, or you’ll see this error message when you choose Preview:

An error occurred during local report processing.
An error has occurred during report processing.
The processing of FilterExpression for the table ‘Sales’ cannot be performed. Cannot compare data of types System.Byte and System.Object[].  Please check the data type returned by the Filter Expression.

10.  You need to make one small change to the filter.  Open the table properties again, but this time change the operator from equals (=) to IN.  Since the Parameters!TerritoryID.Value now contains a comma separated list, we need to search IN instead of comparing.

11. Now, preview your report.  The left image shows the initial preview, the right image shows the drop-down list blown out, you can see that Select All is selected by default.

One caveat is that performance is not going to be top notch because Reporting Services is filtering on the entire dataset every time.  It would (most likely, depending on your implementation) be faster to filter in the dataset.  However, this approach often requires a lot of development work on the T-SQL side to get it working.  This approach is easy and quick enough for many applications.  Enjoy.

Update: I found a good example of the hard way to do multi-value parameters (by using SQL).  If you scroll down to the second post, there is source code there for converting a delimited string into a table variable.  I have re-formatted the charlist_to_table source code to make it more legible.

25 Responsesto “Easy multi-value parameters in SQL Server Reporting Services”

  1. Kylie Mitchell says:

    Brilliant, thanks from a newbie to SSRS and IT generally, previously Excel expert.

  2. govind patel says:

    Great article

  3. Srinivas says:

    Thanks, and Greate work you have done, this article helped me lot

  4. Aaron says:

    I’ve added a related follow-up article on Non-queried multi-value parameters.

  5. Don says:

    Thanks. I was having a brain freeze and glad I founds this. The advantage to filtering it on the table level is that if you the user changes parameters and re-generates the report, it is very fast since it already has the dataset.

    don

  6. Willem says:

    Thanks,

    Simple and fast solution for us guys new to reporting services world.

  7. Jaap says:

    Doing the filering in the dataset is just as easy as filtering the data afterwards and gives much better performance

    Just use a where clause like
    “WHERE TerritoryID IN (@TerritoryID)”

    SQL sever will read the multi value parameter @territoryID as a comma seperated list of values.

  8. Aaron says:

    I totally agree on performance, but in some cases you cannot modify the SQL and are stuck with the above. In my case, I was not using SQL as a datasource and thus was unable to create a query with an IN clause. I used SQL for the example because everyone is familiar with it.

  9. Sijoy Jose says:

    Aaron,
    Thank you for the tip. I found it very helpful. I have been searching this multivalued parameter option for some time.

    Jaap,

    As you suggested, the named parameter option will have much performance aspect. But, I was struggling as my ODBC connection not supporting the named parameter (“WHERE TerritoryID IN (@TerritoryID)”) possibility.
    So, I guess, this is the only one option left for me.
    Please suggest, if you have any more updates on this!

  10. localhost says:

    Thank you! This helped me quite a bit.

  11. bILL says:

    This one did it for me:

    Just use a where clause like
    “WHERE TerritoryID IN (@TerritoryID)”

  12. David says:

    Merci énormément pour cette petite explication. Cela est plus que pratique.

    Merci encore.

  13. israeldiaz says:

    Excelente articulo funciona muy bien, saludos

  14. Diana says:

    Thank-you! This worked perfectly!

  15. Doug says:

    very professonal. Thank you

  16. Douggy says:

    WOW! Thanks for taking baby steps with us. Love now you said, “don’t go anywhere just yet” – See many of us developer want to copy and paste the code real quick so i like you format here and I learned more. Saved me a lot of time. It’s 3:30AM and I been trying to figure this out since 8:00PM. LOL! I can go to bed now.

    YOU ROCK!

  17. Eugene says:

    DUDE thnks a mill its true, MAN u Rock!

  18. marion says:

    Many thanks, it is a great page :)

  19. Andy Brown says:

    For an update of how to do this in Reporting Services 2008 R2, see http://www.wiseowl.co.uk/blog/s187/multivalue-parameters.htm.

  20. Rohan says:

    Thanks. It worked.

  21. Madhavan says:

    Thanks a lot….

  22. Jesus says:

    Thanks!!

  23. Gonçalo Ribeiro says:

    Very Nice Tutorial! Thanks Man!!

  24. crf.nu says:

    This is my first time pay a quick visit at here and
    i am truly pleassant to read everthing at single place.

  25. Maritza says:

    Excellent!! I have been using Sp and SPlit function Sp and this is a better and easy way !!!

    Thanks so much!!

Leave a Reply