Only Over-Allocated Resources in One Report!

Like a mad scientist inventor locked in a basement witout eating or drinking, I have sat myself down during the relative calm of the holidays and once and for all solved the over allocation report for Project Server issue.

My previous post about this only shows allocations within a single project that are over the threshold.  I have retitled that, since I have decided it was misleading.

I’m not really ready to post this query because it’s SO UGLY, but it works, and it doesn’t chug forever, either.  So I’ll let you see the hideous prototype, even though it makes me look worse than Emmit Brown with his hair standing on end and a band-aid across his forehead.  Well maybe not worse, but close!

The trick is to use the methods from before (grouping work into clumps between week dates) and then make a temp table in your query to pull the names out of, then only show those names when  you pull the work out of a different table in your query.

The really hard part was getting it to group/join right so that it didn’t only show assignments that were too high, but actually used the sum of the week for each resource individually.    Here are the “magic” parts of the query, I’ll post the whole thing at the end.
Part 1 – grouping hours into weeks

As I said, this was something I talked about in a previous post, but it only summed the hours per project, not at a resource level. 

(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],

Those are two week long groupings.

Now, when you pull those out of the table, you’ll want to sum them.  (Trust me, otherwise it kind of goes insane and the results look as feasable as a flying steam train) 

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

And in the having part of your query, that’s where you’ll specify your criteria.  I’m sure that different PMOs have different thresholds, and you may also want to use this to look for under allocations, so this is where you do that:

HAVING
SUM (w1) >50 OR
SUM (w2) >50

etc…

Part 2 – Keep that temp table IN THE DARK

You have to get that temp table to associate itself enough that if filters the right data, but doesn’t start associating everything and filter out too much.  For that reason, I used the “Select statement within a Select statement” trick and popped this into my select statement on its own:

(SELECT ResourceName FROM MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = weeks.ResourceUID) as [ResourceName]

This means that it’s lined up with other selected columns, like so:

SELECT
things,
stuff,
(SELECT ResourceName FROM MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = weeks.ResourceUID) as [ResourceName],
andallthat

FROM

etc…

This looks clumsy as heck, but just wait until you see how many extra tables and random junk this sucker has… (feel free to scroll on and grab the whole thing if I’m boring you…)

So your first “temp” table is really a table within a table with a select within a select, looking something like this:

(SELECT
SUM(weeks.w1) as [Week1],
SUM(weeks.w2) as [Week2],
SUM(weeks.w3) as [Week3],
SUM(weeks.w4) as [Week4],
SUM(weeks.w5) as [Week5],
SUM(weeks.w6) as [Week6],
SUM(weeks.w7) as [Week7],
SUM(weeks.w8) as [Week8],
weeks.ResourceUID,
(SELECT ResourceName FROM MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = weeks.ResourceUID) as [ResourceName]
FROM

(SELECT
MSP_EpmAssignment_UserView.ResourceUID,
MSP_EpmAssignment_UserView.AssignmentUID,
(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]
FROM
MSP_EpmAssignment_UserView

INNER JOIN  

MSP_EpmAssignmentByDay_UserView  ON
MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID                         
AND MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID                         
AND MSP_EpmAssignment_UserView.TaskUID = MSP_EpmAssignmentByDay_UserView.TaskUID

WHERE
MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)  AND
MSP_EpmAssignmentByDay_UserView.TimeByDay <= current_timestamp +70 )as weeks

GROUP BY
weeks.ResourceUID

HAVING
SUM (w1) >50 OR
SUM (w2) >50 OR
SUM (w3) >50 OR
SUM (w4) >50 OR
SUM (w5) >50 OR
SUM (w6) >50 OR
SUM (w7) >50 OR
SUM (w8) >50) as names
Feh.  Scary, I know.  If someone else knows a better way to join this junk up, do tell!  I’m going to run it by one of our SQL gurus when he returns from his vacation, and I’ll let you know how hard he laughed at me, if nothing else.

So now all we have to do (haha) is join in the second table (third, if you want to get technical) that pulls the allocations.  You’d just use whatever your standard allocations query is, like so:

LEFT OUTER JOIN

(SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskIsActive,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.ResourceUID

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_EpmResource_UserView.ResourceName IS NOT NULL) AND
(MSP_EpmResource_UserView.ResourceIsActive <>0) AND
(MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ) AND
(MSP_EpmAssignmentByDay_UserView.TimeByDay <= current_timestamp +70) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’) AND
(MSP_EpmTask_UserView.TaskIsActive <>0)) as hours

Now, remembering to keep that names table as in the dark as possible, you join this table in like so:

ON names.ResourceUID = hours.ResourceUID

giving it nothing but the resourceUID to join up with.

We’re almost done!  Now you just have to select the junk you want to actually show in your report.  We don’t want to look at all those ugly week totals, right?  We just want what is relevant:

SELECT
names.ResourceName,
hours.TimeByDay,
hours.ProjectName,
hours.AssignmentWork

and viola! (which is different than a violin)  Now you have a query that only brings in the names of anyone who is wrongly allocated according to your standards.  No more sifting through those jerks with pretty allocations!

As always, I leave you with the whole darn (UGLY) thing.  Happy New Year and good querying!
SELECT
names.ResourceName,
hours.TimeByDay,
hours.ProjectName,
hours.AssignmentWork

FROM

(SELECT
SUM(weeks.w1) as [Week1],
SUM(weeks.w2) as [Week2],
SUM(weeks.w3) as [Week3],
SUM(weeks.w4) as [Week4],
SUM(weeks.w5) as [Week5],
SUM(weeks.w6) as [Week6],
SUM(weeks.w7) as [Week7],
SUM(weeks.w8) as [Week8],
weeks.ResourceUID,
(SELECT ResourceName FROM MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = weeks.ResourceUID) as [ResourceName]
FROM

(SELECT
MSP_EpmAssignment_UserView.ResourceUID,
MSP_EpmAssignment_UserView.AssignmentUID,
(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]
FROM
MSP_EpmAssignment_UserView

INNER JOIN  

MSP_EpmAssignmentByDay_UserView  ON
MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID                         
AND MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID                         
AND MSP_EpmAssignment_UserView.TaskUID = MSP_EpmAssignmentByDay_UserView.TaskUID

WHERE
MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)  AND
MSP_EpmAssignmentByDay_UserView.TimeByDay <= current_timestamp +70 )as weeks

GROUP BY
weeks.ResourceUID

HAVING
SUM (w1) >50 OR
SUM (w2) >50 OR
SUM (w3) >50 OR
SUM (w4) >50 OR
SUM (w5) >50 OR
SUM (w6) >50 OR
SUM (w7) >50 OR
SUM (w8) >50) as names

LEFT OUTER JOIN
(SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskIsActive,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.ResourceUID

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_EpmResource_UserView.ResourceName IS NOT NULL) AND
(MSP_EpmResource_UserView.ResourceIsActive <>0) AND
(MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ) AND
(MSP_EpmAssignmentByDay_UserView.TimeByDay <= current_timestamp +70) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’) AND
(MSP_EpmTask_UserView.TaskIsActive <>0)) as hours ON names.ResourceUID = hours.ResourceUID

Advertisements

One thought on “Only Over-Allocated Resources in One Report!

  1. Your query makes my life very much easier.. 😀
    Very nice job! I haven’t expect to find something like that, usually is pretty difficult to find more “specific things” like this for Project Server…

    Thanks very much!!

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