The "Select ALL" parameter option [SSRS]

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:

Selecting all parameter values by default

When rendering the report we now indeed see a dropdown list with a (Select All) item:

image

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:

Setting the parameter default selection to the custom "All" item

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")

Checking if the "All" item is selected in a multi-valued parameter

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")

Conclusion

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.

Have fun!

V.

Share

Tags: , , ,

  1. Nagaraju Korrapati’s avatar

    I want to Remove the “Select All” Option from Drop down. Is It possible to remove from drop down?. Where we are getting from the “Select All” Option in Report?. Otherwise , can we change the Name “Select All” Option to “All Categories” in the Parameters Drop down?. Is it possible to change it?. Appreciate for immediate help.

    Reply

    1. Valentino Vranken’s avatar

      Unfortunately that’s not possible: when you activate the Allow Multiple Values checkbox you’ll get the (Select All) item and there’s no way to influence it differently. I also don’t know of any way to change that text into something else…

      Reply

  2. Emmanuel’s avatar

    How can you automatically exclude say the last values from a multi-valued parameter selection

    Reply

  3. JK’s avatar

    Where to add that expression?

    Reply

    1. Valentino Vranken’s avatar

      The expressions in this article can be put in a textbox and will result in one of the two strings getting displayed.

      Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved