SSRS Expression Evaluation Issue

Question

I'm having an issue with expressions within reports. I'm coloring the background of a textbox within a table depending on the value within it. The text in the field relates to backups for a SQL Server. The value is either a date or the text "Not Yet Taken". If the date is more than 2 days old, I want the background to be yellow. If its more than a week old or if the date value is "Not Yet Taken", I want the background to be red. Otherwise, it'll be green.

The problem I've been having since I started with reports for SSRS (a few weeks ago) is that my expressions seem to get fully evaluated. An IF statement will have both its true and false values evaluated even though only one of them will be used.

This becomes a problem because "Not Yet Taken" is clearly not a date and to work with the dates I need to convert the date string into a date. Here is the code I have currently:

=IIF(Fields!LastBackUpTaken.Value = "Not Yet Taken","Red", IIF( IsDate(Fields!LastBackUpTaken.Value) = true,
  IIF( CDate(Fields!LastBackUpTaken.Value).AddDays(Parameters!DaysTillExpiry.Value).CompareTo(NOW()) = 1,
       "GreenYellow",
   IIF( CDate(Fields!LastBackUpTaken.Value).AddDays(7).CompareTo(NOW()) = 1, "Yellow", "Red")),
  "Red"))

So basically, the expression reads "If LastBackUpTaken.Value = "Not Yet Taken", return the color Red. If it isn't "Not Yet Taken", check to see if the string is a date. If it isn't a date, return the color Red. If it is a date do calculations and return the appropriate color.

This expressions works for all the text fields that don't have "Not Yet Taken" as its text. For the fields that do have "Not Yet Taken" as its text do not have any color set.

EDIT: I also get a conversion error that I forgot to mention, whenever the text is "Not Yet Taken"

Any ideas?

Answer 1

Write a VB function to return the color string in the Code Tab of the Report Properties. Here you can use language constructs you are comfortable with (case statements, regular if statements, etc.). Logic with be easier to read back as well.

Public Function GetBackgroundColor(ByVal DateString as String) As String
    'plain old vb syntax here
End Function

In the expression for the background color property:

=Code.GetBackgroundColor(Fields!LastBackUpTaken.Value)