3 Simple Compliance Reports (Part 2)

Say No to Summary Resource Assignments!

Once again I’d like to put some info out there which will help you ensure that the users in your Project Server environment aren’t breaking stuff without meaning to!   We all know that bad things happen (work doubles, projects crumble, volcanoes erupt…) when a resource is assigned to a Summary Task!  I’ve put together this nifty little query to help you seek out and destroy any such transgressions with ease!

The real key is just the WHERE statement:

WHERE
(MSP_EpmTask_UserView.TaskIsSummary =1) AND
(MSP_EpmResource_UserView.ResourceName IS NOT NULL)

See what we did there?  It’s so easy a drunken badger could do it!  Maybe.   Notice below that I’ve got the TaskIndex in my query – that lets you give very specific instructions to anyone who needs to go and fix it.   

I’ve got one more of these easy tips for Compliance reports, then I’ll get into the sweet little Excel trick that gives you an easy scorecard for your PMs.  I know, you’re all waiting with bated breath, but it’s gonna be next week before you see it.  Until then, sleep well and dream of tidy SQL queries…like this one:

SELECT
MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmProject_UserView.ProjectName,     
MSP_EpmTask_UserView.TaskName,             
MSP_EpmTask_UserView.TaskIndex,
MSP_EpmTask_UserView.TaskIsSummary,    
MSP_EpmResource_UserView.ResourceName

FROM
MSP_EpmProject_UserView INNER JOIN
MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

INNER JOIN
MSP_EpmAssignment ON
MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

INNER JOIN
MSP_EpmResource_UserView ON
MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

WHERE
(MSP_EpmTask_UserView.TaskIsSummary =1) AND
(MSP_EpmResource_UserView.ResourceName IS NOT NULL)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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