Multivalue Parameters in SSRS 2005

I just posted earlier today, but I need to post this topic while it is fresh on my mind. It took some digging, and I want to get this out there while I can remember it clearly. I want to thank Munish Bansal for his blog post on this topic, which was where I found the answer to my search on this question.

In SQL Server 2008 Reporting Services, handling multivalue parameters is relatively straightforward. You just set the parameter property to multivalue and make sure that the filter is set to the IN comparison instead of equals (=). But in SSRS 2005 it is not quite that easy. You set up the parameter the same way, but the filter works differently. Instead of adding it as a filter at all, you add the condition as a Parameter property and put the IN logic into the dataset query directly. For example, the query's WHERE clause would have a condition like this in it:

WHERE My_Column IN (@MyParameter)

Then in the Parameter property of the dataset query you use the JOIN function to turn the multivalued parameter information into a comma-delimited set of values, as shown below. (Click the image to see it full-size.)

Once this is done the parameter will work as desired.

Hopefully you are working in SSRS 2008 because the improved handling of multivalue parameters along with many other enhancements make it almost a non-decision to upgrade as soon as possible to the latest SQL Server version. Denali promises even more enhancements in Reporting Services, and I can't wait to see what's coming next.