Trying to display weekday calendar dates with business days on my SSRS report

Question

I have a report in which I have business days (so no weekends) calculated based on the calendar date in which the event occurred in the SQL query. IE,

Date_Completed: 22 Jan 2014 (<--- From system tables)
Day_completed = 16 <---- What I calculated in the query.

Now, I am trying to create a calendar-like report in SSRS for the purpose of tracking the event dates. I want to the make the business days and the corresponding calendar date as my column headers ie

1
1/1/2014

2
1/2/2014

So my problem is that the events do not occur on every business day, so I created a bunch of dummy values in my query (basically union all 1-40 business days...I'll work on making this dynamic in the future).

So now I have the 1-40 business day columns, and my values are aligning to them properly...however, the calendar dates are not matching because I do not know how to make SSRS skip weekend dates in my column header...

I tried something like:

=iif((weekday(dateadd("d",Fields!period_complete.Value,Fields!period_dt.Value))<>1 or weekday(dateadd("d",Fields!period_complete.Value,Fields!period_dt.Value))<>7), dateadd("d",Fields!period_complete.Value,Fields!period_dt.Value),"")

but that did nothing. Anyone have any ideas on how I can get SSRS to spit out non-weekend, calendar dates for a quarter (Jan-Mar)?

Christopher Brown Christopher Brown · Accepted Answer · 2014-03-10T19:51:55

I see from your question that you're trying to use SSRS expressions to achieve your desired result. However, when it comes to dynamic filtering, I've found myself turning to SQL to do the majority of the work. With that in mind, I think this will achieve your desired goal.

The query below will create a list of all dates for a year (equal to the current year based on the system time). Then, it will give you the day of the week as an integer and the day of the year. Additionally, it will filter out all Saturdays and Sundays, leaving you with only working days. Finally, it calculates which quarter we're in now (I'm using quarters by calendar year, but you could easily adapt it to rolling quarters or to your fiscal year quarters) as a way of filtering the list of days to the current quarter. Please let me know if you have questions.

Declare @Start date = Case
                            When Month(getdate()) <= 3
                            Then '01/01/' + CAST(YEAR(getdate()) as varchar(4))
                                When Month(getdate()) <= 6
                                Then '04/01/' + CAST(YEAR(getdate()) as varchar(4))
                                    When Month(getdate()) <= 9
                                    Then '07/01/' + CAST(YEAR(getdate()) as varchar(4))
                            Else '10/01/' + CAST(YEAR(getdate()) as varchar(4))
                            End;
Declare @End date = Case
                            When Month(getdate()) <= 3
                            Then '03/31/' + CAST(YEAR(getdate()) as varchar(4))
                                When Month(getdate()) <= 6
                                Then '6/30/' + CAST(YEAR(getdate()) as varchar(4))
                                    When Month(getdate()) <= 9
                                    Then '09/30/' + CAST(YEAR(getdate()) as varchar(4))
                            Else '12/31/' + CAST(YEAR(getdate()) as varchar(4))
                            End;
Set NoCount on;

With All_Dates as (
Select CAST('01/01/' + CAST(YEAR(getdate()) as varchar(4)) as Date) as Dates

Union All

Select DATEADD(day,1,Dates) as Dates

From All_Dates

Where Dates < '12/31/' + CAST(YEAR(getdate()) as varchar(4))
)

Select DATEPART(DAYOFYEAR,Dates) as Day_of_Year
    , DATEPART(WEEKDAY,Dates) as Day_of_Week
    , Dates

From All_Dates

Where DATEPART(WEEKDAY,Dates) between 2 and 6
    and Dates >= @Start
    and Dates <= @End

Option (MaxRecursion 400);

Trying to display weekday calendar dates with business days on my SSRS report

1 Answers