Calculate Work by Resource and Task

[Chris Allen]

Hi everone, my compliments on a great forum.

We need to calculate labour work by task, in a project with both labour and
equipment resources as type 'Work'.

The resources have a custom enterprise resource outline code 'RBS'. I have
created a formula in a custom Resource 'enterprise number field' which
distinguishes labour from equipment and successfully gives labour hours not
machine hours.

However, it only does this in the Resource table view, so cannot break it
down at task AND resource level.

In Task views the custom field is not visible - because it's a resource
field. The Task Usage field shows Work by task value in italics, and I can't
access it with a formula; it only sees the Task value for Work.

In Resource Usage view, the total Work for resource is accessible to
calculate, but the task-level Work is italicised and not available to
calculate.

I think my problem is at a conceptual level; Work is something different
according to whether your view is a Task or Resource view. I believe the
brute force way to tackle it is to have a developer write VB with two nested
loops, on for Task and one for Resource. This is based on the answer to
question "Why can't I display the "Duration" field in "Resource Usage" view?"

Alternatives I have considered are:
1) Use resource type 'Material' for equipment; but we want to be able to
level the Equipment as a resource.
2) Use Resource Groups; but the resources have to be interchangeable whereas
we want to categorise non-interchangeable resources.
3) Paste the table into Excel and do stuff with it there, then past answers
back into custom fields.
4) Customise a copy of the Resource Usage view, and change the right-hand
pane (weekly) to show the custom field instead of Work. I can't see any way
of changing the value used in the graphic pane of the view.

 

[Rod Gill]

So what is it you actually want to achieve?

 

[Chris Allen]

Rod,
I want to operate with Work, at both Task and Resource level. THis is to
get the total Labour hrs for a task.

To do this I distinguish Labour form Plant resource assignments with the
Enterprise RBS (Resource Breakdown Structure) and write the result into
Enterprise Task Number Field 1.

VB is posted below - it works!

So what is it you actually want to achieve?

Sub TaskLab()
Dim Job As Task
Dim Myjob As Assignment
Dim myResource As Resource
Dim myResourceID As String

Dim Res_RBS As String
Dim MyNumber1 As Double


' For each task
For Each Job In ActiveProject.Tasks
' Initialise MyNumber1 as 0
MyNumber1 = 0
If (Not Job Is Nothing) And (Not Job.Summary) Then
For Each Myjob In Job.Assignments

' For each Assignment
'Get the ResourceID for Assignment

myResourceID = Myjob.ResourceID
Res_RBS = GetResourceRBSValue(myResourceID)

'Loop through Resource Table by ResourceID until match
'Get matching RBS
' Res_RBS = myResource.EnterpriseRBS
'End ResourceID Loop

'Test RBS for Equipment range
' If(([RBS]>="USA.P") And ([RBS]<="USA.P.ZZZ") then skip next line

If Not (Left(Res_RBS, 5) = "USA.P") Then
' Add Myjob.Work to MyNumber1
MyNumber1 = MyNumber1 + Myjob.Work / 60
End If
' End Assignment Loop
Next Myjob

MsgBox "Task [" & Job.Name & "] Labour Work Is" +
Format(CStr(MyNumber1), "0.00") & " RBS Value:" & Res_RBS

' Assign the calculated value MyNumber1 back to the field 'Task
Enterprise Number Field 8'
Job.EnterpriseNumber8 = MyNumber1

' End IF NOT Job is Nothing
End If




If (Job.Summary) Then
'MsgBox "Summary Task: " & Job.Name & " FOUND"
'MsgBox "Total: " & MyNumber1
End If

'End Task loop
Next Job

End Sub

Function GetResourceRBSValue(ResourceID As String) As String

Dim myResource As Resource

For Each myResource In ActiveProject.Resources
If myResource.ID = ResourceID Then
GetResourceRBSValue = myResource.EnterpriseRBS
Exit For
End If
Next


End Function

 

[Steve House]

Seems like it would be far simpler to use the Resource Group field in the
vanilla resource sheet and designate labour and equipment by their
respective names. Then in the Task Usage view existing "Resource Group"
filter to display only resources with a Resource Group="Labour" Doesn't
that do what you posed you needed, a breakdown of labour hours only by task?
Your VB may work but it sure seems to me to be hunting flies with an
elephant gun when the direct filter approach is already builtin.

What do you mean by this and why does it preclude using resource groups to
categorize labour and equipment and a custom text or boolean field to handle
the interchangeable versus non-interchangeable attribute?

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Rod,
I want to operate with Work, at both Task and Resource level. THis is to
get the total Labour hrs for a task.

To do this I distinguish Labour form Plant resource assignments with the
Enterprise RBS (Resource Breakdown Structure) and write the result into
Enterprise Task Number Field 1.

VB is posted below - it works!

So what is it you actually want to achieve?

Sub TaskLab()
Dim Job As Task
Dim Myjob As Assignment
Dim myResource As Resource
Dim myResourceID As String

Dim Res_RBS As String
Dim MyNumber1 As Double


' For each task
For Each Job In ActiveProject.Tasks
' Initialise MyNumber1 as 0
MyNumber1 = 0
If (Not Job Is Nothing) And (Not Job.Summary) Then
For Each Myjob In Job.Assignments

' For each Assignment
'Get the ResourceID for Assignment

myResourceID = Myjob.ResourceID
Res_RBS = GetResourceRBSValue(myResourceID)

'Loop through Resource Table by ResourceID until match
'Get matching RBS
' Res_RBS = myResource.EnterpriseRBS
'End ResourceID Loop

'Test RBS for Equipment range
' If(([RBS]>="USA.P") And ([RBS]<="USA.P.ZZZ") then skip next
line

If Not (Left(Res_RBS, 5) = "USA.P") Then
' Add Myjob.Work to MyNumber1
MyNumber1 = MyNumber1 + Myjob.Work / 60
End If
' End Assignment Loop
Next Myjob

MsgBox "Task [" & Job.Name & "] Labour Work Is" +
Format(CStr(MyNumber1), "0.00") & " RBS Value:" & Res_RBS

' Assign the calculated value MyNumber1 back to the field 'Task
Enterprise Number Field 8'
Job.EnterpriseNumber8 = MyNumber1

' End IF NOT Job is Nothing
End If




If (Job.Summary) Then
'MsgBox "Summary Task: " & Job.Name & " FOUND"
'MsgBox "Total: " & MyNumber1
End If

'End Task loop
Next Job

End Sub

Function GetResourceRBSValue(ResourceID As String) As String

Dim myResource As Resource

For Each myResource In ActiveProject.Resources
If myResource.ID = ResourceID Then
GetResourceRBSValue = myResource.EnterpriseRBS
Exit For
End If
Next


End Function

 

[Chris Allen]

Steve I agree that groups would be simpler, but the doco for Resource groups
says they all have to cost the same rate, and have the same descriptive codes
which would preclude USING the RBS (resource breakdown structure). I will
test it though and see if it works.
Cheers
Chris

Seems like it would be far simpler to use the Resource Group field in the
vanilla resource sheet and designate labour and equipment by their
respective names. Then in the Task Usage view existing "Resource Group"
filter to display only resources with a Resource Group="Labour" Doesn't
that do what you posed you needed, a breakdown of labour hours only by task?
Your VB may work but it sure seems to me to be hunting flies with an
elephant gun when the direct filter approach is already builtin.

What do you mean by this and why does it preclude using resource groups to
categorize labour and equipment and a custom text or boolean field to handle
the interchangeable versus non-interchangeable attribute?

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Rod,
I want to operate with Work, at both Task and Resource level. THis is to
get the total Labour hrs for a task.

To do this I distinguish Labour form Plant resource assignments with the
Enterprise RBS (Resource Breakdown Structure) and write the result into
Enterprise Task Number Field 1.

VB is posted below - it works!

So what is it you actually want to achieve?

Sub TaskLab()
Dim Job As Task
Dim Myjob As Assignment
Dim myResource As Resource
Dim myResourceID As String

Dim Res_RBS As String
Dim MyNumber1 As Double


' For each task
For Each Job In ActiveProject.Tasks
' Initialise MyNumber1 as 0
MyNumber1 = 0
If (Not Job Is Nothing) And (Not Job.Summary) Then
For Each Myjob In Job.Assignments

' For each Assignment
'Get the ResourceID for Assignment

myResourceID = Myjob.ResourceID
Res_RBS = GetResourceRBSValue(myResourceID)

'Loop through Resource Table by ResourceID until match
'Get matching RBS
' Res_RBS = myResource.EnterpriseRBS
'End ResourceID Loop

'Test RBS for Equipment range
' If(([RBS]>="USA.P") And ([RBS]<="USA.P.ZZZ") then skip next
line

If Not (Left(Res_RBS, 5) = "USA.P") Then
' Add Myjob.Work to MyNumber1
MyNumber1 = MyNumber1 + Myjob.Work / 60
End If
' End Assignment Loop
Next Myjob

MsgBox "Task [" & Job.Name & "] Labour Work Is" +
Format(CStr(MyNumber1), "0.00") & " RBS Value:" & Res_RBS

' Assign the calculated value MyNumber1 back to the field 'Task
Enterprise Number Field 8'
Job.EnterpriseNumber8 = MyNumber1

' End IF NOT Job is Nothing
End If




If (Job.Summary) Then
'MsgBox "Summary Task: " & Job.Name & " FOUND"
'MsgBox "Total: " & MyNumber1
End If

'End Task loop
Next Job

End Sub

Function GetResourceRBSValue(ResourceID As String) As String

Dim myResource As Resource

For Each myResource In ActiveProject.Resources
If myResource.ID = ResourceID Then
GetResourceRBSValue = myResource.EnterpriseRBS
Exit For
End If
Next


End Function

 

[Steve House]

No, you've misread the docs. - There are Resource Groups and then there are
Grouped Resources, what I like to call aggregate resources. Here's what I
mean.

When you enter the resources you might distinguish individuals who have
unique skill sets and costs - Joe Engineer, Betty Manager, Fred Technician,
etc. Each of these would be listed in the resource sheet as an individual
entry and each has their own set of cost reates, calendar, etc. Their max
availability should be no greater than 100% each since the most work a
single individual can produce in 1 hour of working time is 1 man-hour of
output. But they all could be members of the Research department and to
distinguish them from people working in Admin, Maintainance, Sales, or what
have you, you would enter "Research" in the Resource Group field in the
basic resource sheet view. You can then apply grouping and filters to
various views and reports to answer questions such as "What are the tasks
I've assigned to people in the Research department?" etc. Resource Group is
an arbitrary field and you can enter anything you like into it as long as
you're consistent.

A Grouped (or aggregate) Resource is a collection of individuals who are
essentially interchangeable and whom I really don't need to distinguish as
individuals for assignment purposes. If we have 5 custodians I might be
able to use anywhere from one guy for 15 minutes up to all 5 for a full
shift. But I really don't need to plan in such detail that I know just
which person the lead custodian assigns to mop the South Hallway - as a PM
all I care about is he assigns 1 custodian to mopping tonight and I don't
much care who that is. So I'd list them in the resource sheet as "Custodial
Staff" with a max availability of 500% indicating that there are 5
individuals making up that group (and we can use anything up to a maximum of
5 at once), but I won't list them individually by name. They'll all have
the same cost basis and working time calendar.

What I'm suggesting you use is the former, not the latter.

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Steve I agree that groups would be simpler, but the doco for Resource
groups
says they all have to cost the same rate, and have the same descriptive
codes
which would preclude USING the RBS (resource breakdown structure). I will
test it though and see if it works.
Cheers
Chris

Seems like it would be far simpler to use the Resource Group field in the
vanilla resource sheet and designate labour and equipment by their
respective names. Then in the Task Usage view existing "Resource Group"
filter to display only resources with a Resource Group="Labour" Doesn't
that do what you posed you needed, a breakdown of labour hours only by
task?
Your VB may work but it sure seems to me to be hunting flies with an
elephant gun when the direct filter approach is already builtin.

2) Use Resource Groups; but the resources have to be interchangeable
whereas we want to categorise non-interchangeable resources.

What do you mean by this and why does it preclude using resource groups
to
categorize labour and equipment and a custom text or boolean field to
handle
the interchangeable versus non-interchangeable attribute?

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Rod,
I want to operate with Work, at both Task and Resource level. THis is
to
get the total Labour hrs for a task.

To do this I distinguish Labour form Plant resource assignments with
the
Enterprise RBS (Resource Breakdown Structure) and write the result into
Enterprise Task Number Field 1.

VB is posted below - it works!

:

So what is it you actually want to achieve?


Sub TaskLab()
Dim Job As Task
Dim Myjob As Assignment
Dim myResource As Resource
Dim myResourceID As String

Dim Res_RBS As String
Dim MyNumber1 As Double


' For each task
For Each Job In ActiveProject.Tasks
' Initialise MyNumber1 as 0
MyNumber1 = 0
If (Not Job Is Nothing) And (Not Job.Summary) Then
For Each Myjob In Job.Assignments

' For each Assignment
'Get the ResourceID for Assignment

myResourceID = Myjob.ResourceID
Res_RBS = GetResourceRBSValue(myResourceID)

'Loop through Resource Table by ResourceID until match
'Get matching RBS
' Res_RBS = myResource.EnterpriseRBS
'End ResourceID Loop

'Test RBS for Equipment range
' If(([RBS]>="USA.P") And ([RBS]<="USA.P.ZZZ") then skip
next
line

If Not (Left(Res_RBS, 5) = "USA.P") Then
' Add Myjob.Work to MyNumber1
MyNumber1 = MyNumber1 + Myjob.Work / 60
End If
' End Assignment Loop
Next Myjob

MsgBox "Task [" & Job.Name & "] Labour Work Is" +
Format(CStr(MyNumber1), "0.00") & " RBS Value:" & Res_RBS

' Assign the calculated value MyNumber1 back to the field 'Task
Enterprise Number Field 8'
Job.EnterpriseNumber8 = MyNumber1

' End IF NOT Job is Nothing
End If




If (Job.Summary) Then
'MsgBox "Summary Task: " & Job.Name & " FOUND"
'MsgBox "Total: " & MyNumber1
End If

'End Task loop
Next Job

End Sub

Function GetResourceRBSValue(ResourceID As String) As String

Dim myResource As Resource

For Each myResource In ActiveProject.Resources
If myResource.ID = ResourceID Then
GetResourceRBSValue = myResource.EnterpriseRBS
Exit For
End If
Next


End Function