SSRS: Can I know if user selected "ALL" in multivalued param?

Question

Customer wants me to repeat the parameter values in the page header of the report. But if they just choose "Select All" on a multi-valued parameter, they want the text "Any" listed.

For example, one parameter has a fixed set of 9 values. I hard-coded the expression for a text box to:

="Room Size: " &
iif(Parameters!pRoomCap.Count=9,
    "Any",
    Join(Parameters!pRoomCap.Value, ", "))

How can I do this if the parameter source is a query of unknown size?

Answer 1

Can you compare the count of the parameter to the count of the dataset you pull the parameter values from?

Answer 2

I unioned my dataset for the parameters with one which I created manually with a "select" statement - I was then able to force the value to be something like -1 or null.

Then simply check if the parameter contains -1 or null and replace the value in the header with the replacement text.

Answer 3

Try this out. You need to compare the total number of parameters in the dataset to the count of selected parameters. The following assumes that your multivalue parameter is using a dataset called "dsRoomSizes"

="Room Size: " 
& iif(Parameters!pRoomCap.Count = count(Fields!pRoomCap.Value,"dsRoomSizes"),
"Any", 
Join(Parameters!pRoomCap.Value, ", "))

This expression will work in the page header/footer.

UPDATE

In the interests of finding a solution to your problem, the following should work for you. It feels hackish and I encourage you to keep research alternative methods but this will work:

1. Create a second multivalue parameter and name it something like "pRoomCap_hidden".
   • The source of the parameter is the exact same query
   • In the parameter properties, setting the default values to the same query
   • Important: Set the parameter visibility to hidden

This will create a second multivalue parameter in your report that is exactly the same as your initial multivalue parameter only this parameter list will have all values selected by default.

Enter the following expression in a textbox in your header:

=IIF(Parameters!pRoomCap.Count = Parameters!pRoomCap_hidden.Count,"All",Join(Parameters!ReportParameter1.Value,", "))  

The above will compare the selected values in each parameter list. If the lists contain the same selected values then that indicates that "All" have been selected in the first list.

Like I said, it is hackish but it definitely works. Until you are upgraded to 2008, this might not be a bad workaround for you.