Reporing Services 2005 Filter Question

Question

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.

Any ideas?

Requirements Clarificarion 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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.