Query to find Projects with summary level Resource assignment

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 a EPM
system health check data diagnostic for one of our clients to detect the
projects those are having the summary resource assignments. I response I used
the below SQL query. I Hope this helps you.

–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

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: