Raise your hand if you’ve ever seen one of these scenarios:
A task with Work, but 0 days duration:
Seemingly crazy and nonsensical Assignment Units to the tune of hundreds of thousands of percent:
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:
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:
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)
