none
Is it possible to count the number of vacation days ? RRS feed

  • Question

  • Hello,

    I would like to use Project Server for a complete vacation days demand. I like the approbation system but I have a question :  is it possible to count the number of available vacation days per employee ?

    Actually in my organization, we have a system which count every available vacation days (like legal, delayed holidays, ...) and I didn't see anything like that in PS.

    Thank you.

    Friday, October 12, 2012 10:51 AM

Answers

  • Hi Justin,

    are you looking for someting like this? This will only include individual days off, no non working days from Calendar.

    SELECT     COUNT(dbo.MSP_TimesheetActual_OlapView.TimeByDay) AS NumberOfVacationEnries, dbo.MSP_TimesheetLine_UserView.ResourceName
    FROM dbo.MSP_TimesheetActual_OlapView INNER JOIN
     dbo.MSP_TimesheetLine_UserView ON dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID = dbo.MSP_TimesheetLine_UserView.TimesheetLineUID
    WHERE     (dbo.MSP_TimesheetLine_UserView.TaskName = N'Vacation') AND (dbo.MSP_TimesheetActual_OlapView.PlannedWork > 0) OR(dbo.MSP_TimesheetLine_UserView.TaskName = N'Vacation') AND (dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable > 0)
    GROUP BY dbo.MSP_TimesheetLine_UserView.ResourceName
    Regards
    Barbara
    Friday, October 12, 2012 11:27 AM
    Moderator

All replies

  • Hi Justin,

    are you looking for someting like this? This will only include individual days off, no non working days from Calendar.

    SELECT     COUNT(dbo.MSP_TimesheetActual_OlapView.TimeByDay) AS NumberOfVacationEnries, dbo.MSP_TimesheetLine_UserView.ResourceName
    FROM dbo.MSP_TimesheetActual_OlapView INNER JOIN
     dbo.MSP_TimesheetLine_UserView ON dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID = dbo.MSP_TimesheetLine_UserView.TimesheetLineUID
    WHERE     (dbo.MSP_TimesheetLine_UserView.TaskName = N'Vacation') AND (dbo.MSP_TimesheetActual_OlapView.PlannedWork > 0) OR(dbo.MSP_TimesheetLine_UserView.TaskName = N'Vacation') AND (dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable > 0)
    GROUP BY dbo.MSP_TimesheetLine_UserView.ResourceName
    Regards
    Barbara
    Friday, October 12, 2012 11:27 AM
    Moderator
  • Hi there--

    You can see Resource availability from resource center but won't show the Resource cal exceptions (holidays). Project server reporting database doesn't contain the Cal Info & but using the Project server PSI programming, You can read the exceptions:
    http://social.msdn.microsoft.com/Forums/sv/project2010custprog/thread/f0b971e2-8af8-4a5f-a79d-e008c9ea58de

    I believe that using the MS project using VBA also you can get this information.

    Hope that helps.


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Friday, October 12, 2012 11:33 AM
  • Thank you Barbara.

    I succeeded to collect the good informations with this query. But I'm disappointed that there is no native solution to manage the number of available vacation days per employee .

    I think I'm going to create a Webpart for that and use this query to subtract the number of vacation days taken by an employee.

    Friday, October 12, 2012 2:37 PM
  • Thank you Amit, but I think your solution is for a non-working day from Calendar and I look for individual days off (insert in a Timesheet).
    Friday, October 12, 2012 2:43 PM