Saturday 2 August 2014

Filtering on SQL Server Reporting Services datasets


I’ve been building reports in SSRS for about five years and almost always do all filtering in the SQL WHERE clause.  Over the last week I’ve been refining a report and needed an additional filter that would have been challenging within the query and in the process of investigating an alternative approach I came across dataset filters in SSRS.

As mentioned, I normally do all filtering in the SQL WHERE clause.  My rationale is that it reduces the size of the amount of data returned and is therefore more efficient and quicker.  The report I am currently working on returns information about millions of invoice rows from four tables all via LEFT OUTER JOINS (to ensure I capture all invoices).  One of the required filters is on a field not in the left outer most table and needs logic to handle the prospective nulls returned.

Rather than rewriting the query to handle this, and risking the integrity of the dataset, I found Dataset filters in the SSRS report itself.  By right clicking on the dataset and selecting Properties there is a Filter tab:

 
This offers a very flexible way of controlling the returned data much like a SQL where clause.  Some advantages are:

·         It maintains the query’s integrity.  Sometimes I build a query and verify the data with the business.  Then I am then asked to add additional constraints and these additional WHERE clause conditions then break the integrity of the data returned.  Since the filter doesn't alter the underlying query, the integrity should remain in tact

·         The filter is extremely flexible.  Like a WHERE clause it can operate using a report parameter with all the usual SQL operators



·         SSRS expressions can offer additional flexibility similar to a HAVING clause and nested clauses

·         An expression returning a boolean TRUE / FALSE offers similar functionality to an EXISTS (and NOT EXISTS) clause
 
·         It can be implemented very quickly in comparison to rewriting underlying queries and then revalidating data.

I don’t think I’ll stop filtering within the SQL WHERE clause where I can, but it is a great feature and certainly offers flexibility in filtering data in a SSRS report.
 
Struan Hijner – Infrastructure Services Practice Manager
www.myriad-it.com

No comments:

Post a Comment