none
How we can get Working Days and Non-Working Days between two dates in project server Reporting Database. RRS feed

  • Question

  • I am writing a query for report from ProjectServer_Reporting .

    I want columns in that query for Working Days and Non-Working Days which we mention in calendars of Project Servers.

    How we can get these columns and in which view or table these Working days and Non-working days are saved.

    I want these working days or non-working days between two dates . which the users selected in report as a StartDate and EndDate.

    The Calendar which made in Project Server 2010 . which table or view it saved ?



    • Edited by John.Eddie Monday, September 10, 2012 1:30 PM
    Monday, September 10, 2012 1:21 PM

Answers

  • So, doing this from memory as I don't have the system up at the moment. One thing you may consider is using the Base Capacity/Capacity fields in the MSP_EPMResourceByDay_UserView. The base capacity only has a value for days in which the resource could accept work. If there's an exception in the calendar, the capacity and base capacity fields would differ as the capacity field is the effective capacity field. So, a holiday on a normal working day would have Base Capacity of 8 hrs and Capacity of 0. Using this, you may be able to infer the number of working days (days with positive capacity) and non-working days (positive base capacity but zero capacity).

    Treb Gatte @tgatte http://aboutmsproject.com

     
    • Marked as answer by John.Eddie Wednesday, September 19, 2012 8:48 AM
    Thursday, September 13, 2012 7:35 AM
    Moderator
  • just stumbled on this today from a poster to this forum.

    http://projectserverpants.wordpress.com/2012/09/12/remind-the-reporting-database-that-it-does-in-fact-know-holiday-dates/


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by John.Eddie Wednesday, September 19, 2012 8:48 AM
    Thursday, September 13, 2012 11:17 PM
    Moderator

All replies

  • Hi John.Eddie,

    Calendar information is not available in the reporting database, you will need to read calendar exceptions using the PSI:

    http://msdn.microsoft.com/en-us/library/office/gg223326

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Monday, September 10, 2012 2:11 PM
    Moderator
  • John.Eddie, what's the problem you are attempting to address? This might help the creative juices a bit.

    Treb Gatte @tgatte

    Tuesday, September 11, 2012 7:10 AM
    Moderator
  • I am making a report which I will design and publish on SQLServer Reporting Services.

    Now I am making a query . I have almost complete the query . 70% . in that report I am showing resources w.r.t projects and Dates and theirs ActualBillable Work w.r.t Working hours and non-working hours.

    But I need one column of Working Days and One is Non-Working days of any month . which I select from start date and End Date and pass these dates as a parameters to Query.

    What I created a calendar in Project Server 2010 . which is using for different projects .

    and in that calendar we also mention different working days and exceptions for holidays . I need to get these.

    That is scenario.

    Tuesday, September 11, 2012 9:28 AM
  • So, doing this from memory as I don't have the system up at the moment. One thing you may consider is using the Base Capacity/Capacity fields in the MSP_EPMResourceByDay_UserView. The base capacity only has a value for days in which the resource could accept work. If there's an exception in the calendar, the capacity and base capacity fields would differ as the capacity field is the effective capacity field. So, a holiday on a normal working day would have Base Capacity of 8 hrs and Capacity of 0. Using this, you may be able to infer the number of working days (days with positive capacity) and non-working days (positive base capacity but zero capacity).

    Treb Gatte @tgatte http://aboutmsproject.com

     
    • Marked as answer by John.Eddie Wednesday, September 19, 2012 8:48 AM
    Thursday, September 13, 2012 7:35 AM
    Moderator
  • just stumbled on this today from a poster to this forum.

    http://projectserverpants.wordpress.com/2012/09/12/remind-the-reporting-database-that-it-does-in-fact-know-holiday-dates/


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by John.Eddie Wednesday, September 19, 2012 8:48 AM
    Thursday, September 13, 2012 11:17 PM
    Moderator