Resource Hours Roll-up

[Joe Martocci]

I have a need to roll-up resource names and work hours throughout the
schedule to each summary level task. I don't think Project does this with
any of it's standard functions and was trying to figure a good way to do it
using vba. Any thoughts?

Thanks,

 

[John]

I have a need to roll-up resource names and work hours throughout the
schedule to each summary level task. I don't think Project does this with
any of it's standard functions and was trying to figure a good way to do it
using vba. Any thoughts?

Thanks,

Joe,
If I understand your requirement correctly, you should be able to get
what you need without using any VBA. Try the Resource Usage view. Then
Group first by Task Summary Name and second by Name. Be sure to check
the box to group assignments, not resources. Also set the Field type for
the Task Summary Name to be "Assignment" (leave the field type for Name
as "Resource")>

Hope this helps.
John
Project MVP

 

[Joe Martocci]

Joe,
If I understand your requirement correctly, you should be able to get
what you need without using any VBA. Try the Resource Usage view. Then
Group first by Task Summary Name and second by Name. Be sure to check
the box to group assignments, not resources. Also set the Field type for
the Task Summary Name to be "Assignment" (leave the field type for Name
as "Resource")>

Hope this helps.
John
Project MVP

John,
Thanks for the help. I'll see if it suffices. We've been asked to provide
our schedules to interact with SAP via a 3rd party tool called Impress. We
are provided with a template to input our tasks and it's the Gantt Chart view
with some custom fields added for processing. I'm not sure if the Grouping
will transfer into SAP at the appropriate levels or not. I was thinking I
could roll the resource names and hours into a text field on the summary
level and offer that up without applying any filters or groups.

 

[John]

John,
Thanks for the help. I'll see if it suffices. We've been asked to provide
our schedules to interact with SAP via a 3rd party tool called Impress. We
are provided with a template to input our tasks and it's the Gantt Chart view
with some custom fields added for processing. I'm not sure if the Grouping
will transfer into SAP at the appropriate levels or not. I was thinking I
could roll the resource names and hours into a text field on the summary
level and offer that up without applying any filters or groups.

Joe,
Now that you've explained your real goal, grouping probably won't get
you there. Although the grouping summary lines appear in the view, they
do not exist as rows that can be exported and they are not accessible
through VBA.

However, if you are already provided a Project template for the
necessary data fields to import into SAP, why do you need separately
rolled up and calculated resource data?

John
Project MVP

 

[Joe Martocci]

Joe,
Now that you've explained your real goal, grouping probably won't get
you there. Although the grouping summary lines appear in the view, they
do not exist as rows that can be exported and they are not accessible
through VBA.

However, if you are already provided a Project template for the
necessary data fields to import into SAP, why do you need separately
rolled up and calculated resource data?

John
Project MVP

John,
I'll try to explain the situation... The Template that we were given
contains Levels 1-5 of what areas "management" wants to track in terms of
types of systems we develop. Our schedules need to fit under one of these
areas starting at Level 6. They want our schedules electronically so they
can import them into our SAP system and minimize human error. They can
import the entire schedule I create into the appropriate area of this
template and will assign a charge account to each task in the schedule where
costs appear. Theoretically this will create hundreds of charge accounts as
I apply resources at the lowest levels. In order to minimize the charge
accounts, the Project Managers have asked that I roll-up the resources and
hours to the summary level 3 in our detailed schedules and create a "new"
Master schedule weekly that will feed into the SAP overall Management
Template. This will reduce the charge accounts to something like 7 or 8
which is manageable. I didn't think this was going to be an issue at first
because project rolls up costs and work hours at each summary level.
However management wants to see WHO has how many HOURS for each of these
charge accounts to see if folks are being under utilized and are available to
work on other projects or are full time employed . Since these are R&D
projects, schedules are updated and can change frequently. Hopefully this
paints a clear picture.

 

[John]

John,
I'll try to explain the situation... The Template that we were given
contains Levels 1-5 of what areas "management" wants to track in terms of
types of systems we develop. Our schedules need to fit under one of these
areas starting at Level 6. They want our schedules electronically so they
can import them into our SAP system and minimize human error. They can
import the entire schedule I create into the appropriate area of this
template and will assign a charge account to each task in the schedule where
costs appear. Theoretically this will create hundreds of charge accounts as
I apply resources at the lowest levels. In order to minimize the charge
accounts, the Project Managers have asked that I roll-up the resources and
hours to the summary level 3 in our detailed schedules and create a "new"
Master schedule weekly that will feed into the SAP overall Management
Template. This will reduce the charge accounts to something like 7 or 8
which is manageable. I didn't think this was going to be an issue at first
because project rolls up costs and work hours at each summary level.
However management wants to see WHO has how many HOURS for each of these
charge accounts to see if folks are being under utilized and are available to
work on other projects or are full time employed . Since these are R&D
projects, schedules are updated and can change frequently. Hopefully this
paints a clear picture.

Joe,
I assume by "levels" you mean outline levels. And, if I understand your
template structure, it sounds like you want to have charge numbers track
only to the 3 level of the WBS and not to the actual performance tasks
at level 6 (or higher). Having only 7 or 8 charge numbers for the whole
project seems a little bit "coarse" when it comes to understanding what
various parts of the project actually cost.

At the company where I worked charge numbers did not track to individual
tasks in our project files. The project files tracked to WBS only.
Charge number were generated by our financial system apart from the
project files themselves. However, there were in most cases, several
charge numbers below each WBS. And for some of our projects, which were
also exclusively engineering development, we did have hundreds of charge
numbers. It gave us a pretty good picture of what things cost and was
very useful as historical data for bidding new, similar contracts.

Although I don't necessarily advise it, you could create a separate high
level version of the detail project to feed into SAP. There are various
ways to do that. One might be to run a macro on the "live" file each
week that puts the required template field values into spare fields at
summary level three and then creates a new file to that level. Without
further understanding of the detail of your template and live file, I
can't be more specific on the details of implementation. However, rather
than creating this artificial middle level plan, I suggest you have
dialogue with your management and the people working with SAP and try to
come to some middle ground so all parties get what they need to do their
job.

With regard to what your management wants to see for resource
assignments and hours, the grouping approach I described in my original
response should give that data.

Hope this helps.
John
Project MVP