Reporting Services / Supporting robust filtering

Question

I'm looking for sort of a 'best practice' way to tackle this common scenario. I think the question is best asked with an example. Let's assume the following:

• The goal is to write an 'order summary' report which displays a listing of orders based on various filtering criteria.
  • Example: The user wants to report on all orders created between X and Y dates
  • Example: The user wants to report on all orders with the status of 'open'
  • Example: The user wants to report on all orders generated by another user XYZ
  • Example: The user wants to report on all orders between $1000 and $10000

• These reports will likely be launched from different pages, but perhaps there might be an 'advanced search' page which allows them to check/uncheck filters and define parameters

• I want to use remote processing to generate the report

Creating a single report with all of these filters implemented via report parameters and report filters becomes cumbersome and unmaintainable VERY quickly. This leads me to believe that I should create a single stored procedure which accepts all of the possible filter values (and a NULL if the result set should not be filtered by the parameter).

Do you agree with this assessment?

If so, I am not a TSQL expert and would like to have some general advice on how to implement this stored procedure. So far I am doing it like this:

1. Create a table variable of orderID @resultset
2. Populate @resultset initially via the first filter (i chose start and stop date)
3. For each filter:
   • If the filter is defined, create a table variable @tempresultset and insert all records from @resultset WHERE (filter is applicable)
   • Delete from @resultset, insert into @resultset select orderid from @tempresultset
4. return the @resultset after all filters have been applied

This just doesnt feel right / efficient... Is there a better way to approach this?

Any other suggestions or advice on how to approach this general problem would be greatly appreciated. I feel somewhat lost on the proper way to implement this solution to what seems like should be a very common problem.

Answer 1

After some researching I've found a good way to implement these optional filters within a single select statement within a stored procedure:

It looks something like:

SELECT ordernumber FROM orders --Filter #1 - based on Parameter #1 WHERE (@param1 IS NULL) OR (somefield = @param1) --Filter #2 - based on Parameter #2 AND WHERE (@param2 IS NULL) or (somefield2 = @param2) --Filter #3 - based on Parameter #3 AND WHERE (@param3 IS NULL) or (somefield3 = @param3)