Export Resource Usage Graph

Question

4/21/14

Hello,

I would like to be able to calculate 'peak' for any resource or resource group for any period of time. Is there a way to do that? It would be very similar to the Resource Graph. Management would like me to build stoplight indicators for resources at 1 month, 3 months, and 6 months from NOW.

Any ideas? Clearly MS Project has the information. The question is how to get at it.

Thanks - Roger

Answer 1

The Peak Units field will only show 500% on the left side of the Resource Usage view.  If you add it through "Details Styles" to the right side it will show exactly the values you note.

You can export time-phased data to Excel using VBA or use a Visual Report built in to Project. 

The Visual Report "Resource Work Availability" shows the fields Work Availability, Work, and Remaining Availability.  If you run the report, turn on Resources and Weekly calendar in the Pivot table field list you can begin to show Monthly data.

Take a look at that Visual Report and see if that gets you close.

Answer 2

Hi, folks,

Thank you for your responses, but I don’t think I have explained what I am trying to do very well.

Maybe an example will help:

Suppose I have a resource, R1. Let’s say the following is true for the project while looking at the Resource Usage tool:

                R1           January:               Work is 160 hours, over-allocation is 0 hours

February:            Work is 960 hours, over-allocation is 800 hours

March:                  Work is 640 hours, over-allocation is 480 hours

April:                     Work is 460 hours, over-allocation is 480 hours

May:                      Work is 800 hours, over-allocation is 640 hours

June:                     Work is 800 hours, over-allocation is 640 hours

The Resource Graph with timescale in months:

                R1           Peak Units in January is                 0%

                                Peak Units in February is              500%

                                Peak Units in March is                    300%

                                Peak Units in April is                       300%

                                Peak Units in May is                        400%

                                Peak Units in June is                       400%

If I add the Peak field column to the Resource Usage tool, it would use the highest current Peak and say 500%.

What I need to do is add a number column along with an algorithm to produce a red, yellow, green stoplight for each resource for a particular time period and run a test.

For instance:

                If Peak Units (February) <= 150%, set stoplight to green

Else

    If Peak Units (February) > 350%, set stoplight to red

Else

    Set stoplight to yellow.

I know MS Project stores the time-phased data for Peak Units. I need to know how I can either access the data from within MS Project, so I can use the data in an algorithm (ideal). Or how to download the time-phased data to Excel to do the manipulations there (workable).  

Failing either of those options, how do I get access to the table schema, identify where and how the data is stored, and then invest in using another tool to provide information I need.

The Resource Usage report has the data, but that is only printable.

I am looking for help on how to do what I need to do within the system, with the information stored within and not have to do it manually?

Thoughts?

Roger

Answer 3

The Peak units field is calculated per time unit automatically.  If you add it to the timescaled portion (right side of the Resource Usage view) and zoom out to show months it will show you peak per month.

Answer 4

"Either insert the Peak column in teh table or right click the Usage data, select Detail styles and add the Peak units field."

Hi, Rod,

Thanks for the reply.

My issue is how to be able to address the peak units for a particular time period and/or be able to evaluate peak units for May, July, and October with an algorithm

Another option would be to be able to export that data or the corresponding visual report to Excel. Again, my issue is being able to address the peak units for a particular time period (months).

Regards - Roger

Answer 5

Either insert the Peak column in teh table or right click the Usage data, select Detail styles and add the Peak units field.