Bojensen Blogs

How to Default to ALL in an SSRS Multi-select Parameter » The Data Queen

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.

Add ‘ ALL’ to your Layout_Group parameter with a Union clause and add an order by

Edit your main query where clause to use @TopLayoutCode . Adjust the AND/OR in the WHERE clause accordingly.

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

How to Default to ALL in an SSRS Multi-select Parameter » The Data Queen

Comments are closed.