Issue with empty dates when comparing of two dates in SSRS

Question

I am trying to set cell background in SSRS/Report Builder by comparing two dates, that come from a dataset. The problem is that when either of the dates is empty, it should return white, but I cannot get that part working due to them being dates.

My code is:

Function BackgroundColorFromDates(byVal FirstDate as Date, byVal SecondDate as Date ) As String
return IIF(
    IsNothing(FirstDate) Or IsNothing(SecondDate), 
    "White", 
    IIF(
        FirstDate <= SecondDate, 
        "Red", 
        "Green")
    )
End Function

This is called from the background expression like this:

=Code.BackgroundColorFromDates(Fields!Date1.Value,Fields!Date2.Value)

But if either of them are empty, I am getting red background color instead of white.

Answer 1

I'm not sure you need custom code to do this, you should be able to do it using SWITCH() in an expression.

This is untested but I think it will work, if not let me know and I'll test it later.

= SWITCH(
        IsNothing(Fields!Date1.Value) OR IsNothing(Fields!Date2.Value), "White",
        Fields!Date1.Value < Fields!Date2.Value, "Red",
        True, "Green"
        ) 

SWITCH() stops when it find the first expression that evaluates to true so it should catch the null values first.

---

Edit after OP Reply

---

Function version

This way seems a bit clunky and somebody with better VB.NET knowledge than I have can probably improve this but it seems to work.

First I added a simple function to see what was being passed to the function when a date was null/nothing.

Function StringFromDate(byVal FirstDate as Date) As String
    return FirstDate.ToString
End Function

I added a temporary column to show the results of this function...

It appears that it's not quite null but evaluates to a date of 01-01-0001 . I got the string output from this and then compared to this string rather to Nothing and it seems to work.

Function BackgroundColorFromDates(byVal FirstDate as Date, byVal SecondDate as Date ) As String
    return IIF(
        IsEmptyDate(FirstDate) Or IsEmptyDate(SecondDate), 
        "White", 
        IIF(
            FirstDate < SecondDate, 
            "Red", 
            "Green")
        )
End Function

Function IsEmptyDate (byVal FirstDate as Date) As Boolean
    return FirstDate.ToString = "1/1/0001 12:00:00 AM"
End Function

I tested this against a simple dataset and got the expected results.