reporting-servicesreportbuilder3.0reportbuilderpowerbi-paginated-reports

Report Builder Expression for Cell Conditional Formatting

In report builder I want to add conditional formatting to a column in a table, the table's main dataset is from the Track table.

The column is created from a Lookup expression: RFH_DUE IIf(IsNothing(Fields!RPR.Value), DateAdd(DateInterval.Day, 180, Lookup(CInt(Fields!P_ID.Value), CInt(Fields!ID.Value), CStr(Fields!NPR.Value), "Prov")), DateAdd(DateInterval.Day, 180, Fields!RPR.Value))

For the conditional formatting I want to change the fill color Finished: Green Due Within 30 Days: Red Due Within 90 Days: Yellow

Finished: If the RFH_FILE is not null or PRRB is not null or NOTES like “missed” Due Within 30 Days: If not Finished and the RFH_DUE – Today <= 30 Due Within 90 Days: If not Finished and the RFH_DUE – Today between 31 and 90

What is the correct syntax to do a nested IIf or is there a better solution? I have started with:

Finished IIF(IsNothing(Fields!RFH_FILED.Value) or IsNothing(Fields!PRRB.Value) or Fields!NOTES.Value Like "*missed*", "Transparent", "LimeGreen")

But I really need NOT IsNothing, but that doesn't work. I am also having difficulty nesting all the IIf statements to get all three color fills and haven't had luck using SWITCH.

Solution

  • A switch is definitely the way to go:

    =switch(isnothing(Fields!FRH_FILED.Value) = FALSE, "Green", isnothing(Fields!PRRB.Value) = FALSE, "Green", instr(Fields!NOTES.Value, "missed") > 0, "Green", (isnothing(Fields!FRH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day, Fields!RFH_DUE.Value, Today()) <= 30, "Red", isnothing(Fields!FRH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day, Fields!RFH_DUE.Value, Today()) > 30, "Yellow", 1=1, "Blue")

    You can use the 1=1 as an else in a switch, so if it's coloured blue then you've missed all the conditions and you can go investigate.