Resources on PM Projects + Their Other Allocations too, I swear!

Time for the-holidays-bring-time-to-catch-up-and-be-brilliant happy dance!  I know it’s been a long time since I posted anything new.  Maybe that’s just because everything is solved…or maybe everything is working smoothly…you get the idea.

Anyway, I have finally figured out how to write the SQL query to show me ONLY resources assigned to any project of a certain PM AND their other assignments on other PMs projects.

You know, like the view you’ll see in a Resource Usage View in Project, but in a pretty report and for all a PMs projects at once.

They key is in some fancy join-work and grouping.  It’s pretty terrifying to look at, but as always, I’ll make the whole thing available to stick in your query, should you want it.

The magic is in joining the tables in your query more than once, but on different stuff.  Since the resource is the central point is this equation, I sort of think of it like this:

diagram

 Here’s the JOIN process, color coded for ease of use:

FROM

MSP_EpmProject_UserView PROJ

INNER JOIN
MSP_EpmTask_UserView TASK ON
PROJ.ProjectUID = TASK.ProjectUID

INNER JOIN
MSP_EpmAssignment ASN ON
TASK.TaskUID = ASN.TaskUID

INNER JOIN
MSP_EpmResource_UserView RES ON
ASN.ResourceUID = RES.ResourceUID

INNER JOIN
MSP_EpmAssignment ASN2 ON
ASN2.ResourceUID = RES.ResourceUID

INNER JOIN
MSP_EpmAssignmentByDay_UserView ASSIGN2 ON
ASN2.AssignmentUID = ASSIGN2.AssignmentUID

INNER JOIN
MSP_EpmTask_UserView TASK2 ON
TASK2.TaskUID = ASN2.TaskUID

INNER JOIN
MSP_EpmProject_UserView PROJ2 ON
PROJ2.ProjectUID = TASK2.ProjectUID

 This will get you part of the way there, but the math will be multiplied by the assignment count, so you have to add a group statement:

GROUP BY
RES.ResourceName,
PROJ2.ProjectName,
PROJ.ProjectOwnerName,
ASSIGN2.TimeByDay,
TASK2.TaskName,
RES.RBS,
ASSIGN2.AssignmentWork

The WHERE clause is pretty important as well – and probably not 100% perfect.  You have to limit both the AssignmentByDay table and the Assignment Table, or else you end up with resource on your list that are outside of the assignment range showing, which will just confuse people.

 Additionally,  the numbers will only add up correctly if you are filtering the report on ONE pm.  Otherwise it duplicates the numbers and doesn’t make sense.

 I’m planning to stuff this one into an Excel report in SharePoint so that the logged in PM can only see the resources assigned to their projects and their total allocations.  Hope this makes some sort of sense – color coding it was fun, but I also hope it will help lay it out.  I know that a lot of SQL masters could have written this easily, but for me it wasn’t exactly a no-brainer.

 I’ll leave you with the full query and a Happy New Year!

SELECT
ASSIGN2.AssignmentWork,
PROJ2.ProjectName,
PROJ.ProjectOwnerName as PM,
TASK2.TaskName,
ASSIGN2.TimeByDay,
RES.RBS,
RES.ResourceName,
(SELECT ResourceNTAccount from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = PROJ.ProjectOwnerResourceUID) as [NTAccount]

FROM
MSP_EpmProject_UserView PROJ

INNER JOIN
MSP_EpmTask_UserView TASK ON
PROJ.ProjectUID = TASK.ProjectUID

INNER JOIN
MSP_EpmAssignment ASN ON
TASK.TaskUID = ASN.TaskUID

INNER JOIN
MSP_EpmResource_UserView RES ON
ASN.ResourceUID = RES.ResourceUID

INNER JOIN
MSP_EpmAssignment ASN2 ON
ASN2.ResourceUID = RES.ResourceUID

INNER JOIN
MSP_EpmAssignmentByDay_UserView ASSIGN2 ON
ASN2.AssignmentUID = ASSIGN2.AssignmentUID

INNER JOIN
MSP_EpmTask_UserView TASK2 ON
TASK2.TaskUID = ASN2.TaskUID

INNER JOIN
MSP_EpmProject_UserView PROJ2 on PROJ2.ProjectUID = TASK2.ProjectUID

WHERE
RES.ResourceName IS NOT NULL AND
RES.ResourceIsActive <>0 AND
ASSIGN2.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)  AND
ASSIGN2.TimeByDay <= current_timestamp +90 AND
RES.RBS like ‘%staff%’ AND
TASK.TaskIsActive =1 AND
ASN.AssignmentFinishDate >= current_timestamp AND
ASN.AssignmentStartDate <= Current_timestamp+90 AND
ASSIGN2.AssignmentWork >0 AND
RES.ResourceIsGeneric =0 AND
ASN.AssignmentWork >0

GROUP BY
RES.ResourceName,
PROJ2.ProjectName,
PROJ.ProjectOwnerName,
ASSIGN2.TimeByDay,
TASK2.TaskName,
RES.RBS,
ASSIGN2.AssignmentWork,
PROJ.ProjectOwnerResourceUID

Advertisements

2 thoughts on “Resources on PM Projects + Their Other Allocations too, I swear!

  1. This is a great idea, i am just trying to create a similar report, but i am missing other resource usage capabilities like the capacity (availability) and the “Percent Allocation”. i want to have the percent allocation (Work / Capacity) only to sum it on the project level to know how many units are allocated for the project or for the same PM.

    Meaning, if Project A have Resource 1 allocated 0.8 (or 80%), Resource 2 allocated 1.0 and resource 3 allocated 1.1, i should have the totals of the project allocation as 2.9 (290%).

    And i need to add the time dimension (from TimeByDay table to show fiscal Qtrs and Months)

    Anyway of showing all of that? 🙂

    • Off the top of my head, I can address the TimeByDay question, but I’ll have to think longer on the rest. The TimeByDay_OLAP view can be joined to your query on the .TimeByDay field, and then you can use the fields there which include fiscal quarters and months. If you’re pulling the data into excel, I find that the “group” function in the pivot table generally works better for weeks and for months it would reduce the run-time of your query to not have to join in an additional TimeByDay table.

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