“Crazy” Assignment Units & 0 Duration Tasks with Work

Raise your hand if you’ve ever seen one of these scenarios:

A task with Work, but 0 days duration:

starlord6

Seemingly crazy and nonsensical Assignment Units to the tune of hundreds of thousands of percent:

starlord5

Why is this happening to me?

Both scenarios are initially caused by the following:

Adding a resource at 0% units to a task that has unassigned work. Like this:

starlord2

I can see the wisdom in adding a resource to a task with 0% Units – on a milestone, for example, or as oversight on a task which has other resources allocated.  But if the task has any unassigned work and you allocate a resource at 0%, BUME, project loses its mind.  Actually, to be more accurate, it’s actually doing something “kind of” smart to keep from having to divide by zero – the 0 duration task really becomes a 1 minute task duration task, and that’s what it’s basing its math on.

Will it hurt me?

If you’re using a Fixed Work task, we all know by now that the Assignment Units are just there for show, so in that case it will just look weird and confuse you. On a Fixed Unit task, however, if you update that duration, things get pretty darn ugly:

starlord1

Really, that’s too much work for even Peter Quill to complete in a day, even if he DOES have 12% of a plan!

What’s your super smart fix, Elli?

The long and short of it is that you have to just change the task data until it says what you want.  On a Fixed Work task, if you just want that whacko number to disappear, you have to figure out the closest correct Assignment Unit and update it.  On a Fixed Unit task, it’s a two part process.  You can either change the Units first, then the Duration, or change the Work first and then the Units. Either way, it’s just a couple of tweaks.

How do I know if this is happening in my Environment?

This is the fun part. And by fun, I mean it’s the most fun I’ve had all week, but it doesn’t eclipse my excitement about Guardians of the Galaxy Vol. 2 coming out next week! I digress.

I wrote a quick little query you can run to find the data. For the Tasks with Work and 0 Duration, all you have to do is put into your WHERE statement that you want … (ok, do I have to spell it out?) tasks with 0 duration, and more than 0 work:

WHERE
MSP_EpmTask_UserView.TaskDuration = 0 AND
MSP_EpmTask_UserView.TaskWork > 0

For the Assignment Units, you’ll want to leverage the “AssignmentPeakUnits” column out of the Assignments view.  What this tells you is the maximum units a resource is assigned to on a task, and as long as you put something in your WHERE statement to narrow it down to items over 100% (or over 120%,1000%, depending on how you use generics etc.) then you can get a quick and nifty little list of what you need.  You might also want to filter based on tasks that have no remaining work (if it ain’t broke, don’t fix it, right?)

WHERE
MSP_EpmAssignment_UserView.AssignmentPeakUnits >1000 AND
MSP_EpmAssignment_UserView.AssignmentRemainingWork >0

When you combine those two as two sets with an OR between them, you should get your full list of projects and tasks that have these kinds of ugliness to fix. As always, full query at the bottom.  Have fun reigning in your over-achievers!

SELECT

MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskName,
MSP_EpmAssignment_UserView.AssignmentPeakUnits,
MSP_EpmTask_UserView.TaskDuration,
MSP_EpmTask_UserView.TaskWork

FROM

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

INNER JOIN
MSP_EpmAssignment_UserView ON
MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID

INNER JOIN
MSP_EpmResource_UserView ON
MSP_EpmAssignment_UserVIew.ResourceUID = MSP_EpmResource_UserView.ResourceUID

WHERE
(MSP_EpmAssignment_UserView.AssignmentPeakUnits >1000 AND
MSP_EpmAssignment_UserView.AssignmentRemainingWork >0) OR
(MSP_EpmTask_UserView.TaskDuration =0 AND
MSP_EpmTask_UserView.TaskWork >0)

 

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