User Profile
S_Berg
Frequent Visitor
Joined 5 years ago
User Widgets
Contributions
Report Builder - Conditional Background for a Field Using SWITCH
In report builder I want to add conditional formatting to a column (RFH_DUE_DATE) in a table, the table's main dataset is from the Tracker dataset. I created RFH_DUE_DATE from the Provider dataset. The column is created from a Lookup expression: RFH_DUE_DATE= 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_FILED is not null or PRRB_INDIVIDUAL_CASE__ is not null or NOTES like “missed” -Due Within 30 Days: If not Finished and the RFH_DUE_DATE – Today <= 30 -Due Within 90 Days: If not Finished and the RFH_DUE_DATE – Today between 31 and 90 Here is my expression for the background fill: =switch(isnothing(Fields!RFH_FILED.Value) = FALSE, "Green", isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = FALSE, "Green", instr(Fields!NOTES.Value, "missed") > 0, "Green", (isnothing(Fields!RFH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day,IIf(IsNothing(Fields!RNPR.Value), DateAdd(DateInterval.Day,180,Lookup(CInt(Fields!Provider_ID.Value),CInt(Fields!ID.Value),CStr(Fields!NPR.Value),"Provider")),DateAdd(DateInterval.Day,180,Fields!RNPR.Value)), Today()) <= 30, "Red", (isnothing(Fields!RFH_FILED.Value) = TRUE OrElse isnothing(Fields!PRRB_INDIVIDUAL_CASE__.Value) = TRUE OrElse instr(Fields!NOTES.Value, "missed") = 0) AndAlso datediff(DateInterval.Day, IIf(IsNothing(Fields!RNPR.Value),DateAdd(DateInterval.Day,180,Lookup(CInt(Fields!Provider_ID.Value),CInt(Fields!ID.Value),CStr(Fields!NPR.Value),"Provider")),DateAdd(DateInterval.Day,180,Fields!RNPR.Value)), Today()) > 30, "Yellow", 1=1, "Transparent") When I run the report the entire column incorrectly turns all green, I cannot get any of the other colors in my expression to work. How can I correct this expression?Re: Filter multiple date fields with 1 slicer
Hi Greg_Deckler, Thank you for your response! I want to Create a tracker like this: Here is some sample data: Project Added Deadline Lead Category Status Finished A 1/10/2023 Person 1 Infrastructure 5/12/2023 B 2/24/2023 Person 2 Response 7/5/2023 7/5/2023 C 2/1/2023 3/31/2023 Person 3 Customer 6/23/2023 D 3/31/2023 11/17/2023 Person 4 Admin 5/12/2023
