Assignment Actuals <> Timesheet actuals RRS feed

  • Question

  • We are using SEM to ensure assignment actuals for work resources only come via approved timesheet/task update data.

    I have noticed over time that the actual values on assignments are out of sync with the approved timesheet values.  I have started to look into it more recently and the problem seems more prevelant than I feared.  I use the following query to highlight the differences in our DB.  It basically identifies tasks with differences by timesheet period (week).  For all periods between 1-Sep and 7-Dec, thre are 101 instances where the values are off more than .01 hour.  If I change the differential to 1 hour, there are still 40 instances.  There were 33 instances of difference greater than 2 hours over a timesheet period.  The little issues I can probably live with, but the larger ones are having a material impact on financials.

    select --puv.[Program Group],
    	Sum(abduv.AssignmentWork) AssignmentWork,
    	sum(abduv.AssignmentActualWork) AssignmentActualWork,
    	sum(tsaov.ActualWorkBillable) tsa_ActualWorkBillable,
    	Sum(abduv.AssignmentActualWork - tsaov.ActualWorkBillable) Diff
    	dbo.MSP_EpmProject_UserView puv
    	inner join dbo.MSP_EpmTask_UserView tuv on
    		tuv.ProjectUID = puv.ProjectUID
    	inner join dbo.MSP_EpmAssignment_UserView auv on
    		auv.ProjectUID = tuv.ProjectUID and
    		auv.TaskUID = tuv.TaskUID
    	inner join dbo.MSP_EpmResource_UserView ruv on
    		ruv.ResourceUID = auv.ResourceUID
    	inner join dbo.MSP_EpmAssignmentByDay_UserView abduv on
    		abduv.AssignmentUID = auv.AssignmentUID
    	inner join dbo.MSP_TimeByDay_OlapView tbdov on
    		tbdov.TimeByDay = abduv.TimeByDay
    	inner join	dbo.MSP_TimesheetLine_UserView tsluv on
    		tsluv.ProjectUID = auv.ProjectUID and
    		tsluv.TaskUID = auv.TaskUID and
    		tsluv.ResourceUID = auv.ResourceUID
    	inner join dbo.MSP_TimesheetActual_OlapView tsaov on
    		tsaov.TimesheetLineUID = tsluv.TimesheetLineUID and
    		tsaov.TimeByDay = tbdov.TimeByDay
    		tbdov.TimeByDay >= '01-Sep-2013' and
    	tbdov.TimeByDay <= '7-Dec-2013'
    Group By
    	--puv.[Program Group],
    having Abs(sum(abduv.AssignmentActualWork - tsaov.ActualWorkBillable))>.01

    FYI  ... [program group] (commented out of the above sql) is an enterprise custom field that we use to group projects/sub-projects together for reporting.

    It seems my two main options are to:

    1. Have the timesheets resubmitted to re-apply the correct actuals to the assignment.     If I were just to address those where the difference was > 2 hours in a given timesheet period, that would be 33 timesheets that need to be recalled, made incorrect, saved, corrected and then resubmitted.  This would drive many undesired "collateral" task updates and mushroom into a pretty big effort.
    2. Update reports to pull actual work from timesheets due to unreliability of assignment actuals (this would be consistent with the "best practice" #3 referenced here ... I'll spare my editorial on this).  It is very hard for me to accept bad data ... even worse is the notion of selling a solution to my client that ignores it by design. 

    I really have no solid idea how this happens.  I have some theories but, ultimately, I need to deal with what is there quickly for end of year reporting and intercompany cost settlements. Then I'll start monitoring for occurrences to catch the instances more timely and, hopefully, isolate the behaviors/usages that may be causing it.

    I'm mainly hoping for input on:

    • any others have experience with this phenomena?  Or am I just lucky?
    • Any proven behaviors that cause this to happen and preventative measures to control?
    • Any recommendations for cleaning up other than the options I have above?

    It sure would be nice if the "Sync to Protected actuals" actually did what the feature name implies.  That would be my easy button.  I don't know what that actually syncs to, but it is definitely not the approved actuals stored in the timesheets.

    Thanks for any input.

    • Edited by Grady Byram Wednesday, December 11, 2013 3:50 PM typos, etc.
    Wednesday, December 11, 2013 1:58 AM

All replies

  • Hi Grady Byram, 

    Thanks for your effort, can you please modify the above query for adding resource capacity also. 




    Monday, January 8, 2018 7:14 AM