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:
·
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.
No comments:
Post a Comment