Expression Problem Using a Multi-Select Parameter

I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.

My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.

The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.

How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?

I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

If you're on SP0 or SP2 or later, the Select All option is always there. It's not really a checkbox you can detect. It's just a shortcut way for selecting/deselecting all options.

I've reported this as an enhancement. You should be able to tell whether they've selected all possible options:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124515

At that link I describe a couple of possible workarounds. Hope that helps.

|||

There is currently no built-in functionality, but here are some ideas to achieve what you are looking for:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values (e.g. =Parameters!P1.Count).

-- Robert