Add Column Showing Work for a Resource Group

[Thomas]

I'd like to add a column to MS Project (2003) alongside the default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:

iif([Resource Group]="Group Name",[Work],0)

....but this does not seem to work the way I want.

Any ideas?

 

[JulieS]

Hello Thomas,

If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.

Try the following variation:

IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s

However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.

You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)

I hope this helps. Let us know how you get along.

Julie
Project MVP

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

 

[Thomas]

Hello Thomas,

If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.

Try the following variation:

IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s

However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.

You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)

I hope this helps.  Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/for the FAQs and additional
information about Microsoft Project

I'd like to add a column to MS Project (2003) alongside the  default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:

iif([Resource Group]="Group Name",[Work],0)

...but this does not seem to work the way I want.

Any ideas?- Hide quoted text -

- Show quoted text -

Julie,

Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.

It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...

Tom

 

[JulieS]

Hello Thomas,

If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.

Try the following variation:

IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s

However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.

You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/for the FAQs and additional
information about Microsoft Project

I'd like to add a column to MS Project (2003) alongside the default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:

iif([Resource Group]="Group Name",[Work],0)

...but this does not seem to work the way I want.

Any ideas?- Hide quoted text -

- Show quoted text -

Julie,

Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.

It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...

Tom

Hi Tom,

Did you try my suggestion for grouping? Using the Task Usage view and
applying the group definition I briefly described does show the list of
tasks and for each resource group the total work. Give that a try and
see if it gets you the presentation you were looking for.

Julie

 

[Thomas]

Hello Thomas,
If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.
Try the following variation:
IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s
However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.
You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)
I hope this helps.  Let us know how you get along.
Julie
Project MVP
Visithttp://project.mvps.org/forthe FAQs and additional
information about Microsoft Project
On 2/7/2010 5:42 PM, Thomas wrote:
I'd like to add a column to MS Project (2003) alongside the  default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:
iif([Resource Group]="Group Name",[Work],0)
...but this does not seem to work the way I want.
Any ideas?- Hide quoted text -
- Show quoted text -

Julie,

Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.

It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...

Tom

Hi Tom,

Did you try my suggestion for grouping?  Using the Task Usage view and
applying the group definition I briefly described does show the list of
tasks and for each resource group the total work.  Give that a try and
see if it gets you the presentation you were looking for.

Julie- Hide quoted text -

- Show quoted text -

Julie,

I understand that the Task Usage view will give me the detail I need,
but I do not want to present the project with each resource on a
separate row.

I also tried a VBA solution,

Public Sub GetCoreWork()
Dim Task As Task
Dim Resource As Resource
Dim CoreWork As Double

For Each Task In ThisProject.Tasks

If Task.OutlineChildren.Count = 0 Then

With Task

CoreWork = 0

For Each Resource In Task.Resources

If Resource.Group = "Core" Then
CoreWork = Resource.Work
End If

Next

Task.Number1 = CoreWork / 60

End If

End With

End If

Next

End Sub

But Resource.Work returns the total amount of work assigned to that
resource, not just for a specific task. Is there no way to do this in
VBA either? What this boils down to is that I need Work by Task and
Resource. I don't mind doing the VBA coding if it will work.

Tom

 

[Thomas]

On 2/8/2010 9:07 AM, Thomas wrote:

Hello Thomas,
If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.
Try the following variation:
IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s
However, the formula will show the total amount of work on the task,not
just the amount of work for the Resource Group referenced in the formula.
You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)
I hope this helps.  Let us know how you get along.
Julie
Project MVP
Visithttp://project.mvps.org/fortheFAQs and additional
information about Microsoft Project
On 2/7/2010 5:42 PM, Thomas wrote:
I'd like to add a column to MS Project (2003) alongside the  default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:
iif([Resource Group]="Group Name",[Work],0)
...but this does not seem to work the way I want.
Any ideas?- Hide quoted text -
- Show quoted text -
Julie,
Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.
It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...
Tom

Hi Tom,

Did you try my suggestion for grouping?  Using the Task Usage view and
applying the group definition I briefly described does show the list of
tasks and for each resource group the total work.  Give that a try and
see if it gets you the presentation you were looking for.

Julie- Hide quoted text -

- Show quoted text -

Julie,

I understand that the Task Usage view will give me the detail I need,
but I do not want to present the project with each resource on a
separate row.

I also tried a VBA solution,

Public Sub GetCoreWork()
    Dim Task As Task
    Dim Resource As Resource
    Dim CoreWork As Double

    For Each Task In ThisProject.Tasks

        If Task.OutlineChildren.Count = 0 Then

            With Task

                CoreWork = 0

                For Each Resource In Task.Resources

                        If Resource.Group = "Core" Then
                            CoreWork = Resource.Work
                        End If

                    Next

                    Task.Number1 = CoreWork / 60

                End If

            End With

        End If

    Next

End Sub

But Resource.Work returns the total amount of work assigned to that
resource, not just for a specific task. Is there no way to do this in
VBA either? What this boils down to is that I need Work by Task and
Resource. I don't mind doing the VBA coding if it will work.

Tom- Hide quoted text -

- Show quoted text -

Never mind. I got this working! The Assignments collection of the Task
object gave me what I needed, i.e.

Public Sub GetCoreWork()
Dim Task As Task
Dim Resource As Resource
Dim CoreWork As Double
Dim Assignment As Assignment

For Each Task In ThisProject.Tasks

If Task.OutlineChildren.Count = 0 Then

With Task

CoreWork = 0

For Each Resource In Task.Resources

For Each Assignment In Task.Assignments

If
ThisProject.Resources(Assignment.ResourceName).Group = "Core" Then
CoreWork = Assignment.Work
End If

Next

Task.Number1 = CoreWork / 60

Next

End With

End If

Next

End Sub

 

[JulieS]

Hello Thomas,

If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.

Try the following variation:

IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s

However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.

You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/forthe FAQs and additional
information about Microsoft Project

On 2/7/2010 5:42 PM, Thomas wrote:

I'd like to add a column to MS Project (2003) alongside the default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:

iif([Resource Group]="Group Name",[Work],0)

...but this does not seem to work the way I want.

Any ideas?- Hide quoted text -

- Show quoted text -

Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.

It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...

Tom

Hi Tom,

Did you try my suggestion for grouping? Using the Task Usage view and
applying the group definition I briefly described does show the list of
tasks and for each resource group the total work. Give that a try and
see if it gets you the presentation you were looking for.

Julie- Hide quoted text -

- Show quoted text -

Julie,

I understand that the Task Usage view will give me the detail I need,
but I do not want to present the project with each resource on a
separate row.

I also tried a VBA solution,

Public Sub GetCoreWork()
Dim Task As Task
Dim Resource As Resource
Dim CoreWork As Double

For Each Task In ThisProject.Tasks

If Task.OutlineChildren.Count = 0 Then

With Task

CoreWork = 0

For Each Resource In Task.Resources

If Resource.Group = "Core" Then
CoreWork = Resource.Work
End If

Next

Task.Number1 = CoreWork / 60

End If

End With

End If

Next

End Sub

But Resource.Work returns the total amount of work assigned to that
resource, not just for a specific task. Is there no way to do this in
VBA either? What this boils down to is that I need Work by Task and
Resource. I don't mind doing the VBA coding if it will work.

Tom

Hi Tom,

I created the following group definition in the Task Usage view:

Group by:
Field Name:Outline Number
Field Type: Task
Order: Ascending

Then by:
Field Name: ID
Field Type: Task
Order: Ascending

Then By:
Field Name: Name
Field Type: Task
Order: Ascending

Then By:
Field Name: Resource Group
Field Type: Assignment
Order: Ascending

Then I show only outline level 4. All resource Names (level 5) collapse
up.


What I see is:
Outline Level (Summary) Task Name
Task ID
Task Name
Resource Group

With the Work field showing I see work at all grouping levels including
the total work for resource group per task. Is this not what you need?

Please try stepping through setting up the group and then hiding outline
levels above 4. Does that get you what you need?

Julie

 

[JulieS]

On 2/8/2010 9:07 AM, Thomas wrote:

Hello Thomas,

If you have resources assigned from more than one resource group, the
formula you note will not work correctly as the Resource Group field
will not equal the text, it will contain the text.

Try the following variation:

IIf(Instr([Resource Group],"Your Group Name Here")>0,[Work]/60,0)s

However, the formula will show the total amount of work on the task, not
just the amount of work for the Resource Group referenced in the formula.

You may get closer by applying a group in the Task Usage view grouping
first by ID, then by Name, and then by Resource Group (assignment field)

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/fortheFAQs and additional
information about Microsoft Project

On 2/7/2010 5:42 PM, Thomas wrote:

I'd like to add a column to MS Project (2003) alongside the default
"Work" column that shows the total work for resources in a particular
resource group. I tried added a custom column with the following
formula:

iif([Resource Group]="Group Name",[Work],0)

...but this does not seem to work the way I want.

Any ideas?- Hide quoted text -

- Show quoted text -

Thank you for your reply but as you noted this formula gives the total
hours for the task which I already have in the default Work column.

It looks like I will have to export this data to Access or Excel in
order to present this the way I want. Too bad...

Hi Tom,

Did you try my suggestion for grouping? Using the Task Usage view and
applying the group definition I briefly described does show the list of
tasks and for each resource group the total work. Give that a try and
see if it gets you the presentation you were looking for.

Julie- Hide quoted text -

- Show quoted text -

Julie,

I understand that the Task Usage view will give me the detail I need,
but I do not want to present the project with each resource on a
separate row.

I also tried a VBA solution,

Public Sub GetCoreWork()
Dim Task As Task
Dim Resource As Resource
Dim CoreWork As Double

For Each Task In ThisProject.Tasks

If Task.OutlineChildren.Count = 0 Then

With Task

CoreWork = 0

For Each Resource In Task.Resources

If Resource.Group = "Core" Then
CoreWork = Resource.Work
End If

Next

Task.Number1 = CoreWork / 60

End If

End With

End If

Next

End Sub

But Resource.Work returns the total amount of work assigned to that
resource, not just for a specific task. Is there no way to do this in
VBA either? What this boils down to is that I need Work by Task and
Resource. I don't mind doing the VBA coding if it will work.

Tom- Hide quoted text -

- Show quoted text -

Never mind. I got this working! The Assignments collection of the Task
object gave me what I needed, i.e.

Public Sub GetCoreWork()
Dim Task As Task
Dim Resource As Resource
Dim CoreWork As Double
Dim Assignment As Assignment

For Each Task In ThisProject.Tasks

If Task.OutlineChildren.Count = 0 Then

With Task

CoreWork = 0

For Each Resource In Task.Resources

For Each Assignment In Task.Assignments

If
ThisProject.Resources(Assignment.ResourceName).Group = "Core" Then
CoreWork = Assignment.Work
End If

Next

Task.Number1 = CoreWork / 60

Next

End With

End If

Next

End Sub

Great Tom. I'm glad you were able to find a solution that showed what
you needed.

Julie