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.


    2. Naz’s avatar

      I did for my report, it didn’t work.

      Expression : =Parameters!MachineGroup1.Value(0)
      Operator : in
      Value : =Parameters!MachineGroup1.Value(0)


  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)


    1. Dan’s avatar

      Short answer is, you can’t. The “Select All” option that actually selects all the check boxes is built into SSRS. So the “All” option is just another selection in the list and can’t change the other selection options.
      We use the “All” option for automated reports. Since the list is dynamic, you can’t select the options when setting up the schedule. Having the “All” option get around not having the ability to do a “Select All” when creating the schedule for the report.


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


    1. Dan’s avatar

      Try adding
      “UNION SELECT 0, ‘Select All’ ”
      to your query that pulls the values. Then, in the report data query, assume that a zero for this parameter means you don’t filter on it.


      1. Naz’s avatar

        If I didn’t use option “Get values from a query” and use “Specify Values” in available vales instead, how can I return all rows by checking “Select All”?


    1. Valentino Vranken’s avatar

      It’s your right to disagree with something but I don’t see how your link is related to my article?


      1. Dan’s avatar

        Yeah, that link has nothing to do with this article.


  9. Benicio Galvez’s avatar

    I was enthusiastic at first by reading this article but then taken aback by the implementation which leaves users still with the “Select All” option and its behavior. It appears that this workaround is cosmetic and it’s partial since it leaves the “problem child” still in the picture.


  10. Hareesh Madala’s avatar

    So, It just adds ” value in single valued parameter right. Just like any other value ‘NULL’. I’m looking for something which selects all values and Filter the data when we set ALL as default value in Single valued Parameter.


  11. Firoz’s avatar

    Thanks a lot, it worked.


  12. Naz’s avatar

    If I don’t want to add any code to my query, how can I do it by filter? Thanks


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