Timesheet - Non Project Timesheet hours RRS feed

  • Question

  • Hi ,

    We have been trying to retrieve non-project timesheet hours (for example Sick leave, Vacation etc.,) datewise for a given specific period. Right now we are able to get Task Actual and overtiem hours by datewise using "Statusing"web service, which is not helping us to also get administrative hours (sick, vacation etc.,).

    We tried to use Timesheet webservice using LinedataTable wherein it gives us comulative hours of each task and administrative hours, but not by date.

    Can anyone help us if you had similar requiremetns and able to success in it..

    Thanks in advance


    Friday, February 1, 2013 6:58 AM

All replies

  • Download Project SDK and look into the database schema.  There is a schema for the database and find out what table the administrative time is located.

    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Blog contains my field notes and SQL queries

    Saturday, February 2, 2013 10:41 AM
  • Hi,

    you can use the following query against the Reporting (2007/2010) or Project Service database as a starting point:

    USE PWA_Reporting
          MSP_TimeByDay.FiscalPeriodName                       AS Period
          , MSP_EpmResource_UserView.ResourceName              AS ResourceName
          , SUM(MSP_TimesheetLine_UserView.PlannedWork)        AS PlannedAbsence
          , SUM(MSP_TimesheetLine_UserView.ActualWorkBillable) AS ActualAbsence
      FROM MSP_TimesheetLine_UserView
          INNER JOIN MSP_EpmResource_UserView 
                ON MSP_TimesheetLine_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID
          INNER JOIN MSP_TimeByDay
                ON MSP_TimesheetLine_UserView.PeriodStartDate = MSP_TimeByDay.TimeByDay
      WHERE TimesheetLineClassType =1  -- Absence
      GROUP BY
          MSP_TimeByDay.FiscalPeriodName, MSP_EpmResource_UserView.ResourceName 
      ORDER BY 
        Period, ResourceName


    Renke Project Management with MS Project - Oldenburg, Berlin, Munich/Germany

    • Proposed as answer by Renke Holert Monday, February 4, 2013 8:09 PM
    Saturday, February 2, 2013 6:02 PM