Reporting Services 2005 Filter

reporting-services

Is there a way to do "and" "or" filters in SSRS 2005?

I have a table pointing to a dataset (stored procedure) that looks like this:

    name                       type                 amount 
License Plate Credit           fees                ($150.00) 
Lieu Tax                       fees                $1,012.12 
Finance Tax City               taxes               $1,839.90 
Finance Tax County             taxes               $306.65 
Finance Tax State              taxes               $3,434.48

The user would like to see all rows with:

  1. type = 'taxes' or
  2. type = 'fees' and name = 'Lieu Tax'

The reason I need to do this in the report and not in the stored procedure is because
we will be creating multiple reports pointing to the same stored procedure depending on
how each client wants to lay out the display and business rules.

Requirements Clarification
I was hoping there was a way to do it in the report instead of the proc. The plan is to have many custom reports pointing to the same proc with different requirements. The idea was for report builders (who don't know SQL) to create the reports instead of us programmers always having to get involved.

Best Answer

Each possible condition combination is either a pass or a fail. You use a SWITCH to evaluate each possible condition, and return a 1 or a 0. Then you use an "=" and "=1" in the filter condition.

=SWITCH (TYPE = "TAXES", 1, TYPE = "FEES" AND NAME = "Lieu Tax"), 1, 1=1, 0 )

You can handle your entire filtering in a single expression this way. Works like a charm.

Related Topic