Don’t Make Me Draw This Capacity Line With a Crayon!!!

This one all started about a year ago, if you can believe it.

The problem:

You can view capacity for resources vs work, but if you have the nerve to try to break out the work into projects, PS has a nervous breakdown. It starts adding capacity up against each project, making the numbers completely useless. The first time someone asked me if I could show them Project Work for a team in a bar graph with a line across to show capacity, I didn’t think it would be too hard to whip up a nice little report.  Boy was I wrong.

I tried just about everything, including the “official” answer, which is to use the “Resource /Capacity versus Demand Heatmap by Position Role” from the Project Server 2007 report pack, but I couldn’t get even that to play nice.

I slowly chipped away at this and thought about it and made drawings and noodle mosaics and sacrificed some goats to the SQL goddesses before I came up with a solution. It’s not bullet-proof, but the query, a bit of Pivot Chart manipulation, and an itty bitty VB script can make the situation very workable. Ready? Here we go:

The first step is to “trick” the query (I like tricking queries, don’t I?) into thinking that Capacity is its very own project. The way I’ve done this is to union two queries. Here’s the simplified version, I’ll post the whole query at the end:

SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
AND STUFF…

FROM

MSP_EpmAssignmentByDay_UserView

JOIN SOME STUFF

MSP_EpmProject_UserView

UNION ALL <—— there must be equal parts above and below, and they have to match up —-

SELECT

MSP_EpmResource_UserView.ResourceBaseCalendar, <<—or any other text string value which is the same for all resources>>
MSP_EpmResourceByDay_UserView.TimeByDay,
MSP_EpmResourceByDay_UserView.Capacity as [AssignmentWork]

AND STUFF…

FROM

MSP_EpmResource_UserView

JOIN SOME STUFF

MSP_EpmResourceByDay_UserView

See what we did there? Now the capacity will show up in your report on the same line with the other projects. This does, of course, totally hose your totals. Don’t try to total it, that’s not what this is about. This was in pursuit of a line, remember?

Now that it’s in your report, rename the “capacity project” (in our case it’s Standard, since that’s the name of the calendar that all resources use) to “Capacity” or something helpful.
Now create a pivot chart using a stacked bar. Your “capacity Project” will stack up with everything else, which is annoying. Go ahead and right click on that sucker, then change the series chart type to a line.

Look, I didn’t have to use a crayon!!!!!!!

There’s a problem, though. Any time you refresh the report, the numbers that the chart associates with the projects get scrambled and your line turns back into a bar. While it’s easy enough to reset this manually, I found a much better way – a button with a little macro attached that will reset the whole thing. Here’s the VB code:
Sub chart()

‘ chart Macro

On Error Resume Next

ActiveSheet.ChartObjects(“Chart 2”).Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SeriesCollection(“capacity”).Select
ActiveChart.SeriesCollection(“capacity”).ChartType = xlLine
End Sub
Neat and tidy! Now just insert a nifty looking button, assign your macro to it (I named mine “Reset Chart Style”) and presto!

EDIT:  I have improved this – I was getting tired of clicking said little button, so I found a way to get the chart to automatically update whenever you change the data. (using slicers or refreshing the data, either one will trigger the above macro)  It’s a bit hinky to set up, but here’s the basics:

In my worksheet, the pivot table is on a separate page from the pivot chart, so I had to add this line to code above:

Sheets(“Chart”).Select

Then, on the Sheet data part of the VBA code, I added this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Application.OnTime Now() + TimeValue(“00:00:02”), “chart”  <— this adds a pause to give the chart time to catch up with the table, otherwise excel will crash.  1 second is probably enough. –>

End Sub

Run “chart”

End Sub

Now this sucker will update on it’s own!  It’s so pretty, I could almost cry.

Now of course, my real query has RBS and all sorts of other delicious bits of information (as well as some filtering because that boyo can take a while to run otherwise) and you can insert slicers and slice and dice away at the data like a pro! This puppy impressed my boss, his boss and her boss so much you have no idea.

Here’s the whole code (I removed our custom fields, so it should be happy as is as long as you don’t have multiple base calendars, in which case I recommend creating some kind of custom field and using that) (oh, and check the RBS filter because not everyone uses the .staff designation) and BANG, you’re ready to go.

Put away that box of crayons, the glue and the macramé, this is going to work.

SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.RBS,
MSP_EpmResource_UserView.ResourceLatestAvailableTo,
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_EpmResource_UserView.ResourceName IS NOT NULL) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’)

UNION ALL

SELECT
MSP_EpmResource_UserView.ResourceBaseCalendar,
MSP_EpmResourceByDay_UserView.TimeByDay,
MSP_EpmResourceByDay_UserView.Capacity as [AssignmentWork],
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.ResourceIsGeneric,
MSP_EpmResource_UserView.RBS,
MSP_EpmResource_UserView.ResourceLatestAvailableTo,
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.ResourceName IS NOT NULL) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’)

EDIT: Here is a nice example of my pretty pretty picture!

Advertisements

3 thoughts on “Don’t Make Me Draw This Capacity Line With a Crayon!!!

  1. Great idea. I like it. Unfortunately for us we have several calendars to support all of the different schedules : ( I guess i could filter for just one of the calendars for a specific set of projects : ) Great job, keep them coming.

    • You know, it also occurs to me that you could do something like this:

      CASE WHEN MSP_EpmResource_UserView.ResourceName IS NOT NULL THEN ‘Capacity’ ELSE ‘NULL’ END as [ProjectName]

      and not have to create a field at all. 🙂

  2. Actually, Tony, the field you use for that part of the query doesn’t have to be the resource calandar, it can be any resource field that has a NAME value. If you don’t already have anything else you can use, you could always create a resource field and enter the same data for all your resources, then filter on that in the WHERE statement of that portion of the query, and it should work. Give it a try, I hope it helps!

    Thanks for reading!

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