How to verify Project Managers are approving time in Approval Center Project Server 2010 RRS feed

  • Question

  • My company has Project Server 2010 set up so that each Project Manager needs to access Approval Center and approve any time reported to their projects.  The problem is the majority of PMs do not do this, or do it on a very delayed basis.

    Is there a way to monitor Approval Center to ensure that all time is being approved at least on an every other week basis?

    Tuesday, September 2, 2014 8:46 PM

All replies

  • Here is a SQL query to get you started.  What you can do is right a query to find task managers and/or timesheet managers that have not approved time.   The status changes as the approval is moved.  The thing to note, is that some of the status is only found in the published database.   Microsoft does not support pulling data out of the database.  The reason is that record locks could cause  issues and thus you notice I have a NOLOCK in my query to reduce the risk.

    There are two status.  One is for timesheet and the other is for project managers.  I think you are only interested in project managers approval, so I would remove the timesheet manager and the joins that it  requires.

    I hope this gets you started

     TSR.ResourceName , TSP.PeriodName , TSS.Description "Project Manager"
     , TSS2.Description "Resource Manager" , ER2.ResourceName as 'Timesheet Manager'
     , ER3.ResourceName as 'Status Manager' , ER.ResourceEmailAddress + ';'
     , TSP.StartDate , SUM(TSA.ActualWorkBillable) as 'Weekly Total'
        PUB.MSP_TIMESHEETS PTS (nolock)
     RIGHT JOIN MSP_TimesheetResource TSR  ON TS.OwnerResourceNameUID = TSR.ResourceNameUID
     RIGHT JOIN MSP_TimesheetPeriod  TSP   ON TS.PeriodUID = TSP.PeriodUID
     INNER JOIN MSP_TimesheetLine TSL
      ON TSL.TimesheetUID = TS.TimesheetUID  INNER JOIN MSP_TimesheetStatus TSS
      ON TSS.TimesheetStatusID = TS.TimesheetStatusID 
     INNER JOIN MSP_TimesheetStatus TSS2   ON TSS2.TimesheetStatusID = PTS.TS_STATUS_ENUM 
     INNER JOIN MSP_TimesheetActual TSA   ON TSA.TimesheetLineUID = TSL.TimesheetLineUID
     INNER JOIN MSP_EpmResource ER   ON ER.

    ResourceUID = TSR.ResourceUID
     INNER JOIN MSP_EpmResource_UserView RUV   ON RUV.ResourceUID = TSR.ResourceUID
     INNER JOIN MSP_EpmResource ER2   ON ER2.ResourceUID = Ruv.ResourceTimesheetManagerUID
     INNER JOIN MSP_EpmAssignment EA   ON EA.AssignmentUID = TSL.AssignmentUID
     RIGHT JOin MSP_EpmTask_UserView TU     ON TU.TaskUID = EA.TaskUID
     RIGHT JOIN MSP_EpmResource ER3   ON ER3.ResourceUID = TU.TaskStatusManagerUID 
    WHERE   TSS2.Description = 'Submitted'
     TSR.ResourceName  , TSP.PeriodName  , TSP.StartDate  , ER.ResourceEmailAddress
     , TSS.Description  , TSS2.Description  , ER2.ResourceName  , ER3.ResourceName

     ER2.ResourceName  ,TSR.ResourceName  ,ER3.ResourceName ,TSS.Description  , TSP.PeriodName

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

    Wednesday, September 3, 2014 3:30 AM
  • Michael,  thank you for sending the SQL query.  I have been looking at it in order to understand it better.  If I have the correct understanding of it I am not sure it will work for what I need.  Even though timesheet tasks are sitting in Approval Center waiting to be approved, the associated timesheet itself is auto Approved.  So I believe the MSP_TimeshhetStatus.TimesheetStatusID could equal Approved even though there tasks have not been approved in PWA Approval Center.

    The company did not want to impact any data going to Finance, so the timesheets are auto approved.  Approved the tasks in Approval Center only updates the Actuals in the assocaited project plan(s).

    I need someway to tell when there are tasks sitting in Approval Center waiting to be approved, even though the timesheet they are associated to is most likely in Approved status.  I've been looking at the various tables in both the Published and Reporting databases but so far have not been able to find anything I can use.  If I am not understanding your SQL query correctly please let me know.


    Wednesday, September 17, 2014 7:43 PM
  • Hi

    what you want to see is not really easy to find in the database.

    The key table is MSP_ASSIGNMENT_TRANSACTIONS in the published database.

    The column ASSN_TRANS_STATE_ENUM is the important one. MSDN shows you the values for it:

    1 is waiting to be approved.

    Try something like this:

    select p.proj_name,r.res_name,at.mod_date from msp_projects p inner join msp_assignments a on a.proj_uid=p.proj_uid
    inner join msp_assignment_transactions at on a.assn_uid=at.assn_uid
    inner join msp_resources r on a.wres_uid_manager =r.res_uid
    where at.assn_trans_state_enum=1

    Hope that helps

    Kind regards


    Christoph Muelder | Senior Consultant, MCTS, MCSE | SOLVIN information management GmbH, Germany

    Thursday, September 18, 2014 6:59 PM