如何验证项目经理是否批准审批中心Project Server 2010中的时间 [英] How to verify Project Managers are approving time in Approval Center Project Server 2010

问题描述

我公司设置了Project Server 2010，以便每个项目经理都需要访问审批中心并批准报告给他们项目的任何时间。 问题是大多数PM不这样做，或者是在非常延迟的情况下这样做。

是否有办法监控审批中心以确保所有时间都至少得到批准每隔一周？

解决方案

这是一个SQL查询开始群组。您可以执行的操作是查找未查看时间的任务管理器和/或时间表管理器。  移动审批时状态会发生变化。 需要注意的是，只有部分
状态才会在已发布的数据库中找到。   Microsoft不支持从数据库中提取数据。  原因是记录锁定可能导致 问题，因此您注意到我的查询中有一个NOLOCK可以降低风险。

有两种状态。 一个用于时间表，另一个用于项目经理。  我认为您只对项目经理的批准感兴趣，因此我会删除时间表管理器及其连接。需要。

我希望这能让你开始

SELECT

  TSR.ResourceName ，TSP。 PeriodName ，TSS.Description" Project Manager"

 ，TSS2.Description" Resource Manager" ，ER2.ResourceName as'Timesheet Manager'

  ;，ER3.ResourceName为"状态管理器"，ER.ResourceEmailAddress +';'

 ，TSP.StartDate ，SUM（TSA.ActualWorkBillable）为'每周总计'
FROM

    PUB.MSP_TIMESHEETS PTS（nolock）

  INNER JOIN MSP_TIMESHEET TS     ON TS.TimesheetUID = PTS.TS_UID

  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 MS P_EpmTask_UserView TU     ON TU.TaskUID = EA.TaskUID

  RIGHT JOIN MSP_EpmResource ER3    ON ER3.ResourceUID = TU.TaskStatusManagerUID 


WHERE   TSS2.Description ='已提交'

GROUP BY

  TSR.ResourceName  ，TSP.PeriodName  ，TSP.StartDate  ，ER。 ResourceEmailAddress

 ，TSS.Description  ，TSS2.Description  ，ER2.ResourceName  ，ER3.ResourceName

ORDER BY

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

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?

解决方案

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

SELECT
 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'
FROM
    PUB.MSP_TIMESHEETS PTS (nolock)
 INNER JOIN MSP_TIMESHEET TS    ON TS.TimesheetUID = PTS.TS_UID
 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'
GROUP BY
 TSR.ResourceName  , TSP.PeriodName  , TSP.StartDate  , ER.ResourceEmailAddress
 , TSS.Description  , TSS2.Description  , ER2.ResourceName  , ER3.ResourceName

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

这篇关于如何验证项目经理是否批准审批中心Project Server 2010中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！