How to Default to ALL in an SSRS Multi-select Parameter

This seems like it should be easy, but SSRS has nothing built in to allow you to do this.  I searched an couldn’t come up with a solution that fit all my requirements for a report. There are ways to default the selection list to every value in the list, but I needed to add the option of ALL to my parameter list, default to ALL, and then most importantly have ALL show up in my header instead of showing every item in the list.  I was determined to find a way, and here is how I did it.

In this example I would like to allow the report to default to ALL Layout Codes, rather than selecting every Layout Code in the list.  there is already a multi-select parameter in the report called Layout_Code, and the main query filters where Layout_Code IN (@Layout_Code)

1. ADD ‘ ALL’ TO YOUR PARAMETER QUERY

The first thing to do is add the value ‘ALL’ to your parameter query.  You’ll want it show up at the top of your select list.  A simple way to do this is to put a space before the A to make it sort to the top.

SELECT DISTINCT Layout_Code
FROM         Your_Table
UNION
SELECT     ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

2. EDIT YOUR MAIN QUERY TO USE BOTH PARAMETERS

Edit the where clause in your main query.  If you are using Query Designer in Text mode, simply add this statement.  Be sure to use outer brackets to contain both statements if you have other items in your where clause.

WHERE (‘ ALL’ IN (@Layout_Code))    OR     (Layout_Code IN (@Layout_Code)) )

If you have multiple parameters in your main query are using Query Designer in the View mode rather than Edit Text mode, your WHERE clause will have to return all combinations using AND and OR.  Let Query Designer build your query for you by using outer brackets and replace:

(Layout_Code IN (@Layout_Code))  

with

(   (‘ ALL’ IN (@Layout_Code))    OR     (Layout_Code IN (@Layout_Code))   )

I suggest you have your WHERE clause already complete before adding the OR, since the results can get confusing. 

3. CASCADING PARAMETERS WITH ‘ ALL’

You can use this method for cascading parameters where multiple parameters have the ALL value added.  You’ll need to add the OR clause to your underlying parameter queries where applicable.

For example, if you have a parameter preceding the Layout_Code parameter called Layout_Group you will follow the same steps above, plus an additional step to edit the Layout_Code dataset query.

  1. Add ‘ ALL’ to your Layout_Group parameter with a Union clause and add an order by
  2. Edit your main query where clause to use @TopLayoutCode . Adjust the AND/OR in the WHERE clause accordingly.
  3. Edit your cascaded parameter query, in this case Layout_Code, to OR in the where clause.  Adjust the AND/OR accordingly.

Your main query where clause will look like this in the Query Designer View mode:

WHERE  �
(‘ ALL’ IN (@Current_Layout_Group)) AND (‘ ALL’ IN (@Layout_Code)) OR
(‘ ALL’ IN (@Current_Layout_Group)) AND (Layout_Code IN (@Layout_Code)) OR
(‘ ALL’ IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group)) OR
(Layout_Code IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group))

Your Layout_Code query will look like this

SELECT DISTINCT Layout_Code
FROM         Your_Table
WHERE     (Layout_Group_Current IN (@Current_Layout_Group)) OR (‘ ALL’ IN (@Current_Layout_Group))UNION
SELECT     ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

4. SET YOUR PARAMETER DEFAULTS TO ALL

Set the default value for each of your parameters, Layout_Code and Layout_Group, to the value ALL.  Be sure to put a space before the A if you are using this method to sort your list in Step 1.

image

37 thoughts on “How to Default to ALL in an SSRS Multi-select Parameter”

  1. Thanks for the info!

    I ran into one problem though, the ALL value wasn’t being selected as the default when viewing via the Reporting Services browser interface. Apparently it doesn’t work (for me anyway) by putting the ALL value directly as you have it above but does as an expression = ” ALL”

    1. Thanks for the feedback, Dave. Not sure why it didn’t work for you. When I type the ALL (with a space in front) it does work for me. Glad you found a workaround.

  2. Martina,

    I need to know how to just default to all the selections in the list or to the “(Select All)” that is already in the selection list of my parameter. Can you help me ?

    thanks!

    2. I found how to set the default to (Select All) with all items checked (re: )

      In the Parameter Properties of the Parameter you want set to (Select All) go to the Default Values tab.

      Select “Get Values from a query”

      Set Dataset to the same dataset your values will be populated from.

      Set Value Field to the same value field your values will be populated from.

      It will now select all by default. I thought it would hang up since there is a parameter in the dataset but it doesn’t, it selects everything in the list. I hope this helps any one else with this issue.

      One point to add – if you find that RS is still not showing your default value as ‘Select All’, check your dataset associatedi with the multi-select valuesfor null values. If that dataset contains a null value, but your report parameter is set to not allow nulls, RS will revert to not selecting any values by default. Fix the DataSet with the ISNULL() operator so that it cannot return a null.

      1. Thanks Buddy you have save my time a lot..its worked for me in simple steps. Thanks a tone. PavanKumar B

      2. The last paragraph was the missing information I needed. I fought hours trying to come up with a solution to the NULL issue. Very Good Job CSMatDSNT!!!

      3. Worked great! However, only the first time I start up the report. Once I change a parameter above it, the “Select all” now only has check marks next to the items that were from before changing the higher up parameter. Essentially, doesn’t update the cascading parameter.

        Any thoughts on that?

      4. Thanks a lot. this was really useful, especially your last point-
        One point to add – if you find that RS is still not showing your default value as ‘Select All’, check your dataset associatedi with the multi-select valuesfor null values. If that dataset contains a null value, but your report parameter is set to not allow nulls, RS will revert to not selecting any values by default. Fix the DataSet with the ISNULL() operator so that it cannot return a null.

      5. Dear CSMatDSNY,

        the NULL problem was exactly what I struggled with the last days. THX A LOT FOR THAT HINT! You saved my day 😀
        Regards Verena

  3. I am unsuccessfully trying to do this in DB2. Here is my code chunk (it’s part of a function, which you need to use for multi-value parameters in DB2, ugh):
    “(UR.USER_NAM IN (‘” + JOIN(Parameters!User.Value,”‘,'”)+”‘) OR ‘ALL’ IN (” + Parameters!User.Value +”))”

    ANY help would be greatly appreciated!
    Chris

    1. I’m no expert in DB2, but I think the problem lies with the quotes around the comma in your JOIN function. The double quotes are likely being interpereted by the statement as opening quotes for the next literal. Try remove the “” from around “‘,'” like this:

      “(UR.USER_NAM IN (‘” + JOIN(Parameters!User.Value,’,’)+”‘) OR ‘ALL’ IN (” + Parameters!User.Value +”))”

      IF that doesn’t work you may have to build the join statement in a new parameter and then reference the new parameter in your statement.

  4. Hello,

    I have followed the above and when I run my two data sets stand alone I get data back, but when I run them in the preview window, I do not get any results.

    Main Query:
    select a.techid,a.callnbr,a.entdte,a.custnmbr,a.address1,a.address2,a.city,a.state,c.refrence,a.svcdescr from bsv_svc00200 a (nolock)
    join bsv_svc00201 b (nolock) on a.callnbr = b.callnbr
    join svc00300 c (nolock) on b.equipid = c.equipid where a.entdte
    between @begindate and @beginenddate and ((‘ ALL’ in (@techid)) or (a.techid in (@techid)))

    Parameter Query:
    select distinct techid from svc00100 (nolock) where techid like ‘%HD’ union select ‘ ALL’ as techid

    1. Hi,

      They queries look good, and you say they each return results. When you say you run it in the Preview window, do you mean the Data Set window or the report Preview mode in SSRS?

      If it’s the data set, you must input a valid value for the parameter when prompted – ideally ‘ ALL’ (no quotes) since presumably that is what you are testing.

      If you are referring to the report Preview tab, did you apply the Parameter dataset to the Parameter as available values? Do you see anything in the parameter dropdown? Did you set the parameter default to ‘ ALL’ (no quotes) or leave it blank? If it’s blank you’ll need to select a value from the drop down and then click on the View Report button.

      Cheers,
      Martina

  5. Hi,
    I am new to SSRS—please help

    Source is SYBASE,
    Below query is not working…not sure what is wrong..

    =” SELECT tu.*, “‘location'”=l.name , “‘department'”= d.name
    FROM common.dbo.tudor_user tu, common.dbo.department d, common.dbo.location l
    WHERE tu.department_id = d.department_id AND tu.location_id = l.location_id and d.name in(‘”+Join(Parameters!departmentName.Value, “‘,'”) + “‘)”

    1. Hi Suresh, I don’t work with Sybase, but i would suspect it doesn’t like the format of the Join(Parameters.. statement. Try subbing in a hard coded list first and get your query working before sorting that out.
      Cheers,
      Martina

  6. Really This post helped me a lot ,but I have quick question if there are 5 parameters then in mail query and subquery I need write all combinations like
    ALL in @para1, ALL IN @para2,ALL In @para3
    ALL in @para1, Column IN @para2,Column In @para3
    ALL in @para1, ALL IN @para2,Column In @para3
    ….
    ….
    …..
    Like this
    is there any other way that can make life easy

    1. I would use brackets in the query, so you don’t have to do all combinations. Example:

      WHERE (Column IN (@para1) OR ALL IN (@para1))
      AND (Column IN (@para2) OR ALL IN (@para2))
      AND (Column IN (@para3) OR ALL IN (@para3))

      Cheers,
      Martina

      1. Hi, what if you need to add a cascading to the code where the cascading is already set-up within the parameter, but not the main code?

        WHERE (Column IN (@para1) OR ALL IN (@para1))
        AND (Column IN (@para2) OR ALL IN (@para2))
        AND (Column IN (@para3) OR ALL IN (@para3))

  7. I have cascading parameters where i want to add a value “ALL” as select all option is not working when report is subscribed. The first parameter has label and value, I would like to know what should be the given in value column when I union a row “ALL” for label column.

    1. The value is what will be used in the main query, not the label. You will want the value to be ‘ ALL’, unless your values are numeric, in which case you will want to use -1 (if it doesn’t interfere with existing values) and adjust your main query where clause to look for -1 instead of ‘ ALL’.

  8. The fact is that if you set the default values equal to the same query that supplies the list of values it works by default with no code

    1. That is true, Dale, but in the outlined requirements I wanted specifically to choose a single value to represent ALL. “There are ways to default the selection list to every value in the list, but I needed to add the option of ALL to my parameter list, default to ALL, and then most importantly have ALL show up in my header instead of showing every item in the list”

  9. Hello, Thank you for the post. I tried it and it worked. But one thing is the parameter I am working on is dependent on date range parameters. Like when I change the date range, the Policyname parameter changes for that date range and I still want the report to show all the options to be ticked. Is there a way to accomplish that? Thanks in advance. I am working on SSRS in Visual Studio 2012.

    1. Hi Manasa, From your description it sounds like you have a cascading parameter, and you would like the prefiltered list of Policynames to be passed to your main query. If you really have the requirement to allow for the value of ‘ ALL’ in your parameter dropdown, I would probably create an additional hidden parameter for the prefiltered Policynames called @PolicyFiltered. Create a dataset for the available and default values for @PolicyFiltered filtered on the date range, and then feed the values of @PolicyFiltered to your main query instead of the value ‘ ALL’.

      Change your main query where clause from:
      WHERE ( ‘ ALL’ IN (@Policyname) OR Policyname IN (@Policyname) )
      to:
      WHERE Policyname in @PolicyFiltered

  11. Hi,

    I am a newbie to MS SQL/creating report using ssrs/report builder 3.0 so please be patient with me especially if my question does not make sense 🙂

    is it possible to create a cascading drop down list parameter even though I have only one table to query? If yes, could you please let me know how?

    The table that I refer to consist of the following Columns:

    Region, Country, City, Street, Department, Software, Firstname, Lastname, CostCenter

    I am hoping to have a Drop down list parameter that will filter by Region then Country then Department…

    The query that I used:

    Select Region, Country, City, Street, Department, Software, Firstname, Lastname, CostCenter
    from Table

    Parameter Setting:

    The data type that I used is Txt and I “Allow Multiple Values”
    Available Values Settings “Get Values from a Query”

    I created a separate Data Set for each Drop Down List Parameter…I am able to filter/make one drop down list work (i.e. Region) but I cannot make the succeeding filter (cascading parameter) work…Hope you can help/guide a newbie like me…

    thanks in advance 🙂

    1. Hi, Yes you can use a single table for this, however you’ll want to create separate queries for each dropdown so you only display a single value. For example a Region will have multiple entries for Canada in your table since the table houses data at a lower level than that. But your Country dropdown should only display Canada once. So you’ll want to SELECT DISTINCT COUNTRY FROM yourtable WHERE REGION = @Region as your query for the available values in the Country parameter. I hope that helps.

  12. Hi All,

    I have an Doubt. I Set the Default value to ALL but my prompt is a float..i am getting error failed to Convert varchar to Float.

    Prompt: SO Number #
    Requirement: It needs to open field as user can search for Order Number and If user won’t select any thing it has to take default as ALL Order Numbers.
    Please help me here.

  13. TDQ – This worked as described and finally resolved an issue I struggled with all day! Thank you very much!

  14. this isn’t working for me. ‘All’ option is checked in my drop down list but when I run the report, it’s not showing up data as All nowhere refers to valid values in my table

    1. You need to add ALL as an option in your main query in the WHERE clause in Step 2 of this post. Specifically WHERE (‘ ALL’ IN (@Layout_Code)) . If a user selects ‘ ALL’ in the dropdown then this part of the WHERE clause will select every line in the main query because ‘ ALL’ in @Layout_Code (the dropdown) will be true for every line returned.

Comments are closed.