Tuesday, March 22, 2011

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.

I will explain below the way I do it and I find easiest. 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


Cheers!!

4 comments:

Sangita Pillay said...

Hello Rahul, Thanks for posting this solution. I am trying to do the same thing but my main query comes from a stored procedure so I am not sure how to add step 3c. Any ideas?

Sangita

Rahul Kumar said...

@Sangita: that should not be a problem..
you would need pass @Grade as parameter to your stored proc and use it in the query inside the Stored proc

Leslie Lim said...

First time I commented in a blog! I really enjoy it. You have an awesome post. Please do more articles like this. I'm gonna come back surely. God bless.

Rica
www.imarksweb.org

Vignesh Adhi said...

cheeers ..........

Post a Comment