Get TimeSheet Data Project Server 2013

Here is SQL query to get Timesheet Data Project Server:

Two-way are to get timesheet data in project server, first is to get data using Project Server Interface(PSI) and second is using SQL Query. We have written SQL query to get timesheet data.

SELECT MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate, MSP_TimesheetProject.ProjectName,
MSP_TimesheetResource.ResourceName, MSP_EpmTask.TaskName, MSP_TimesheetActual.TimeByDay,
MSP_TimesheetActual.ActualWorkBillable + MSP_TimesheetActual.ActualWorkNonBillable + MSP_TimesheetActual.ActualOvertimeWorkBillable + MSP_TimesheetActual.ActualOvertimeWorkNonBillable
AS ActualWork, MSP_TimesheetActual.PlannedWork, MSP_EpmAssignment.AssignmentPercentWorkCompleted, MSP_EpmTask.ProjectUID,
MSP_EpmTask.TaskUID
FROM MSP_EpmTask INNER JOIN
MSP_TimesheetTask INNER JOIN
MSP_Timesheet INNER JOIN
MSP_TimesheetLine ON MSP_Timesheet.TimesheetUID = MSP_TimesheetLine.TimesheetUID INNER JOIN
MSP_TimesheetActual ON MSP_TimesheetLine.TimesheetLineUID = MSP_TimesheetActual.TimesheetLineUID INNER JOIN
MSP_TimesheetPeriod ON MSP_Timesheet.PeriodUID = MSP_TimesheetPeriod.PeriodUID INNER JOIN
MSP_TimesheetResource ON MSP_Timesheet.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID INNER JOIN
MSP_EpmAssignment ON MSP_TimesheetLine.AssignmentUID = MSP_EpmAssignment.AssignmentUID ON
MSP_TimesheetTask.TaskNameUID = MSP_TimesheetLine.TaskNameUID ON MSP_EpmTask.TaskUID = MSP_TimesheetTask.TaskUID AND
MSP_EpmTask.ProjectUID = MSP_EpmAssignment.ProjectUID AND MSP_EpmTask.TaskUID = MSP_EpmAssignment.TaskUID LEFT OUTER JOIN
MSP_TimesheetProject ON MSP_EpmTask.ProjectUID = MSP_TimesheetProject.ProjectUID AND
MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID
WHERE (MSP_TimesheetActual.PlannedWork > 0)
--AND (MSP_Timesheet.PeriodUID = @TimeSheetPeriod)
ORDER BY MSP_TimesheetPeriod.PeriodName, MSP_TimesheetProject.ProjectName, MSP_TimesheetResource.ResourceName, MSP_EpmTask.TaskName,
MSP_TimesheetActual.TimeByDay

 

Below is result of SQL Query-

 

get timesheet

 

get timesheet


Comments

  1. Dear Suresh,

    When you will create timesheet for resources then with the help of this query you can get complete timesheet detail as shown in images.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

error: Content is protected !!