The (Relatively) Painless Way to Show Resource Allocation in a Gantt Chart

Microsoft Project lets you assign different levels of work to each task and resource, but getting this information out of Project in a meaningful way is another story. Here is what a project plan looks like with resource units turned on:

Resource Loading

The “Resource Names” field is basically a text field with the resource allocation percentages duct-taped onto the end, which makes it pretty worthless when it comes to reporting. In this post, we’ll show you how to clean this data up in Project, and then use OnePager Pro to create an intuitive resource allocation report that looks like this (click to zoom in):

Resource Allocation Percentages

The first step is to use a formula to separate the resource allocation percentages into a separate field where you can actually use them. If you’re not an expert in Microsoft Project formulas, review this post first, and then use this formula to do the separation:

IIf(InStr([Resource Names],"[")>0,(Mid([Resource Names],InStr([Resource Names],"[")+1,(InStr([Resource Names],"%")-InStr([Resource Names],"[")))),"100%")

It’s a little daunting, but it works, so you can blindly copy and paste if you need to. Once you apply the formula, the resource allocation values will appear in a separate column. In this case we’ve used “Text 30:”

Resource Loading Parsed

Now, you can fire up OnePager Pro, using the “Multi-Resource Allocation View” template as a starting point.  You change your starting template in OnePager by clicking New from the Start screen, then Change… in the Starting Template section. Choose BROWSE… and find Multi-Resource Allocation View.tat in your list of templates; it comes pre-loaded with each installation of OnePager.

The template will do most of the heavy lifting for you; all you really need to do is to turn on “Text 30” as your row label so that the allocation percentages show up inside the swimlanes for each assigned resource. You can do this by going to Home > Project View Properties > Rows/Swimlanes and changing Label rows by:

Allocation PVP

If you want, you can also set up conditional formatting rules to color-code your tasks as red or green, based on whether they are over-allocated or under-allocated.

Let us know if you have other creative ideas on how to make use of this information, now that we’ve shown you how to free it from the confines of the “Resource Names” field.

This entry was posted in Microsoft Project Tips, Project Reporting by Safford. Bookmark the permalink.

About Safford

Safford is a versatile technology professional with a solid history of empowering emerging growth companies in a broad array of industries. His employment history includes energy industry consulting at Quorum Software, Senior Manager of Client Services and Technical Sales at telecom service aggregator GetConnected, and Vice President of Strategic Partner Management at electronic payment processor IP Commerce. Prior to his tenure as OnePager's COO, Safford was the company's Vice President of Marketing and Alliances. Safford holds a BA in Psychology and management from Rice University.

One thought on “The (Relatively) Painless Way to Show Resource Allocation in a Gantt Chart

  1. That’s amazingly useful! Many thanks for the pointer. This beats the VBA/Export to Excel/Conditional Formatting that I used to do.

Leave a Reply

Your email address will not be published. Required fields are marked *