Using SWITCH statement in reporting services for a date field

Question

Hi All,

I am trying to get this code to work in reporting services 2003:

 =switch(
    DateDiff("d", fields!WARRANTY_EXP!value,now()) > 765, "White",
    DateDiff("d", fields!WARRANTY_EXP!value,now()) > 365, "Yellow",
    DateDiff("d", fields!WARRANTY_EXP!value,now()) > 0, "Red",
    DateDiff("d", fields!WARRANTY_EXP!value,now()) < 0, "Gray"
    )

I'm trying to code a field so that if the date field is in the past it changes the background colour to grey, if it's going to be in the next year then red, and in the next 1-2 years yellow. Everything else white.

I've tried rearranging it but the coding must be wrong somewhere as greater than 2 years is grey, dates in the past are yellow and next year is red :-S

Many thanks,

Pierce

Answer 1

I think you may just have your greater than and less than signs the wrong way round, or your now() and field expression the wrong way round.

Instead of

  DateDiff("d", fields!WARRANTY_EXP!value,now()) > 0, "Red",

Try

 DateDiff("d",now(), fields!WARRANTY_EXP!value) > 0, "Red",

# An Example from the online help:- Test the value of the ImportantDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item: Copy

=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>7,"Red","Blue")