Today I was working on a Reporting Services report that needed to have all the values checked for a multi-value parameter when the report opened up. It was a little tricky to figure out, but it turned out to be very simple in the end.
Multiple Value Parameters
I've got some multiple value parameters on a report. These are easy to create in SQL Server Reporting Services. Just check the box for your parameter and the user gets a check box drop down letting them pick more than one value from the drop down.
Available Values
You can set the available values for the drop down to a dataset in your report or a shared dataset in the Reporting Services project. Next, pick the value and name fields (which can be the same column in the dataset if that's what works in your situation).
Default Values
This is the part where we want to check each of those multiple values on report startup. The hard part is, I don't know the values at design time, so I can't hard code them. I want to simulate a "select all" of the check boxes. My mind started wandering to VBA code I could put in the report as a startup function.
But on a whim, I tried selecting all the values from the same query, and I was pleased to see it work!
And I get the results I want on the report when it opens:
