I’ve completed several SQL Server Reporting Services Projects across versions that have needed to calculate the number of working days between two dates. For example I’ve needed to calculate if fulfilment of an order or some other process has met its Service Level Agreement.
When I am using a SQL Server datasource it is relatively straight forward to remove weekends. I then need to take out the Bank Holidays etc. Some of my work is with Microsoft Dynamics CRM which has handy way to record business closures. As long as I train the administrators to record all closures in single days I can use a Select statement like the one below:
DATEDIFF(dd, new_datereceived, new_despatchdate) + 1)
– DATEDIFF(wk, new_datereceived, new_despatchdate) * 2
– (CASE WHEN DATENAME(dw, new_datereceived) = ‘Sunday’ THEN 1 ELSE 0 END)
– (CASE WHEN DATENAME(dw, new_despatchdate) = ‘Saturday’ THEN 1 ELSE 0 END)
– (SELECT COUNT(*) AS Expr1 FROM FilteredCalendarRule WHERE (starttime BETWEEN FilteredNew_myorder.new_datereceived
AND (subcode = 5))
AS [WorkDays to Ready]
On a recent project I was reporting from a third-party business system, for the sake of this article we’ll call it fluorite. Now fluorite is SQL based however the developers wouldn’t give me access to directly query their tables. Instead they created a report model and recommended that I reported from that using Report Builder. Now using a report model presents a few issues and restrictions.
One issue was hot to store the list of business closures. The customer already had SharePoint (Windows SharePoint Services v3) and the reports were using SQL 2008 R2 Reporting Services. This meant that I could create a SharePoint List for Business Closures and then create a SharePoint List Datasource with a Dataset pointing at the closures list. The screenshots below show the Dataset properties and the query builder. In the query I’m pulling through the Title and the Date column. The Title column isn’t really needed here as I don’t care why they’re closed. The Date column just contains the date of the closure
I thought that I could use one of the new 2008R2 lookup or lookupset functions to read/filter and count results from my Business Closures dataset. This turned out not to be possible. On thinking again I realised that I need to put code on the Report to process this dataset. The following articles helped me here:
Firstly Joshua Clark has written a great article on how to put code onto a Reporting Services project http://joshua-clark.blogspot.com/2008/10/how-to-write-custom-code-in-sql.html.
Secondly I found a blog post on how to remove the weekends with code. I could do this with an expression but this is a good way http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c8558211-9414-482b-8f78-e9e307b5c8ea/. Check out the answer from Nehemiah Willis who provides some code for iterating through all days between two dates and ignoring the weekends. The article then says
if you want to account for holidays it would prolly be best to have a business day lookup table then u can query for business like:
select count(*) from calendar where calendar_date between @startdate and @enddate
So now I need a way to do that without being able to write a select statement but needing to process a dataset of business closures. Well the answer can be found in the following thread: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a7d59224-0ee5-491e-883b-2e5fcb3edeab. Check the answer by Miguel Catalao
Heres how, the ideal is to “transform” the data set into a multivalued parameter (or if you need several fields, transform it in multiple multivalued parameters).
The multivalued Report Parameter must have the following characteristics:
Hidden = True
Allow Multiple Values = True
Available Values tab:
Chose the desired dataset. Select the searchable id as Value id, and the field you want to expose as Label Field.
Default Values Tab – Get Values from a Query.
Choose the same Dataset as choosen in the available Values Tab.
Value Field the same you choose for value id.
Set the parameter to never refresh (or it will be loading the data from each iteraction of another parameter).
So I now know how to create code, have a function that will remove weekends and know how to process my dataset. To put this all together I added the following code:
Function work_days(ByVal StartDate As DateTime, ByVal EndDate As DateTime) As Integer
Dim Bus As Integer
Dim temp As DateTime
Dim i as Integer
Bus = 0
temp = StartDate
While (temp < EndDate)
if (temp.DayOfWeek <> DayOfWeek.Saturday And temp.DayOfWeek <> DayOfWeek.Sunday) Then
Bus += 1
temp = DateAdd(“d”, 1, temp)
for i = 0 to Report.Parameters!ClosureDates.Count() -1
if (Report.Parameters!ClosureDates.Value(i) >= StartDate and Report.Parameters!ClosureDates.Value(i) <= EndDate ) Then
Bus -= 1
Then all I need to do is call the code from a placeholder expression in my report:
My report can now use the secondary data set to process the bank holidays and deliver on the requirement at the beginning