Non-queried multi-value parameters in SQL Server Reporting Services
As a follow-up to my previous post on Easy multi-value parameters in SQL Server Reporting Services, I wanted to point out some information on non-queried, or static multi-value parameters.
For my example, I’m using a parameter named “Time” with choices “Morning”, “Noon”, and “Night”. The values are M, N, and I respectively. You can see how I set up the parameter in the following screenshot.
You can accomplish having all the checkboxes selected by default in two ways. The first way is to pass an object array of values, and the easiest way to do that is to use the Split command to split a string into a string array. I used =Split(“M,N,I”,”,”) as my default. I had to provide the second parameter for Split, the delimiter, because the default is space.
The second, and easier method, is to just add a line item for each item you want checked, as demonstrated in this screenshot:
Here is the resulting parameter list, showing all the items selected (same for either method):
Sometimes, you need to display the parameters of your report, perhaps so it could be regenerated later. You’ll need to print out that object array, so use the opposite of Split, Join. Below is a textbox with an expression of =Join(Parameters!Time.Label, “, “), which joins the parameter values together with a comma and space separator.
Here is the result of the code, with two parameters selected shown:
If you only put the parameter label in the textbox (i.e. using code =Parameters!Time.Label) then when you display the report, you’ll get the famous Reporting Services #Error message. You have to use Join in this case.
Is there a way of having default “select All” from a Non-Queried list?
If you select all the possible values, it is the same as “Select All”. For example, when I picked M, N, I for Morning, Noon, and Night, all of them were selected, which is the equivalent of checking “Select All”. They will always all the values show in the display. It never does show “Select All”.