Resource Rate used for a specific Assignment (Project 2003)

[bstobart]

I'm trying to lookup the rate used for a specific assignment. Generally, we
have used the Standard rate (A), but we occasionally use the B rate.

Given a specific assignment, say oAssignment, which links resource oResource
to task oTask, how can I reference the corresponding resource's applicable
rate, or alternatively, determine the cost rate table used, and then look up
the rate there? I'm not currently using effective dated rates.

 

[Stephen Sanderlin]

The Assignment should have a CostRateTable property, which you can use
to look up the rate in the Resource's PayRates collection.

So, given a task called TestTask, which has a resource, TestRes,
assigned to it that has a rate of $100/hr in Cost Table C, the
following code should work:

Option Explicit

Sub Test()
Dim oTask As Task
Dim oAssignment As Assignment
Dim oResource As Resource
Dim oRate As String

Set oTask = ActiveProject.Tasks("TestTask")
Set oAssignment = oTask.Assignments(1)
Set oResource = ActiveProject.Resources(oAssignment.ResourceName)

oRate = oResource.CostRateTables((oAssignment.CostRateTable +
1)).PayRates(1).StandardRate

Call MsgBox(oRate, vbOKOnly, "Rate")
End Sub



I had to add 1 to the value from Assignment.CostRateTable because that
property is 0-based, while the CostRateTables collection is 1-based.

Please let me know if this works for you.

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.

 

[John]

I'm trying to lookup the rate used for a specific assignment. Generally, we
have used the Standard rate (A), but we occasionally use the B rate.

Given a specific assignment, say oAssignment, which links resource oResource
to task oTask, how can I reference the corresponding resource's applicable
rate, or alternatively, determine the cost rate table used, and then look up
the rate there? I'm not currently using effective dated rates.

bstobart,
For VBA the following syntax will give you the cost rate table for a
given resource's assignment. Be advised that the resource and assignment
must be valid or a runtime failure will occur.
Tbl = ActiveProject.Resource(resource ID).Assignments(desired
assignment).CostRateTable

The return value will be an integer with "0" being rate table "A", "1"
being rate table "B", etc.

To get the rate for that resource, use the following.
Rat = ActiveProject.Resource(resource
ID).CostRateTables(Tbl).PayRates(1).StandardRate

This assumes there is only one pay rate for that resource in the desired
cost rate table (you did mention you are NOT using date effective rates).

Hope this helps.
John
Project MVP

 

[bstobart]

Stephen,

Thanks for your response. I was able to use it directly. I appreciate your
using my variable names, as you really did my work for me.

I was quite confused by the rate tables object model. Now it makes sense.
Given the following statement (from you):

oRate = oResource.CostRateTables((oAssignment.CostRateTable
+1)).PayRates(1).StandardRate

The oAssignment.CostRateTable holds the A,B,C,D,E value (albeit encoded as
0,1,2,3,4) based on which rate table the user specified in the "assignment
information" dialog. [As you noted there's an offset between these two sets,
hence the "+1" in the formula above.]

The i of PayRates(i) apparently determines which of the effective dated
rates is used. (The first one works for me since I'm not using effective
dated rates, so I haven't checked this.)

StandardRate is the rate used. The alternative is Overtimerate which we are
not using.

Thanks again. And thanks to John who also provided the same information in
a second independent response.

 

[Stephen Sanderlin]

No problem. I'm glad that it worked for you.

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.

Stephen,

Thanks for your response. I was able to use it directly. I
appreciate your using my variable names, as you really did my work
for me.

I was quite confused by the rate tables object model. Now it makes
sense. Given the following statement (from you):

oRate = oResource.CostRateTables((oAssignment.CostRateTable
+1)).PayRates(1).StandardRate

The oAssignment.CostRateTable holds the A,B,C,D,E value (albeit
encoded as 0,1,2,3,4) based on which rate table the user specified in
the "assignment information" dialog. [As you noted there's an offset
between these two sets, hence the "+1" in the formula above.]

The i of PayRates(i) apparently determines which of the effective
dated rates is used. (The first one works for me since I'm not using
effective dated rates, so I haven't checked this.)

StandardRate is the rate used. The alternative is Overtimerate which
we are not using.

Thanks again. And thanks to John who also provided the same
information in a second independent response.

The Assignment should have a CostRateTable property, which you can
use to look up the rate in the Resource's PayRates collection.

So, given a task called TestTask, which has a resource, TestRes,
assigned to it that has a rate of $100/hr in Cost Table C, the
following code should work:

Option Explicit

Sub Test()
Dim oTask As Task
Dim oAssignment As Assignment
Dim oResource As Resource
Dim oRate As String

Set oTask = ActiveProject.Tasks("TestTask")
Set oAssignment = oTask.Assignments(1)
Set oResource =
ActiveProject.Resources(oAssignment.ResourceName)
oRate = oResource.CostRateTables((oAssignment.CostRateTable +
1)).PayRates(1).StandardRate

Call MsgBox(oRate, vbOKOnly, "Rate")
End Sub



I had to add 1 to the value from Assignment.CostRateTable because
that property is 0-based, while the CostRateTables collection is
1-based.

Please let me know if this works for you.

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of
my employer. All information is provided in "GOOD FAITH" and on an
"AS IS" basis only. I provide no presentations or warranties,
express or implied, including implied warranties of fitness for a
particular purpose, merchantability, title, and noninfringement. I
strongly advise you to extensively test any changes, workarounds,
or techniques described herein on a development system prior to
implementation in a production environment, and you are hereby
notified that I bear no responsibility whatsoever for any loss,
harm, or otherwise negative outcomes resulting from your actions,
whether or not said actions were a result of this electronic
message, directly or indirectly.

 

[John]

Stephen,

Thanks for your response. I was able to use it directly. I appreciate your
using my variable names, as you really did my work for me.

I was quite confused by the rate tables object model. Now it makes sense.
Given the following statement (from you):

oRate = oResource.CostRateTables((oAssignment.CostRateTable
+1)).PayRates(1).StandardRate

The oAssignment.CostRateTable holds the A,B,C,D,E value (albeit encoded as
0,1,2,3,4) based on which rate table the user specified in the "assignment
information" dialog. [As you noted there's an offset between these two sets,
hence the "+1" in the formula above.]

The i of PayRates(i) apparently determines which of the effective dated
rates is used. (The first one works for me since I'm not using effective
dated rates, so I haven't checked this.)

StandardRate is the rate used. The alternative is Overtimerate which we are
not using.

Thanks again. And thanks to John who also provided the same information in
a second independent response.

bstobart,
You're welcome and thanks for the feedback.
John