The Query to Find Projects With a Summary Level Resource Assignment

Posted By Posted by: EPM Partners on October 8, 2013

Problem:

  1. Create unpredictable durations and assignment values in the project
  2. If you have the same resource assigned to the summary task and its subtask, you will not be able to resolve an over allocation if the summary task is automatically scheduled
  3. In addition, fields such as %Comp are usually used on summary tasks to indicate amount of progress made on all its subtasks. However if there is an assignment on the summary task as well, then the %Comp is used for tracking both the progress made on subtasks and progress from that summary assignment. Under these circumstances, Project may not be able track specific progress accurately

Environment: Project Server 2010

Requirement: Recently, we had a requirement as part of an EPM system health check, data diagnostic, for one of our clients to detect the projects for those that are having the summary resource assignments. To respond I used
the below SQL query.

–Query to display the Project Names having Summary Level resource assignment

Select distinct P.ProjectName, COUNT(a.AssignmentUID)

FROM

MSP_EpmProject p INNER JOIN MSP_EpmTask t

ON p.ProjectUID=t.projectuid

INNER JOIN MSP_EpmAssignment A on t.taskUID=A.TaskUID

WHERE  t.TaskIsSummary=1

GROUP By P.ProjectName, t.taskuid

We hope this helps you.

 

Want more news, information, events and blogs from EPM Partners?


Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *