I am producing a Task Usage view – with just ‘Percent Allocation’ field shown. (in Project Pro 2010)
Challenge: I would like to be able color code (GREEN) any resource that is not allocated at capacity (i.e., has some availability remaining). Is this possible in a Task Usage report?
Examples of desired results:
1) Bob has allocation on current project’s Task Usage view of 50%, but total allocation (on all projects) of 100%.
RESULT: show 50% in black, since he has no bandwidth left.
2) Sue has allocation on current project’s Task Usage view of 50%, but total allocation (on all projects) of 80%.
RESULT: show 50% in green, since she still has 20% bandwidth left.
Can anyone help me?
Many thanks! Tom M
Project 2010 does NOT have a feature to show Percent Allocation (in table and timephased data) below or above capacity in Task Usage view in different color. Though your requirement is on Task Usage, I would want to let you know in Resource Usage view percent allocation above capacity are displayed in Red color, which can be customized using Format tab> Text Styles > Overallocated Resources. Again, in Resource Usage there is no feature to display Percent Allocation (in table and timephased data) below capacity in different color.
In Task Usage, if you want to know the dates when the resources is assigned more or less than the capacity, you need to write a macro.
a) First, change your current view to Task Usage
b) Insert two columns on the table: Overallocated, Flag1, Notes. The Overallocated column will be true if the resource is overallocated in some day of that assignment. The Flag1 will be used by the below macro to indicate if the resource is assigned less than the capacity. The Notes field will be updated by the below macro with the dates when the resource is above and below capacity.
c) In View tab, Macros > Visual Basic. Copy the below code into the module group
Dim myProjectTask As Task
Dim myResourceAssignment As Assignment
For Each myProjectTask In ActiveProject.Tasks
For Each myResourceAssignment In myProjectTask.Assignments
Sub CalculateResourceCapacity(myResourceAssignment As Assignment)
Dim tsv, tsvs
Dim st, fn
st = myResourceAssignment.Start
fn = myResourceAssignment.Finish
myResourceAssignment.Notes = “”
myResourceAssignment.Flag1 = False
Set tsvs = myResourceAssignment.TimeScaleData(StartDate:=st, EndDate:=fn, _
For Each tsv In tsvs
If (Val(tsv) < 100) Then
myResourceAssignment.Flag1 = True
myResourceAssignment.AppendNotes “Below capacity on ” & tsv.StartDate
If (Val(tsv) > 100) Then
myResourceAssignment.AppendNotes “Above capacity on ” & tsv.StartDate
I know this is quite complex, but if your intent is to know the dates when the resource is working below and above capacity the above code should be good.
Hope this helps.
Also consider my suggestion: you can create a visual Resource Work Summary report and customize the pivot table generated to display Work / Remaining Availability rows for each resource in a specified period of time, such as weeks.
Sai: Thanks Sai. When our “Macro guy” has a little time on his hands, I’ll ask him to try your solution.
Ismet: Thanks for your suggestion. However, if I understand correctly, a Resource Work Summary report only deals with Resource Usage fields. What I’m looking for is to display in a Task Usage view. I know I am trying to combine Task Usage and Resource Usage data, which may be a stretch. Am I missing something?
Sai: We tried your macro. It does perform as you indicated. Unfortunately, our leadership team was hoping to be able to see in a Task Usage view, at a glance, what months a resource has bandwidth as they view across the table.
I’m surprised that whatever calculation and coding used to produce “red” when an overallocation exists, can’t also produce “green” when an underallocation exists. Oh well, maybe in a future version.
Project doesn’t have feature to display under allocations in different color. Also programatically the project object model doesn’t supports this feature in task usage view.