Select ALL in parameter of SSRS report

Select ALL as parameter value is one of the most common functionality which most of the reports have and there are number of ways to implement it.

The driver of the solution is CASE option under WHERE clause of SELECT DataSet Query.

        SELECT * FROM TableName
         WHERE
         (
               CASE
                      WHEN  @RepParam <> 'ALL' AND ColName= @RepParam THEN 1
                      WHEN  @RepParam= 'ALL'  THEN 1
               END
         ) = 1 ;

Lets see it through an simple Student Table example where we will Select student either on the basis of the Grade they are in or select all of them.

1. Records in Student table are

2. Create a simple report with DataSet Student as SELECT * FROM Student;

3. To add the Grade parameter and option for Select ALL

3.a. Create a DataSet for available Grades for Report parameter

3.b. Configure a Report parameter "Grade" as

3.c. Modify query for Student Dataset  to allow filtering on Grades as

4.a. Run the Report for ALL grades

4.b. Run the Report for grade - X