Reporting Services 2005 Filter

0 votes
asked Apr 13, 2009 by ken-burkhardt

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.

4 Answers

0 votes
answered Jan 17, 2009 by rob1n

Yes there is. Open the table properties for the table or matrix with the data. There's a 'filters' tab there allowing you to filter data after the query has been executed.

0 votes
answered Apr 13, 2009 by msvcyc

You should modify your stored procedure to accept parameters like type and name and then from your report you should invoke this stored procedure with the right values based on user requirements. Reporting services reports do have a feature called report parameters. Therefore, you should convert your report to accept the type and name parameters from the user & pass this onto the stored procedure. To create multiple reports for multiple users, you will just create multiple linked reports from this one template report with different parameter values.

0 votes
answered Apr 14, 2009 by adolf-garlic

Change proc to be

SELECT 

xxx

FROM

xxx

WHERE (a.id = @aID OR @aID IS NULL)
AND   (b.id = @bID OR @bID IS NULL)
AND   (c.id = @.....

Just pass in either a value or NULL from the report

0 votes
answered Apr 15, 2010 by john-s

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.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...