Search code examples
ssrs-2008

conditional color coding of a textbox in SSRS Report based on date range


I have below columns in my report body and I need to fill background color in a ProviderNextContactDate textbox based on below condition

ProviderContactDate    ProviderNextContactDate
  2/3/2022               8/3/2022
  1/6/2022               7/6/2022
  11/18/2022             5/18/2022

..

if ProviderNextContactDate is past due then Red
if ProviderNextContactDate is within 30 days then yellow
otherwise transparent

Solution

  • Assuming past due means past on or earlier than today and your colums are Date or DateTime data types, then just set the backgroundcolor property of the textbox to an expression something like this...

    =SWITCH(
            Fields!ProviderNextContactDate.Value >=Today(), "Red",
            DateDiff("d", Fields!ProviderNextContactDate.Value, Today()) <=30, "Yellow",
            True, Nothing
           )
    

    The final 'True' acts like an else. 'Nothing' is the default backgroundcolor for textboxes which is effectively transparent.

    You may need to adjust the numbers of days or the >= and <= depnding on your needs. For example, DateDiff returns the number of boundaries crossed, in this case the number of days (denoted by the "d").