web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SSRS - Count of Busine...
Microsoft Dynamics GP (Archived)

SSRS - Count of Business Days excluding Holidays

(0) ShareShare
ReportReport
Posted on by ASheppardWork 220

We have a SSRS report need to average the amount of GrossProfit over a range of business days; excluding holidays.

Currently the report is being run in Crystal Reports and copy/pasted into excel where a NETWORKDAYS formula is using a spreadsheet with the calendar days filled in.

The report must be fully automated with no need for human intervention and the result set will look like:

$5,000.00 GP/Day for the last 22 business days.

I found a code for a function that will calculate the weekdays minus the weekend days; but not one for also excluding holidays.  Before I go through the trouble of making a holiday/business day calendar table that will need to have the next 5-10 years and the prior 2-3 years; I wanted to see if anyone had tackled this problem before and what you did.

Here is the current code (I forgot the author):

Function getBusinessDaysCount(ByVal tFrom As Date, ByVal tTo As Date) As Integer

   Dim tCount As Integer

   Dim tProcessDate As Date = tFrom

   For x as Integer= 1 To DateDiff(DateInterval.Day, tFrom, tTo) + 1

     If Not (tProcessDate.DayOfWeek = DayOfWeek.Saturday Or tProcessDate.DayOfWeek =    DayOfWeek.Sunday) Then

       tCount = tCount + 1

     End If

     tProcessDate = DateAdd(DateInterval.Day, 1, tProcessDate)

   Next

   Return tCount

End Function

*This post is locked for comments

I have the same question (0)
  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    You could explore the option of creating a "holidays" table and reference that in the SQL query. Someone will have to maintain the table with all holidays through some interface but this would be easier than hardcoding those dates in the SQL query itself.

    Your other option would be similar to what you're doing now in Crystal. Just export from SRS to Excel.

  • Suggested answer
    ASheppardWork Profile Picture
    ASheppardWork 220 on at

    Unfortunately, it looks like my best bet is to create a calendar table.  I found the following article on the how and the why of creating calendars to report off of.

    Why should I consider using an auxiliary calendar table?

    sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    SET @StartDate = '2013/10/01'

    SET @EndDate = '2013/10/31'

    SELECT

      (DATEDIFF(dd, @StartDate, @EndDate) + 1)

     -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

     -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

     -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

    Source:

    stackoverflow.com/.../count-work-days-between-two-dates

    Cheers!

    Sanjay

  • ASheppardWork Profile Picture
    ASheppardWork 220 on at

    Thank you Sanjay; but that query does not address the holiday issue; it is basically the same as the function I had above.

    What I need is to figure how many business days (excluding holidays) are between the two parameters @StartDate/@EndDate in my SSRS report.

    I followed the instructions on the link I listed above and now have a ZZ_Calendar table.  The problem is now using this table in my report.

    The dataset has fields

    ID,SalesID,DocDate,Price,GrossProfit

    The ZZ_Calendar table has fields

    dt(date),isWeekday,isHoliday,Y(for year),HolidayDescription

    The query would be:

    SELECT COUNT(DT)

    FROM ZZ_Calendar

    WHERE

    (

    YEAR(DT) = YEAR(@StartDate) OR

    YEAR(DT) = YEAR(@StartDate)-1

    )

    However the trouble is now trying to use this result in the SSRS because it is a different dataset with no relation to the main data.

  • Verified answer
    ASheppardWork Profile Picture
    ASheppardWork 220 on at

    The final solution has been to create a calendar table (see above link) and then to use the following query as the dataset:

    SELECT ID,SalesID,DocDate,Price,GrossProfit,

          CYDayCount.CYWkDayCNT,LYDayCount.LYWkDayCNT

    FROM   MainTable

    Cross JOIN (SELECT COUNT(DT)CYWkDayCNT FROM ZZ_Calendar WHERE ( DT >= @StartDate AND DT<= @EndDate) AND isWeekDay = 1 AND isHoliday=0 ) CYDayCount

    Cross JOIN (SELECT COUNT(DT)LYWkDayCNT FROM ZZ_Calendar WHERE (DT >= CONVERT(VARCHAR(10), DATEADD(Year,-1,@StartDate), 120) AND DT <= CONVERT(VARCHAR(10), DATEADD(Year,-1,@EndDate), 120))AND isWeekDay = 1 AND isHoliday=0 ) LYDayCount

    WHERE (YEAR(DOCDATE) = YEAR(@StartDate) OR

         YEAR(DOCDATE) = YEAR(@StartDate) - 1)

    This allows me to use GrossProfit/CYWkDayCNT to the get the Gross Profit divided by the current year work day count and the last year work day count.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans