StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReporting Services / Supporting robust filtering
text
Body
copied!<p>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:</p> <ul> <li>The goal is to write an 'order summary' report which displays a listing of orders based on various filtering criteria. <ul> <li>Example: The user wants to report on all orders created between X and Y dates</li> <li>Example: The user wants to report on all orders with the status of 'open'</li> <li>Example: The user wants to report on all orders generated by another user XYZ</li> <li>Example: The user wants to report on all orders between $1000 and $10000</li> </ul></li> <li><p>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</p></li> <li><p>I want to use remote processing to generate the report</p></li> </ul> <p>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).</p> <p>Do you agree with this assessment?</p> <p>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:</p> <ol> <li>Create a table variable of orderID @resultset</li> <li>Populate @resultset initially via the first filter (i chose start and stop date)</li> <li>For each filter: <ul> <li>If the filter is defined, create a table variable @tempresultset and insert all records from @resultset WHERE (filter is applicable)</li> <li>Delete from @resultset, insert into @resultset select orderid from @tempresultset</li> </ul></li> <li>return the @resultset after all filters have been applied</li> </ol> <p>This just doesnt feel right / efficient... Is there a better way to approach this?</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload