SQL Reporting Services Snippets - Expressions
Author: jason
Date: 2008-04-08 12:01:26
Category: Technical

I've run into a couple situations with writing reports for SQL Reporting Services that I thought I'd share. I'm writing up a quick install and configuration guide for SQL 2005 Reporting Services as well, but that will have to wait.

The first thing I ran into was trying to look for a specific date range in a SQL query. I wanted to limit my query to fields with a date in the next 45 days. In the Data window here's the code that goes after the WHERE in the SQL statement. The SQL field is less than or equal to function NOW() plus 45.

(award_sch <= {fn NOW()}+45)

The next thing I wanted to do was color the background of a cell a certain color based on the values of a SQL field. In the Layout window go into the properties of a cell, click on BackgroundColor, select Expression.

Here's the coding in the BackgroundColor expression. If the value of the SQL field contains a slash then the background is White. If not, if the value of the second field has a slash then the background is PeachPuff. Since the code is in the background expression no other coding is needed, i.e. no background.cell("blue") or whatever...

=SWITCH(Instr(Fields!award_act.Value, "/"), "White", InStr(Fields!award_sch.Value, "/"), "PeachPuff")

Then I was thinking, I really want to know if the SQL field is empty/null. So here is the statement: if the field value has a slash in the string the background is White, if the field value ISNOTHING then it's PeachPuff.

=SWITCH(Instr(Fields!award_act.Value, "/"), "White", ISNOTHING(Fields!award_act.Value), "PeachPuff")

Here's the report with the coloring

jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009