Search code examples
reporting-servicesssrs-2008ssrs-2012

SSRS Expression Issue


I have a column sg1 with date as string and may have some extra characters like

25/10/18 (M) 
25/06/15 (P) this is planned.

My date format is DD/MM/YY. I have to colour the cells based on cell value. I have written the background colour expression for the column. The last condition is not working and throwing error.

=IIF(Fields!sg1.Value = "W","Khaki",
IIF(Fields!sg1.Value="g","Gray", 
IIF(Fields!sg1.Value="b","Blue",
IIF((LEN(Fields!sg1.Value)=12 And
Format(LEFT(Fields!sg1.Value,8),"YY-MM-DD")<Today),"Pink", 
"Green"))))

I am not able to get what is the issue.


Solution

  • This is more complex then I thought. I tested it myself and this way it should work:

    =IIF(Fields!sg1.Value = "W","Khaki", 
     IIF(Fields!sg1.Value="g","Gray", 
     IIF(Fields!sg1.Value="b","Blue", 
     IIF((LEN(Fields!sg1.Value)=12 And 
     DateSerial(Mid(Fields!sg1.Value, 7, 2),
                Mid(Fields!sg1.Value, 4, 2), 
                Left(Fields!sg1.Value, 2))  
                <Today),"Pink", "Green"))))
    

    Where from 25/10/18 is Mid(Fields!sg1.Value, 7, 2) = 18 (YY), Mid(Fields!sg1.Value, 4, 2) = 10 (MM) and Left(Fields!sg1.Value, 2) = 18 (DD).