Conditional formatting based on number of days in SSRS against a cell value in reference to another field

Question

I have a SSRS report where I have cell displaying a field called activity_date. I would like this to be colour coded based on a number of days against another dataset field called valid_to where if it is greater than 14 days it is green, within 14 days it is yellow and if it is later than the valid_to date it is red. Can any one help me?

I have made some changes to the requirements:

RED     valid_to is prior to today AND cell_value is null
ORANGE  valid_to is prior to today AND cell_value is NOT null
YELLOW  valid_to is between today and 14 days ahead of today and cell_value is null
BLUE    valid_to is in the future and cell_value is NOT NULL
GREEN   valid_to is 15 or more days ahead of today and cell_value is NOT NULL

Answer 1

You will need to come up with an expression for your fill color of that cell or field you want to change. So an example:

The basic setup is I just have 2 variables to enter a date and then a text box on the body of the report I will adjust the fill color based on the days between those two dates:

So I go into the text box properties and find the Fill page, I will set the "Fill Color" to this expression:

=IIF (DateDiff("d", Parameters!activity_date.Value, Parameters!valid_to.Value) > 14
,"Green","Yellow")

Then my result:

In your case you will just build out the "IIF" to follow the criteria you have for your formatting.

This is just a guess from doing it in my head (and writing it out in Notepad++) but I think this should be what you are looking for:

=IIF(Fields!valid_to.Value = Today() AND IsNothing(Fields!cell_value.Value) = 1, "Red",
    IIF(Fields!valid_to.Value < Today() AND IsNothing(Fields!cell_value.Value) = 0, "Orange",
        IIF(Fields!valid_to.Value = Today() AND DateDiff("d",Today(),Fields!valid_to.Value) <= 14, "Yellow",
            IIF(Fields!valid_to.Value > Today() AND IsNothing(Fields!cell_value.Value) = 0, "Blue",
                IIF(DateDiff("d",Today(),Fields!valid_to.Value) >= 15 AND IsNothing(Fields!cell_value.Value) = 0, "Green","White")
                )
            )
        )
    )