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

Exporting to Excel format

Quite often, when a user exports to an Excel spreadsheet and selects "Open"
instead of "Save" for the file download dialog the file does not open.
Instead, they get an error saying "xxxxx.xls could not be found. Check the
spelling of the file name, and verify that the location is correct". This
seems to happen more often than not.
Is there anything which can be done about this? The problem doesn't occur if
they save the file instead of opening it, but they should not have to
perform extra steps for what should be a simple operation.
PeterThis is a bug. You have identified the workaround: Save the file to the
local machine and then open it. At this time I cannot give you any specific
guidance when a fix might be available.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Kenyon" <p.kenyon.no.spam@.paradise.net.nz> wrote in message
news:%23XZJHuPdEHA.3572@.TK2MSFTNGP10.phx.gbl...
> Quite often, when a user exports to an Excel spreadsheet and selects
"Open"
> instead of "Save" for the file download dialog the file does not open.
> Instead, they get an error saying "xxxxx.xls could not be found. Check the
> spelling of the file name, and verify that the location is correct". This
> seems to happen more often than not.
> Is there anything which can be done about this? The problem doesn't occur
if
> they save the file instead of opening it, but they should not have to
> perform extra steps for what should be a simple operation.
> Peter
>