Capacity in a Perfect World!

One of our portfolio manager types here asked for something that initially didn’t sound plausible to me, but after thinking about it for a while I  not only figured out a way to do it, it also began to dawn on me how it would be useful.

The capacity information that comes out of Project Server is based on holidays and end dates, etc, which is great when you’re trying to give evidence that your contract resources need their contracts extended.

However, what it doesn’t show you is how much work the resoruces could handle in a perfect world.  So I give you the “Best Case Capacity” or “Ideal Capacity” line.  It’s still based on the same principle of tricking the query into thinking of capacity as a project, but instead of pulling actual capacity, you have to use the “something out of nothing” method and put a piece like this in the resource portion of your union statement:

CASE WHEN MSP_EpmResourceByDay_UserView.TimeByDay between current_timestamp and current_timestamp+360 then 5.7 else null end as [AssignmentWork]

See what we did there?  You just divide 40 by 7 and put that number into each day.  This wouldn’t work so well if you were trying to look day by day, of course, but you could always rewrite it to only use the days per week you wanted it to, I suppose.  We only look at weeks or months so it’s never been an issue for me.

At any rate, this will give you the “if we extend all our contractors and never take any holidays…” capacity, which may help you in certain situations. Follow the instructions in my original post  for how to set this baby up in Excel and you’re good to go.   As always, the full query is posted below.

Have fun!

SELECT
ProjectName,
AssignmentWork,
RBS,
“Staff Type”,
Skills,
Month,
Year,
ResourceName

FROM
(SELECT

MSP_EpmProject_UserView.ProjectName,
DATENAME(month, MSP_EpmAssignmentByDay_UserView.TimeByDay) as [Month],
DATENAME(year, MSP_EpmAssignmentByDay_UserView.TimeByDay) as [Year],
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.RBS,
MSP_EpmResource_UserView.”staff type”,
MSP_EpmResource_UserView.Skills,
MSP_EpmResource_UserView.ResourceCount,
MSP_EpmResource_UserView.ResourceName

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_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) AND
(MSP_EpmAssignmentByDay_UserView.AssignmentWork >0) AND
(MSP_EpmResource_UserView.ResourceIsActive <>0) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’)

UNION ALL

SELECT
MSP_EpmResource_UserView.ResourceBaseCalendar,
DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month],
DATENAME(year, MSP_EpmResourceByDay_UserView.TimeByDay) as [Year],
CASE WHEN MSP_EpmResourceByDay_UserView.TimeByDay between current_timestamp and current_timestamp+360 then 5.7 else null end as [AssignmentWork],
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.RBS,
MSP_EpmResource_UserView.”staff type”,
MSP_EpmResource_UserView.Skills,
MSP_EpmResource_UserView.ResourceCount,
MSP_EpmResource_UserView.ResourceName

FROM
MSP_EpmResource_UserView

INNER JOIN
MSP_EpmResourcebyDay_UserView ON
MSP_EpmResourcebyDay_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

WHERE
(MSP_EpmResource_UserView.ResourceIsActive <>0) AND
(MSP_EpmResource_UserView.ResourceIsGeneric =0) AND
(MSP_EpmResourceByDay_UserView.TimeByDay >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’))as x

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