You all know that report parameters can be populated through a query, right? And multi-value parameters get this magical (Select All) item as first one in the dropdown list. I used the word magical in the previous sentence because this (Select All) item is not a real one. It’s a bit like a fata morgana: you can click it but it doesn’t really get selected. It only appears that way in the dropdown. But you can’t write an expression that tests if the (Select All) item is selected because it doesn’t really exist in the array. The only thing it does is it selects all items when it gets clicked.
Little side note: as usual there’s a workaround to check if all items are selected. I’ll mention the details on that later.
Sometimes people want to avoid using this functionality and implement their own "All" item. Even more so in the case of a single-value parameter which doesn’t get an auto-generated (Select All) item. And that’s what this article is all about!
Setting The Scene
Once again I’ll use the AdventureWorksDW2012 free sample database and write some product-related queries.
The Parameter Query
Let’s use the following query in a dataset called ProductCategory to populate a multi-value parameter called ProductCategory, type integer:
select ProductCategoryKey ID , EnglishProductCategoryName ProductCategory from dbo.DimProductCategory
ID is used as Value field while ProductCategory serves fine as Label. To have all values selected by default we can use that same dataset:
When rendering the report we now indeed see a dropdown list with a (Select All) item:
To come back to the workaround mentioned in the intro: you can compare the count of the selected items array with the count of the whole dataset. When equal then all items are selected. Here’s what that looks like in an expression:
=IIF(Parameters!ProductCategory.Count = COUNT(1, "ProductCategory") , "all selected", "not all selected")
Fetching Some Data
Like in any regular report we’d like to display some data. Let’s keep it simple and show a list of product subcategories based on the selected categories:
select cat.EnglishProductCategoryName ProductCategory , sub.EnglishProductSubcategoryName ProductSubcategory from dbo.DimProductSubcategory sub inner join dbo.DimProductCategory cat on cat.ProductCategoryKey = sub.ProductCategoryKey where cat.ProductCategoryKey in (@ProductCategory)
Adding an "<All>" item to the parameter
The first step in setting up a custom "<All>" item is adding a simple UNION ALL to the query that fetches the parameter list. That goes like this:
select ProductCategoryKey ID , EnglishProductCategoryName ProductCategory from dbo.DimProductCategory UNION ALL select -1, '<All>' order by ProductCategory asc
I’m using -1 as ID for the "All" record because I know -1 will never exist in the database. And I’ve included < > characters in the "All" label for two reasons: to nicely distinguish it from the other items as being a bit special and it ends up on top of the list when sorted alphabetically! Neat huh?!
This also changes how you set the default parameter selection to "All": just set it to –1:
Taking the "<All>" selection into account
Alright, the next step is taking the "All" parameter item into account in the dataset that retrieves the subcategories. Here’s what the WHERE clause will need to get changed to:
where cat.ProductCategoryKey in (@ProductCategory) or -1 in (@ProductCategory)
In case of a single-value parameter there’s no need to use the IN operator. For those situations the WHERE clause can be simplified to this:
where cat.ProductCategoryKey = @ProductCategory or @ProductCategory = -1
Detecting if "All" is selected in an expression
The expression to check if "All" is selected also changes, we no longer need to use the COUNT function. Instead, we can use the Array.IndexOf VB function:
=IIF(Array.IndexOf(Parameters!ProductCategory.Value, -1) > -1 , "all selected", "not all selected")
How does this work? Because ProductCategory is a multi-value parameter, its .Value property returns an array of selected items. The second parameter of IndexOf is the value we’d like to search for. As our report parameter type is integer, this needs to be an integer as well.
When the item is not found in the array IndexOf returns -1, otherwise it returns the zero-based index position of the item.
Of course, in case of a single-value parameter this check can be simplified to the following:
=IIF(Parameters!ProductCategory.Value = -1 , "all selected", "not all selected")
This article demonstrates how to implement a custom "All" item for both single and multi-valued report parameters meant for filtering data. It also shows how these values can be used in report expressions.