Comparing timesheet and project assignments actual data

From to time to time you may need to check out that your Project Server’s timesheet and assignments actual data are in sync. In order to achieve that you need to build a SQL query that connects and compares the timesheet and assignments data. The final report can be a cube, excel file or whatever report you may need.

In the example below I am going to create 3 SQL views based on the reporting database of Project Server 2007:

• The _Tasks_All view which combines assignment actual data from different tables in a human readable form
• The _Timesheet_All view which combines timesheet actual data from different tables in a human readable form
• The _Timesheet_Diff view which compares the other two custom views and calculates the differences

Connect to your database server where you are hosting your Project Server databases and expand the Project Server reporting database. Right click the views folder and select new view. On the add table popup dialog click “close” and paste the query below in the view builder

SELECT dbo.MSP_EpmAssignment.AssignmentUID,dbo.MSP_EpmResource.ResourceUID,dbo.MSP_EpmResource.ResourceName,dbo.MSP_EpmAssignmentByDay.TimeByDay,
dbo.MSP_EpmAssignmentByDay.AssignmentActualWork - dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork AS 'AssignmentActualRegularWork',
dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork,
dbo.MSP_EpmProject.ProjectUID,dbo.MSP_EpmProject.ProjectName,
dbo.MSP_EpmTask.TaskUID,dbo.MSP_EpmTask.TaskName,dbo.MSP_EpmTask.TaskOutlineNumber
FROM dbo.MSP_EpmAssignmentByDay INNER JOIN
dbo.MSP_EpmTask ON dbo.MSP_EpmAssignmentByDay.TaskUID = dbo.MSP_EpmTask.TaskUID INNER JOIN
dbo.MSP_EpmProject ON dbo.MSP_EpmAssignmentByDay.ProjectUID = dbo.MSP_EpmProject.ProjectUID INNER JOIN
dbo.MSP_EpmAssignment ON dbo.MSP_EpmAssignmentByDay.AssignmentUID = dbo.MSP_EpmAssignment.AssignmentUID INNER JOIN
dbo.MSP_EpmResource ON dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResource.ResourceUID

Save the view as _Tasks_All and close the builder.

Repeat the preceding procedure to create the _Timesheet_All view

SELECT dbo.MSP_TimesheetResource.ResourceUID, dbo.MSP_TimesheetResource.ResourceName,dbo.MSP_TimesheetLine.AssignmentUID,
dbo.MSP_TimesheetStatus.Description,dbo.MSP_TimesheetTask.TaskUID,
dbo.MSP_TimesheetTask.TaskName, dbo.MSP_TimesheetProject.ProjectUID,dbo.MSP_TimesheetProject.ProjectName,
dbo.MSP_TimesheetActual.TimeByDay,dbo.MSP_TimesheetActual.ActualWorkBillable,
dbo.MSP_TimesheetActual.ActualWorkNonBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkNonBillable, dbo.MSP_TimesheetActual.PlannedWork
FROM dbo.MSP_Timesheet INNER JOIN
dbo.MSP_TimesheetResource ON dbo.MSP_Timesheet.OwnerResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
dbo.MSP_TimesheetLine ON dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID = dbo.MSP_TimesheetPeriod.PeriodUID INNER JOIN
dbo.MSP_TimesheetStatus ON dbo.MSP_Timesheet.TimesheetStatusID = dbo.MSP_TimesheetStatus.TimesheetStatusID INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER JOIN
dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetActual ON dbo.MSP_TimesheetResource.ResourceNameUID = dbo.MSP_TimesheetActual.LastChangedResourceNameUID AND
dbo.MSP_TimesheetLine.TimesheetLineUID = dbo.MSP_TimesheetActual.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetPeriodStatus ON dbo.MSP_TimesheetPeriod.PeriodStatusID = dbo.MSP_TimesheetPeriodStatus.PeriodStatusID INNER JOIN
dbo.MSP_TimesheetClass ON dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID

and _Timesheet_Diff view

SELECT dbo._Timesheet_All.Description as 'Approval',dbo._Timesheet_All.ProjectName, dbo._Timesheet_All.TaskName,ISNULL (dbo._Tasks_All.TaskOutlineNumber,'N/A'),
dbo._Timesheet_All.TimeByDay,dbo._Timesheet_All.ResourceName,
dbo._Tasks_All.AssignmentActualRegularWork AS 'TaskRegularWork',
dbo._Timesheet_All.ActualWorkBillable AS 'TSRegularWork',
dbo._Tasks_All.AssignmentActualOvertimeWork AS 'TaskOvertimeWork',
dbo._Timesheet_All.ActualOvertimeWorkBillable AS 'TSOvertimeWork',
dbo._Timesheet_All.ActualWorkBillable – ISNULL(dbo._Tasks_All.AssignmentActualRegularWork,0) AS 'RegularDiff',
dbo._Timesheet_All.ActualOvertimeWorkBillable - ISNULL (dbo._Tasks_All.AssignmentActualOvertimeWork,0) AS 'OvertimeDiff'
FROM dbo._Timesheet_All LEFT OUTER JOIN
dbo._Tasks_All ON
dbo._Timesheet_All.AssignmentUID = dbo._Tasks_All.AssignmentUID AND
dbo._Timesheet_All.TimeByDay = dbo._Tasks_All.TimeByDay
WHERE dbo._Timesheet_All.ProjectName <> 'Administrative' AND(dbo._Timesheet_All.Description <> 'Rejected')

What we’ve done so far? We have created the _Tasks_all view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TaskOutlineNumber
• TimeByDay
• AssignmentActualRegularWork
• AssignmentActualOvertimeWork

The _Timesheet_All view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TimeByDay
• ActualWorkBillable
• ActualWorkNonBillable
• ActualWorkOvertimeBillable
• ActualWorkOvertimeNonBillable
• Description (approval status)
  

And finally the comparison view _Timesheet_Diff which repots back:

• Approval
• ProjectName
• TaskName
• OutlineNumber
• TimeByDay
• ResourceName
• TaskRegularWork
• TSRegularWork
• TaskOvertimeWork
• TSOvertimeWork
• RegularDiff (ActualWorkBillable – AssignmentActualRegularWork)
• OvertimeDiff (ActualOvertimeWorkBillable – AssignmentActualOvertimeWork)

The _Timesheet_Diff view joins the _Timesheet_All and _Tasks_All views on AssignmentUID and TimeByDay fields. I have used a LEFT OUTER JOIN instead of INNER JOIN because I’ve seen that some timesheets cannot bind back to assignments, due to Project changes after timesheets creation by users.

Finally, an easy way to produce a report is to create an excel document with a pivot table based on the _Timesheet_Diff view. A more robust way is to create a Analysis Services Cube or a Reporting Services Report under SharePoint server.

Technorati Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

Windows Live Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

WordPress Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare