Tuesday, December 18, 2007

SQL Server Reporting with Expressions

I wanted to talk about Expressions within the SQL Text of the Dataset in SQL Reporting. Since I don't see any blogs or posts in google on the subject I thought I would shed some light to help you out. Have you ever been tasked with a report that had say 4 filters? Instinctively you would write your SQL and have this massive un-maintainable Where statement that was full of OR clauses right? It may look something like SELECT * FROM TableA WHERE (@StartDate IS NULL AND @EndDate IS NULL AND @Parameter1 = '-1' AND @Parameter2 = 'All') OR (...) which is normal if you didn't know that you could build this sql statement using Expressions. Once this Where clause is done after you have thought out every scenario the user may choose you are left with 100 lines of sql code. Now this may be an over exaggeration but you get the point. To start this off correctly, the best method to begin with is your Select statement. Add your select then execute it. This will have SQL Reporting automatically add the fields for you. Then take your select statement and change it like so (Using same example):

= "SELECT * FROM TableA"
& "WHERE 1=1"
& IIF(IsNothing(Parameters!StartDate.Value), "", " AND StartDate >= '" & Parameters!StartDate.Value & "'')
& IIF(
IsNothing(Parameters!EndDate.Value), "", " AND EndDate <= '" & Parameters!EndDate.Value & "'')
& IIF(Parameters!Parameter1.Value = "-1", "", " AND Parameter1 = '" & Parameters!Parameter1.Value & "'")
& IIF(Parameters!Parameter2.Value = "All", "", " AND Parameter2 = '" & Parameters!Parameter2.Value & "'")

As you can see we are starting off our Where clause to be 1=1 just to alleviate complication, then based on the values of our parameters we are injecting our filters into the where clause. Using this technique you have covered all your scenarios and are now ready to move on.

Creative Commons License
Blogged Information and Code is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.