SSRS Weekday Expressions

Question

I have two tables in SQL Server 2008 R2 Report Builder 3.0 each with the same three columns: Task, Assigned To and Due Date.

My goal is to use the expression builder to show me a specific range of dates for each table.

In the first table, I want to show only the records/rows where the due date is older than or equal to the previous Friday. All records that show up last week (Monday - Friday) must have the Fields!Due_Date.value highlighted as yellow. If Fields!Due_Date.value is two Fridays ago or older, the record will be highlighted as red.

The second table contains the same columns as the first. In this table, I only want to display the records where the Due Date value is within the current week (Monday - Friday). There does not need to be a color.

---

MY PROGRESS

---

There are two filters in the first table:

• Expression: [Due_Date]
• Data Type: Date/Time
• Operator: <
• Value: =Today()

AND

• Expression: =IsNothing(Fields!Due_Date.Value)
• Data Type: Boolean
• Operator: =
• Value: False

I have customized the Background Color for the Due Date TextBox and use the following expression:

=IIF(Fields!Due_Date.Value <= DateAdd("d", -8(today()), "Red", "Yellow")

These expressions and filters will only give me the results that I want when the current day is Monday.

To reiterate, I want the table to show records where the due date is less than today and where the background color of the Due Date cell is yellow when the value is any day of last week, AND where if the due date is two past fridays or older, then the background color to be red.

Answer 1

You need to adjust the TODAY by the day of the week (with DATEPART) and normalize the day of the week (-2 ... Sunday - 2 = Friday) :

=IIF(Fields!Due_Date.Value <= DateAdd("d", -2 - DATEPART("dw", TODAY), TODAY), "Red", "Yellow")

I'm not sure this will do what you want - records a date older than (or =) last Friday would be red while not as old would be yellow. It may need some adjustment but should get you in the right direction.