In Reporting Services, there are generally two different methods to determine when a user has selected all of the values in a multi-value parameter (i.e. a parameter where the “allow multiple values” checkbox has been checked). The method to choose is dependent upon whether you are trying to determine this during report rendering or as part of the data retrieval for the report. Usually this will become a requirement when the multi-value parameter is populated from a dataset (i.e. “Get values from a query” has been selected from the Available Values tab) as opposed to being hard-coded (i.e. “Specify values” has been chosen from the Available Values tab) where the number of available values is static and known.
An example of needing to determine if all parameter items are selected during report rendering, is when you need to display the selected values from a multi-value parameter on your report. Rather than display a list of all items in the case that all are selected (or none were de-selected), you could show something like: “Selected Brands: All”. This would be particularly nice is the parameter list is long.
This can be accomplished by comparing the count of items from the dataset that populates the available values of the parameter using the CountRows aggregate function, to the count of selected parameter items using the parameter object’s Count function. In the example below, the dataset is named dsBrands and the parameter is named Brands:
You cannot, however, use this method if you are trying to determine if the user has selected all values as part of the data retrieval process for the report. An example of this would be when you are constructing your dataset query as part of a function based on the selected items from a multi-value parameter (this will become clearer in the example below). If you try to use the CountRows function at this point, you’ll receive an error similar to the following:
“Aggregate and lookup functions cannot be used in query parameter expressions”
The Parameter object does not provide a function to get the total item count, but as mentioned above, it does provide the selected item count. Therefore you can set up a second Internal or Hidden parameter and set the default values to the same dataset, thereby allowing you to retrieve the total item count from the hidden parameter and the selected item count from the visible parameter. In the following simple MDX query, I am adding the Brand constraint only if the user has not requested all brands:
As a note, you can use this method (i.e. creating a hidden parameter) to perform the first task of determining if all parameter items are selected during report rendering, however it is not as efficient, as it results in the dataset query being performed twice, once for each of the parameters (visible and hidden).
An example of needing to determine if all parameter items are selected during report rendering, is when you need to display the selected values from a multi-value parameter on your report. Rather than display a list of all items in the case that all are selected (or none were de-selected), you could show something like: “Selected Brands: All”. This would be particularly nice is the parameter list is long.
This can be accomplished by comparing the count of items from the dataset that populates the available values of the parameter using the CountRows aggregate function, to the count of selected parameter items using the parameter object’s Count function. In the example below, the dataset is named dsBrands and the parameter is named Brands:
="Selected Brands: " & IIf(CountRows("dsBrands").Equals(Parameters!Brands.Count),
"All", Join(Parameters!Brands.Label, ", ")
You cannot, however, use this method if you are trying to determine if the user has selected all values as part of the data retrieval process for the report. An example of this would be when you are constructing your dataset query as part of a function based on the selected items from a multi-value parameter (this will become clearer in the example below). If you try to use the CountRows function at this point, you’ll receive an error similar to the following:
“Aggregate and lookup functions cannot be used in query parameter expressions”
The Parameter object does not provide a function to get the total item count, but as mentioned above, it does provide the selected item count. Therefore you can set up a second Internal or Hidden parameter and set the default values to the same dataset, thereby allowing you to retrieve the total item count from the hidden parameter and the selected item count from the visible parameter. In the following simple MDX query, I am adding the Brand constraint only if the user has not requested all brands:
="SELECT NON EMPTY { [Units] } ON COLUMNS, " &
"NON EMPTY { [Geography].[State].[State] } ON ROWS " &
IIf(Parameters!Brands.Count=Parameters!HiddenBrands.Count, Nothing,
"FROM ( SELECT ( STRTOSET(@Brands, CONSTRAINED) ) ON COLUMNS ") &
"FROM [Motorsports]" &
IIf(Parameters!Brands.Count=Parameters!HiddenBrands.Count, Nothing, ")")
As a note, you can use this method (i.e. creating a hidden parameter) to perform the first task of determining if all parameter items are selected during report rendering, however it is not as efficient, as it results in the dataset query being performed twice, once for each of the parameters (visible and hidden).
