Jun 172011
 

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

  20 Responses to “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”

    • 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!

  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

    • 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

    • 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, “‘,’”) + “‘)”

    • 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

    • 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

  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.

    • 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

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

    • 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

  10. Thanks !!

    it worked succefully for me .

Leave a Reply

Wordpress SEO Plugin by SEOPressor
%d bloggers like this: