SSRS Report Builder Expression Issue

Question

I have a report builder expression that takes a condition and turns the background cell red on the SSRS report if the condition is true.

Example:  DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) >= 2)

However if the print_date is on a Friday and the ship_date is on a Monday, it should not be counting the weekends. Does anyone know how to exclude the weekends from the count?

=IIf(

    (Fields!location_id.Value = 100 OR Fields!location_id.Value = 200 OR Fields!location_id.Value = 600 OR Fields!location_id.Value = 800 OR Fields!location_id.Value = 1600 OR Fields!location_id.Value = 2600) AND

    (

        DateValue(Fields!print_date.Value) <= DateAdd("d", -1, DateValue(Fields!required_date.Value)) AND

        DateValue(Fields!ship_date.Value) > DateValue(Fields!required_date.Value)

    ) OR

    (DateValue(Fields!print_date.Value) > DateValue(Fields!required_date.Value) AND

     Hour(Fields!print_date.Value) < 15 AND

     DateValue(Fields!ship_date.Value) > DateAdd("d", 1, DateValue(Fields!print_date.Value))) OR

    (DateValue(Fields!print_date.Value) = DateValue(Fields!required_date.Value) AND

     Hour(Fields!print_date.Value) >= 15 AND

     DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) >= 2) OR

    (DateValue(Fields!print_date.Value) = DateValue(Fields!required_date.Value) AND

     Hour(Fields!print_date.Value) < 15 AND

     DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) > 1),

    "Red",

    IIf(

        Fields!location_id.Value = 2500 AND

        (

            DateValue(Fields!print_date.Value) <= DateAdd("d", -1, DateValue(Fields!required_date.Value)) AND

            DateValue(Fields!ship_date.Value) > DateValue(Fields!required_date.Value)

        ) OR

        (DateValue(Fields!print_date.Value) > DateValue(Fields!required_date.Value) AND

         Hour(Fields!print_date.Value) < 11 AND

         DateValue(Fields!ship_date.Value) > DateAdd("d", 1, DateValue(Fields!print_date.Value))) OR

        (DateValue(Fields!print_date.Value) = DateValue(Fields!required_date.Value) AND

         Hour(Fields!print_date.Value) >= 11 AND

         DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) >= 2) OR

        (DateValue(Fields!print_date.Value) = DateValue(Fields!required_date.Value) AND

         Hour(Fields!print_date.Value) < 11 AND

         DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) > 1),

        "Red",

        "No Color"

    )

)

Thank you!

Answer 1

Hi Karen Bashaw

Try with below expression to check the rule for print_date and ship_date.

=IIf((DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value))
        - (
            ( (DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) \ 7 ) * 2 )
            + IIf( (DatePart("w", DateValue(Fields!print_date.Value)) + (DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) Mod 7 ) -1 ) >=7, 1, 0 )
            + IIf( DatePart("w", DateValue(Fields!print_date.Value)) <=1 Or (DatePart("w", DateValue(Fields!print_date.Value)) + (DateDiff("d", DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) Mod 7 ) -1 ) >=8, 1, 0 )
        )
    ) >= 2,
    "Red",
    "Transparent"
)

Or using custom code.

Public Function BusinessDaysBetween(startDate As Date, endDate As Date) As Integer
    Dim count As Integer = 0
    Dim current As Date = startDate
    
    While current <= endDate
        ' Check if the day is a weekday (Monday to Friday)
        If Weekday(current, vbMonday) <= 5 Then
            count = count + 1
        End If
        current = DateAdd("d", 1, current)
    End While
    
    Return count - 1 ' Exclude the start date if needed
End Function

And then use the code in your expression:

=IIf(Code.BusinessDaysBetween(DateValue(Fields!print_date.Value), DateValue(Fields!ship_date.Value)) >= 2, "Red", "Transparent")

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Zoe,

I used your expression to check the rule for print_date and ship_date and then I built the rest of the code up and it worked. I also split each OR statement into it's own column so I could check each calculation. THANK YOU! This has been driving me crazy for days.