none
Query for the list of publich holidays from the Project Server 2010 for reporting RRS feed

  • Question

  • I would like to get the list of public holidays that is configured in the calendar from the reporting database. I am not sure if this information is available in the database.

    Please can you provide with the sql query . thanks


    Simi
    Monday, September 5, 2011 8:46 AM

Answers

  • I know this is an old thread, but I found it while looking to do the same thing, but later managed to come up with a solution:

    You can write a query that will find non-weekend days that have a sum 0 capacity. (you might have to specify a specific calendar if you have some that don't contain these holidays)

    The one below works for me.  Hope this helps.

    SELECT
    TimeByDay,
    SUM (CAP) as [Cap]

    FROM

    (SELECT
    MSP_TimeByDay.TimeByDay,
    MSP_TimeByDay.TimeDayOfTheWeek,
    MSP_EpmResourceByDay_UserView.BaseCapacity as [CAP],
    MSP_EpmResource_UserView.ResourceBaseCalendar

    FROM

    MSP_EpmResourceByDay_UserView

    INNER JOIN
    MSP_TimeByDay ON
    MSP_TimeByDay.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_EpmREsourceByDay_UserView.ResourceUID

    WHERE

    (MSP_TimeByDay.TimeDayOfTheWeek = 2 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 3 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 4 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 5 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 6) AND
    (MSP_EpmResource_UserView.ResourceBaseCalendar like '%standard%') as x

    GROUP BY
    TimeByDay

    HAVING
    SUM (CAP) = 0

    Hope this helps!

    Elli J - Project Server Admin  Blog:  http://projectserverpants.wordpress.com/

    Wednesday, September 12, 2012 4:24 PM
  • A bit more low tech perhaps, but here's how to do it with Microsoft Project and OOTB reports:

    http://azlav.umtblog.com/2011/06/22/identifying-resource-calendar-exceptions-with-project-professional/


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Thursday, September 13, 2012 1:04 AM
    Moderator

All replies

  • Hello Simi,

    I am afraid that you may not like my answer. As far as i know that calendar information is Not available in project server reporting database. You will need to use calendar web service which gets you the information about the calendar. You can use Readcalendar method to get the calendar exceptions that match the filter set.

    http://msdn.microsoft.com/en-us/library/websvccalendar_di_pj14mref.aspx
    http://msdn.microsoft.com/en-us/library/websvccalendar.calendar_di_pj14mref_methods.aspx
    http://msdn.microsoft.com/en-us/library/gg223326.aspx

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, September 5, 2011 10:11 AM
  • I know this is an old thread, but I found it while looking to do the same thing, but later managed to come up with a solution:

    You can write a query that will find non-weekend days that have a sum 0 capacity. (you might have to specify a specific calendar if you have some that don't contain these holidays)

    The one below works for me.  Hope this helps.

    SELECT
    TimeByDay,
    SUM (CAP) as [Cap]

    FROM

    (SELECT
    MSP_TimeByDay.TimeByDay,
    MSP_TimeByDay.TimeDayOfTheWeek,
    MSP_EpmResourceByDay_UserView.BaseCapacity as [CAP],
    MSP_EpmResource_UserView.ResourceBaseCalendar

    FROM

    MSP_EpmResourceByDay_UserView

    INNER JOIN
    MSP_TimeByDay ON
    MSP_TimeByDay.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_EpmREsourceByDay_UserView.ResourceUID

    WHERE

    (MSP_TimeByDay.TimeDayOfTheWeek = 2 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 3 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 4 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 5 OR
    MSP_TimeByDay.TimeDayOfTheWeek = 6) AND
    (MSP_EpmResource_UserView.ResourceBaseCalendar like '%standard%') as x

    GROUP BY
    TimeByDay

    HAVING
    SUM (CAP) = 0

    Hope this helps!

    Elli J - Project Server Admin  Blog:  http://projectserverpants.wordpress.com/

    Wednesday, September 12, 2012 4:24 PM
  • A bit more low tech perhaps, but here's how to do it with Microsoft Project and OOTB reports:

    http://azlav.umtblog.com/2011/06/22/identifying-resource-calendar-exceptions-with-project-professional/


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Thursday, September 13, 2012 1:04 AM
    Moderator
  • I just put together this query based on what Elli J did. My problem was to come up with a way to define a calendar-based "grace period" extension of the baseline finish date for a project. The grace period had to be based on a scale factor (here 1.1) applied to the project's official baseline duration. I then had to project forward, using the standard calendar, to the relevant grace period finish date on a per-project basis.

    Anyway, here is what I came up with. Note that I eliminated the SUM operations as unnecessary, and I also had to account for the fact that the official calendar days queryable using resource by day base capacity can "run out" before it should, so that it has to be extended by tacking on regular weekdays after that.

    SELECT
        P.ProjectUID,
        P.ProjectName,
        ProjectBaseline0StartDate = PSTB0.TaskBaselineStartDate,
        ProjectBaseline0FinishDate = PSTB0.TaskBaselineFinishDate,
        ProjectBaseline0DurationInDays = ROUND(PSTB0.TaskBaselineDuration / 8, 0),
        ProjectBaseline0GracePeriodDurationInDays = ROUND(ROUND(PSTB0.TaskBaselineDuration / 8, 0) * (GracePeriodParameters.MultiplierExcludingBase), 0),
        ProjectBaseline0GracePeriodFinishDate =
        (
            SELECT TimeByDay
            FROM
            (
                 SELECT
                     TimeByDay,
                     DayRank = RANK() OVER (ORDER BY TimeByDay)
                 FROM
                 (
                    -- Query all calendar days in standard calendar, using resource base capacity as proxy
                    -- Actual calendar data is not available in any of the MSPS relational databases.
     
                     SELECT TBD.TimeByDay
                     FROM dbo.MSP_TimeByDay TBD
                     JOIN dbo.MSP_EpmResourceByDay RBD ON TBD.TimeByDay = RBD.TimeByDay
                     JOIN dbo.MSP_EpmResource R ON R.ResourceUID = RBD.ResourceUID
                     WHERE
                        R.ResourceBaseCalendar = StandardCalendarInformation.CalendarName AND
                        TBD.TimeByDay > PSTB0.TaskBaselineFinishDate AND
                        RBD.BaseCapacity > 0

                    UNION

                    -- Extend genericallly with weekdays if the calendar days queried above run out too early
                    SELECT TBD2.TimeByDay
                    FROM dbo.MSP_TimeByDay TBD2
                    WHERE
                        TBD2.TimeDayOfTheWeek IN (2,3,4,5,6) AND
                        TBD2.TimeByDay > PSTB0.TaskBaselineFinishDate AND
                        TBD2.TimeByDay > StandardCalendarInformation.LatestDateWithNonZeroCapacity
                )
                UnrankedUnion
            )
            RankedUnion
            WHERE RankedUnion.DayRank = ROUND(ROUND(PSTB0.TaskBaselineDuration / 8, 0) * (GracePeriodParameters.MultiplierExcludingBase), 0)
        )
    FROM dbo.MSP_EpmProject P
    LEFT JOIN dbo.MSP_EpmTask PST ON PST.TaskIsProjectSummary = 1 AND P.ProjectUID = PST.ProjectUID
    LEFT JOIN dbo.MSP_EpmTaskBaseline PSTB0 ON PSTB0.BaselineNumber = 0 AND PST.TaskUID = PSTB0.TaskUID
    CROSS JOIN (SELECT 1.1 AS MultiplierIncludingBase, 0.1 AS MultiplierExcludingBase) GracePeriodParameters
    CROSS JOIN
    (
        SELECT TOP 1 CalendarName = C.CAL_NAME, LatestDateWithNonZeroCapacity = TBD.TimeByDay
        FROM dbo.MSP_TimeByDay TBD
        JOIN dbo.MSP_EpmResourceByDay RBD ON TBD.TimeByDay = RBD.TimeByDay
        JOIN dbo.MSP_EpmResource R ON R.ResourceUID = RBD.ResourceUID
        JOIN [Demo2010A_PWA_Published].dbo.MSP_CALENDARS C ON R.ResourceBaseCalendar = C.CAL_NAME
        WHERE C.CAL_IS_STANDARD_CAL = 1 AND RBD.BaseCapacity > 0
        ORDER BY TBD.TimeByDay DESC
    )
    StandardCalendarInformation

    - William Schmidt

    Monday, February 10, 2014 6:30 PM
  • Dear William 
    would you please explain "also had to account for the fact that the official calendar days queryable using resource by day base capacity can "run out" before it should"

    I'm trying to use the same query, but for some resources, for - maybe your line - some reason, they query does not back with anything


    Hany Zayed

    Sunday, November 29, 2015 3:09 PM
  • Can you please provide the Query for the list of publich holidays from the Project Server 2016 for reporting?

    Thanks in advance

    Nikhil

    Monday, August 22, 2016 5:58 AM