Custom field to sum work of resources from a specific group

[jomo]

Hi,

I am trying to write a formula that will test each task for resources
belonging to a specific group and then sum/display the total work content
(for that task) for the resources from that group...

eg. A task with duration of 8hrs uses 3 resources of which 2 are in group A
and 1 is in group B. I want to identify/display the total work for Group A.
This would be 16hrs in this example

I can use the formula.. InStr([Resource Group],"GROUPA" to identify if any
resources from Group A are present. However, I don't know how extend the
formula to sum the total work for all Group A resources on that task.

Any help appreciated

 

[Jim Aksel]

I haven't checked this (I am really busy), here is how I would approach it.
Consider a customized task grouping or custom view, the summation will
happen automatically for you.

Identify a colum, such as Resource Group, or Tex1 that discloses which group
the resources belong to.

Project/Group By.../Customize Group By.
Your grouping order would be by Resource Group or Text1. You need to find a
way to preserve the outline structure.

You may need to use a Custom View rather than a Customized Group By. You
can also investigate the Task Usage View and try customizing on that in the
same manner.

I believe what you want to see if the Task Usage View with an additional
layer-- "Level2" that identifies "Resource Group" and then Level3 identifies
the individual workers and their efforts. In this scenario, you will have
the summary you need. I know this is possible, just haven't played with it.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[John]

Hi,

I am trying to write a formula that will test each task for resources
belonging to a specific group and then sum/display the total work content
(for that task) for the resources from that group...

eg. A task with duration of 8hrs uses 3 resources of which 2 are in group A
and 1 is in group B. I want to identify/display the total work for Group A.
This would be 16hrs in this example

I can use the formula.. InStr([Resource Group],"GROUPA" to identify if any
resources from Group A are present. However, I don't know how extend the
formula to sum the total work for all Group A resources on that task.

Any help appreciated

jomo,
I seriously doubt it can even be done with a formula since the formula
would have to not only isolate resource groups but also isolate each
unit assignment. Very doable with VBA but not with formulas.

However, I have a solution that should get you what you want. It first
requires that you either manually copy the resource groups from the
Resource Sheet to the assignment rows in the Task Usage view, or use a
simple VBA macro to do that (see FAQ 37 - Custom fields in tables on our
MVP website at, http://www.mvps.org/project/faqs.htm). Once the group
data is present, you can use Project's Grouping function (Project/Group
by/customize group by...) to group the data in the Task Usage view.
First group on the task name. Then group on the assignment field that
you copied the resource group info into (e.g Text1). Check the box to
group by assignments, not tasks and then change the field type for the
Text1 field to be "assignment".

Hope this helps.
John
Project MVP

 

[JulieS]

Hi,

I am trying to write a formula that will test each task for resources
belonging to a specific group and then sum/display the total work
content
(for that task) for the resources from that group...

eg. A task with duration of 8hrs uses 3 resources of which 2 are in
group A
and 1 is in group B. I want to identify/display the total work for
Group A.
This would be 16hrs in this example

I can use the formula.. InStr([Resource Group],"GROUPA" to identify
if any
resources from Group A are present. However, I don't know how extend
the
formula to sum the total work for all Group A resources on that task.

Any help appreciated

jomo,
I seriously doubt it can even be done with a formula since the formula
would have to not only isolate resource groups but also isolate each
unit assignment. Very doable with VBA but not with formulas.

However, I have a solution that should get you what you want. It first
requires that you either manually copy the resource groups from the
Resource Sheet to the assignment rows in the Task Usage view, or use a
simple VBA macro to do that (see FAQ 37 - Custom fields in tables on
our
MVP website at, http://www.mvps.org/project/faqs.htm). Once the group
data is present, you can use Project's Grouping function
(Project/Group
by/customize group by...) to group the data in the Task Usage view.
First group on the task name. Then group on the assignment field that
you copied the resource group info into (e.g Text1). Check the box to
group by assignments, not tasks and then change the field type for the
Text1 field to be "assignment".

Hope this helps.
John
Project MVP

Hi John,

Just a quick comment-- I don't think there is a need to copy the
resource group information. It should show up automatically in the Task
Usage view as Resource Group is one of the few fields that actually
crosses the Task to Resource world.

The group you mentioned works grand without any customizing of the field
required, just use the Resource Group field as the assignment group.

Julie

 

[John]

Hi,

I am trying to write a formula that will test each task for resources
belonging to a specific group and then sum/display the total work
content
(for that task) for the resources from that group...

eg. A task with duration of 8hrs uses 3 resources of which 2 are in
group A
and 1 is in group B. I want to identify/display the total work for
Group A.
This would be 16hrs in this example

I can use the formula.. InStr([Resource Group],"GROUPA" to identify
if any
resources from Group A are present. However, I don't know how extend
the
formula to sum the total work for all Group A resources on that task.

Any help appreciated

jomo,
I seriously doubt it can even be done with a formula since the formula
would have to not only isolate resource groups but also isolate each
unit assignment. Very doable with VBA but not with formulas.

However, I have a solution that should get you what you want. It first
requires that you either manually copy the resource groups from the
Resource Sheet to the assignment rows in the Task Usage view, or use a
simple VBA macro to do that (see FAQ 37 - Custom fields in tables on
our
MVP website at, http://www.mvps.org/project/faqs.htm). Once the group
data is present, you can use Project's Grouping function
(Project/Group
by/customize group by...) to group the data in the Task Usage view.
First group on the task name. Then group on the assignment field that
you copied the resource group info into (e.g Text1). Check the box to
group by assignments, not tasks and then change the field type for the
Text1 field to be "assignment".

Hope this helps.
John
Project MVP

Hi John,

Just a quick comment-- I don't think there is a need to copy the
resource group information. It should show up automatically in the Task
Usage view as Resource Group is one of the few fields that actually
crosses the Task to Resource world.

The group you mentioned works grand without any customizing of the field
required, just use the Resource Group field as the assignment group.

Julie

Julie,
As usual you are exactly right. I had forgotten that for the task and
assignment objects, the Group field is called Resource Group.

Good catch.

John

 

[JulieS]

Julie,
As usual you are exactly right. I had forgotten that for the task and
assignment objects, the Group field is called Resource Group.

Good catch.

John

Hi John,
Thanks for the "as usual" comment -- you give me far too much credit!

Julie