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:


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


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!



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.


    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…


  2. Emmanuel’s avatar

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


    1. Dan’s avatar

      You would need another query that pulls the IDs of the items you want selected, then use that query as the “Default Values” on the Parameter


  3. JK’s avatar

    Where to add that expression?


    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.


  4. Carl’s avatar

    Another way to do this without writing all this code…

    On the Dataset Properties under Filters
    Expression is [Parameter]
    Operator is [IN]
    Value is [@Parameter] – This is very important. This eliminates adding any expression. All you need to do is add the Parameter in brackets with the @ sign and you can use the (Select All) in the drop down without adding the in the expression.


    1. Warren’s avatar

      I like this thought. Optimizing the Select to just pull it all for that parameter and then filter them out. Of course this assumes your not doing any complicated counts or totals in your proc. This can work for most simple pulls, just pushes wasted bytes down the pipe…


      1. Valentino Vranken’s avatar

        That method does indeed minimize code writing. But as you mentioned: careful when/where you use it or the report will be pulling whole tables over the network while it only needs to display a minimal part of the data.


  5. Gangadhar’s avatar

    Nice article.


  6. Sri’s avatar

    Is there any way to just display Select All but no the values. more like a user friendly manner.


    1. Valentino Vranken’s avatar

      Could you clarify what you mean? Display were exactly?


  7. agunawan’s avatar

    Hello, thank you for your article. One question, if I may. Where do I put the expression to uncheck the rest of the option when is selected?
    I mean this script:
    =IIF(Array.IndexOf(Parameters!ProductCategory.Value, -1) > -1 , “all selected”, “not all selected”)

    Or, did I misunderstand? This article will still allow users to check and other item(s)


  8. Gustavo’s avatar

    Thanks for the info. My issue is how to add the “Select All” to a drop-down for a single choice (instead of multiple choice).


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