Find Over-Allocations in One Project Alone

See, Project Server, by design, does not have a mechanism by which to “cap” allocations for resources.  It seems like it might be nice for it to just come back with, “Nope, you can’t over-allocate that dude!” messages when you put say, 800 hours into a week for someone, but MS had other plans when they designed it.  All it does it turn red.  “Oh no, please don’t turn red at me, I can’t stand it!”

Not much of a threat.  Additionally, it also turns red if you allocate someone 8.1 hours in a day.  If you look at the view in weeks, the week is still red even if they are allocated ONLY for those 8.1 hours in that day. *weep*

I don’t know about your PMO, but ours doesn’t particularly like it when a resource has like 70 hours in a week on a single project.  Makes it hard to iron out any other scheduling issues – it’s hard to send a request for people to look over allocations when some fool has fed nonsense into the system.

So I worked out a way to find these suckers and report on it, so in the first wave of “allocation smack down”, we can get rid of the nonsense before we get down to business.

The most important piece of this query is the part where it “groups” a set of timephased data into a week, like so:

(CASE WHEN  MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate()), 0) and dateadd(wk, datediff(wk, 0, getdate()), 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w1]

OMG that is enough to hurt a brain!  But I promise that it makes sense.  The real beauty of this is that it doesn’t specify dates, it uses a time frame based on today, and therefore will always march forward as you go.  I have 8 of these, one for each week I want to show, which looks like this (showing only 3 for brevity):

(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate()), 0) and dateadd(wk, datediff(wk, 0, getdate()), 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w1],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+1, 0) and dateadd(wk, datediff(wk, 0, getdate())+1, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w2],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+2, 0) and dateadd(wk, datediff(wk, 0, getdate())+2, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w3],

…and so on.  The date modifiers are more fully explained from where I rightfully stole them from SQL Authority. (this is a great resource, btw!)

Now that you have your clumps of data, you have to sum them.  What I did was create a temp table (just put a full query into ()’s and then write ‘as x’ at the end, and select from THAT….

SELECT

SUM(w1) as [Week1],
SUM(w2) as [Week2],
SUM(w3) as [Week3]

Etc…

And one more special step – after your initial select statement and the x table, you have to first GROUP BY (annoying side affect of the SUM) and then put in your HAVING clause:

HAVING

SUM (w1) >40 OR
SUM (w2) >40 OR
SUM (w3) >40

Ok ok, this seems like a lot of explanation, but so often I find that folks post these great queries but don’t explain them, and then it’s difficult to edit and change them and make them your own.  Now that I’ve explained it, I’m going to put it together with the other necessary pieces, and you can just stick this puppy in your query and watch it go, I swear!

One quick aside- I know I’m a hack.  I know that there’s probably a simpler, easier, more streamlined way to do this – but I googled my butt off and didn’t find it, so please make suggestions if you have them, but keep calm and carry on if you just feel like ranting.

SELECT

SUM(w1) as [Week1],
SUM(w2) as [Week2],
SUM(w3) as [Week3],
SUM(w4) as [Week4],
SUM(w5) as [Week5],
SUM(w6) as [Week6],
SUM(w7) as [Week7],
SUM(w8) as [Week8],
ResourceName,
ProjectName

FROM

(SELECT
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate()), 0) and dateadd(wk, datediff(wk, 0, getdate()), 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w1],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+1, 0) and dateadd(wk, datediff(wk, 0, getdate())+1, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w2],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+2, 0) and dateadd(wk, datediff(wk, 0, getdate())+2, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w3],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+3, 0) and dateadd(wk, datediff(wk, 0, getdate())+3, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w4],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+4, 0) and dateadd(wk, datediff(wk, 0, getdate())+4, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w5],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+5, 0) and dateadd(wk, datediff(wk, 0, getdate())+5, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w6],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+6, 0) and dateadd(wk, datediff(wk, 0, getdate())+6, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w7],
(CASE WHEN MSP_EpmAssignmentByDay_UserView.TimeByDay between dateadd(wk, datediff(wk, 0, getdate())+7, 0) and dateadd(wk, datediff(wk, 0, getdate())+7, 6) then MSP_EpmAssignmentByDay_UserView.AssignmentWork else 0 end) as [w8],
MSP_EpmResource_UserView.ResourceName,
MSP_EpmProject_UserView.ProjectName

FROM
MSP_EpmResource_UserView

INNER JOIN
MSP_EpmAssignment ON
MSP_EpmResource_UserView.ResourceUID =MSP_EpmAssignment.ResourceUID
INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

INNER JOIN
MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID

WHERE
(MSP_EpmResource_UserView.ResourceIsGeneric <>1)) as x

GROUP BY
ResourceName,
ProjectName

 

HAVING
SUM (w1) >40 OR
SUM (w2) >40 OR
SUM (w3) >40 OR
SUM (w4) >40 OR
SUM (w5) >40 OR
SUM (w6) >40 OR
SUM (w7) >40 OR
SUM (w8) >40

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