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:
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):
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:”
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:
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.