none
TimeTracking - Actual Costs on the actual day RRS feed

  • Question

  • Hi everyone,
    we are using Project Server 2010, where each project member reports their time using the Timesheet function. This way we update the tasks and the actual costs for a project.

    But sometimes the task in a project plan is not updated and the days for the task are different from the days the members are entering it in the timesheets. This could lead to small differences in "Actual Costs" if you create a financial report at the end of a month.

    We need a financial report, where the "Actual Costs" of a project are based on the dates on the timesheets, but not on the dates of the tasks in a project plan.

    One way to solve this would be to always have the tasks with the days uptodate. But this is not always achievable.

    Has anyone an idea on how to solve this ?

    Thanks

    Monday, August 6, 2012 11:25 AM

Answers

  • Under PWA>Server Settings>Timesheet Settings and Default, enable the Tied mode option to synchronise timesheet data with the project schedule. If you already have this and are still having discrepencies, then make sure you update your farm to use the latest Service Pack and Cumulative Update as there have been a few issues addressed around timesheet/task update discrepencies.

    When it comes to reporting you can most certainly read data from the timesheet table/viewes directly. The following query is a good starting point to get you going. You will need to wrap some grouping and change things around to suit your specifric needs though. Good luck!

    --------------------------------------------------------------------
    -- GLOBAL VARIABLE DECLARATIONS
    --------------------------------------------------------------------
    DECLARE @vuidResourceUIDs UNIQUEIDENTIFIER
    SET @vuidResourceUIDs = NULL

    DECLARE @vdtmStartDate DATETIME
    SET @vdtmStartDate = '1-aug-2012'

    DECLARE @vdtmEndDate DATETIME
    SET @vdtmEndDate = '8-aug-2012'

    DECLARE @vuidProjectUID UNIQUEIDENTIFIER
    SET @vuidProjectUID = NULL

    --------------------------------------------------------------------
    -- Timesheet Actuals For Selected Resources and project for selected date range
    --------------------------------------------------------------------
    SELECT    
     tsp.StartDate
     ,tsp.EndDate
     ,tsr.ResourceUID
     ,tsr.ResourceName AS TimeSheetResourceName   
     ,p.ProjectUID
     ,p.ProjectName 
     ,p.Customer
     ,p.Billable 
     ,pm.ResourceName AS IPMOProjectManager
     ,tst.TaskUID
     ,tst.TaskName
     ,tsa.TimeByDay 
    FROM MSP_TimesheetActual tsa
    INNER JOIN MSP_TimesheetLine tsl
     ON tsa.TimesheetLineUID = tsl.TimesheetLineUID
    INNER JOIN MSP_TimesheetProject tsProj
     ON tsl.ProjectNameUID = tsProj.ProjectNameUID
    INNER JOIN MSP_Timesheet ts
     ON  tsl.TimesheetUID = ts.TimesheetUID
    INNER JOIN MSP_TimesheetResource tsr
     ON ts.OwnerResourceNameUID = tsr.ResourceNameUID
    INNER JOIN MSP_TimesheetPeriod tsp
     ON ts.PeriodUID = tsp.PeriodUID
    INNER JOIN MSP_TimesheetTask tst
     ON tsl.TaskNameUID = tst.TaskNameUID
    LEFT OUTER JOIN MSP_EpmProject_UserView p
     ON tsProj.ProjectUID = p.ProjectUID
    LEFT OUTER JOIN MSP_EpmResource_UserView pm
     ON p.ProjectOwnerResourceUID = pm.ResourceUID
    WHERE 1 = 1
     AND (p.ProjectUID = @vuidProjectUID OR @vuidProjectUID IS NULL)
     AND (tsr.ResourceUID IN (@vuidResourceUIDs) OR @vuidResourceUIDs IS NULL)
     AND (tsa.TimeByDay BETWEEN @vdtmStartDate AND @vdtmEndDate) 
    GROUP BY
     tsp.StartDate
     ,tsp.EndDate
     ,tsr.ResourceUID
     ,tsr.ResourceName
     ,p.ProjectUID
     ,p.ProjectName 
     ,p.Customer
     ,p.Billable 
     ,pm.ResourceName
     ,tst.TaskUID
     ,tst.TaskName 
     ,tsa.TimeByDay 
    ORDER BY
     tsr.ResourceName
     ,tsa.TimeByDay 
     ,p.ProjectName 
     ,tst.TaskName


    Regards,

    Piet Remen
    http://pietremen.blogspot.com.au

    Monday, August 6, 2012 3:23 PM

All replies

  • Hi there--

    You may need to write a SSRS  or edit Excel reports (TimesheetActuals excel report) to get the Actual cost data basedon the dates from Timesheets. From Reporting database, You can join the MSP_TimesheetLine_UserView & MSP_EpmResource_UserView  to get the actual cost from the ActualWorkBillable & Standard Rate of Resource.

    Hope this 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

    Monday, August 6, 2012 12:02 PM
  • Under PWA>Server Settings>Timesheet Settings and Default, enable the Tied mode option to synchronise timesheet data with the project schedule. If you already have this and are still having discrepencies, then make sure you update your farm to use the latest Service Pack and Cumulative Update as there have been a few issues addressed around timesheet/task update discrepencies.

    When it comes to reporting you can most certainly read data from the timesheet table/viewes directly. The following query is a good starting point to get you going. You will need to wrap some grouping and change things around to suit your specifric needs though. Good luck!

    --------------------------------------------------------------------
    -- GLOBAL VARIABLE DECLARATIONS
    --------------------------------------------------------------------
    DECLARE @vuidResourceUIDs UNIQUEIDENTIFIER
    SET @vuidResourceUIDs = NULL

    DECLARE @vdtmStartDate DATETIME
    SET @vdtmStartDate = '1-aug-2012'

    DECLARE @vdtmEndDate DATETIME
    SET @vdtmEndDate = '8-aug-2012'

    DECLARE @vuidProjectUID UNIQUEIDENTIFIER
    SET @vuidProjectUID = NULL

    --------------------------------------------------------------------
    -- Timesheet Actuals For Selected Resources and project for selected date range
    --------------------------------------------------------------------
    SELECT    
     tsp.StartDate
     ,tsp.EndDate
     ,tsr.ResourceUID
     ,tsr.ResourceName AS TimeSheetResourceName   
     ,p.ProjectUID
     ,p.ProjectName 
     ,p.Customer
     ,p.Billable 
     ,pm.ResourceName AS IPMOProjectManager
     ,tst.TaskUID
     ,tst.TaskName
     ,tsa.TimeByDay 
    FROM MSP_TimesheetActual tsa
    INNER JOIN MSP_TimesheetLine tsl
     ON tsa.TimesheetLineUID = tsl.TimesheetLineUID
    INNER JOIN MSP_TimesheetProject tsProj
     ON tsl.ProjectNameUID = tsProj.ProjectNameUID
    INNER JOIN MSP_Timesheet ts
     ON  tsl.TimesheetUID = ts.TimesheetUID
    INNER JOIN MSP_TimesheetResource tsr
     ON ts.OwnerResourceNameUID = tsr.ResourceNameUID
    INNER JOIN MSP_TimesheetPeriod tsp
     ON ts.PeriodUID = tsp.PeriodUID
    INNER JOIN MSP_TimesheetTask tst
     ON tsl.TaskNameUID = tst.TaskNameUID
    LEFT OUTER JOIN MSP_EpmProject_UserView p
     ON tsProj.ProjectUID = p.ProjectUID
    LEFT OUTER JOIN MSP_EpmResource_UserView pm
     ON p.ProjectOwnerResourceUID = pm.ResourceUID
    WHERE 1 = 1
     AND (p.ProjectUID = @vuidProjectUID OR @vuidProjectUID IS NULL)
     AND (tsr.ResourceUID IN (@vuidResourceUIDs) OR @vuidResourceUIDs IS NULL)
     AND (tsa.TimeByDay BETWEEN @vdtmStartDate AND @vdtmEndDate) 
    GROUP BY
     tsp.StartDate
     ,tsp.EndDate
     ,tsr.ResourceUID
     ,tsr.ResourceName
     ,p.ProjectUID
     ,p.ProjectName 
     ,p.Customer
     ,p.Billable 
     ,pm.ResourceName
     ,tst.TaskUID
     ,tst.TaskName 
     ,tsa.TimeByDay 
    ORDER BY
     tsr.ResourceName
     ,tsa.TimeByDay 
     ,p.ProjectName 
     ,tst.TaskName


    Regards,

    Piet Remen
    http://pietremen.blogspot.com.au

    Monday, August 6, 2012 3:23 PM