Cost rate table entries variable?

[Russ]

We use cost rate table A only. Each line-item entry in the cost rate table
represents the rate for a fiscal year for all our resources. Example: the
entry 1 represents cost rate for FY2005 and/or earlier; entry 2 represents
cost rate for FY06, and so on. Here’s the kicker; not all our resources have
the same dates as their fiscal year change over dates.

I would like to add custom columns to our gaunt view (and other views)
showing the costs broken out based on fiscal years. Is there a variable I
could use in a custom field which will show me cost broken out base on the
line-item entry for the resource’s cost rate table entries? Or, does anyone
have a better approach…

 

[John]

We use cost rate table A only. Each line-item entry in the cost rate table
represents the rate for a fiscal year for all our resources. Example: the
entry 1 represents cost rate for FY2005 and/or earlier; entry 2 represents
cost rate for FY06, and so on. Here’s the kicker; not all our resources have
the same dates as their fiscal year change over dates.

I would like to add custom columns to our gaunt view (and other views)
showing the costs broken out based on fiscal years. Is there a variable I
could use in a custom field which will show me cost broken out base on the
line-item entry for the resource’s cost rate table entries? Or, does anyone
have a better approach…

Russ,
First let me address your current use of the cost rate tables. If I
understand your post correctly it sounds like you are using a single
cost rate schedule for all resources but not all resource pay rates are
synced to the fiscal year. The solution for that is to use
individualized cost rate tables for each resource that is different from
the rest. For example, let's say resources 1 through 10 are all on the
company fiscal year cost rate schedule but resources 11 & 12 are on a
different cost rate schedule (e.g. calendar year). Use fiscal year based
cost rate table "A" for the first ten resources and then create a
calendar year based cost rate table for resources 11 & 12. Both can be
table "A" or any of the other four tables. Note that cost rate table "A"
is the default. If a different cost rate table is used, it must be
individually selected on an assignment row in a Usage view (Resource or
Task).

With regard to your custom column idea. Just for reference, it's "Gantt"
not "gaunt". To see costs by fiscal year your best bet is to use one of
the Usage views set up with a fiscal year based timescale (i.e. select
the "use fiscal year" option in the Format/Timescale window - note, you
must also set Tools/Options/Calendar tab for the start month of the
fiscal year). The fiscal year data could be shown in a task view (e.g.
Gantt Chart view) in spare fields but it would require VBA to sum up the
timescale data and dump it into each spare field designated for each
year. Keep in mind though, if you set up the timescale display to show
costs by fiscal year, it will do so for all resource assignments, not
just those on a fiscal year schedule. If VBA is used, individualized
costs by time period (fiscal or calendar) could be shown in spare fields.

Hope this helps.
John
Project MVP

 

[Russ]

Hi John,

Thanks for your response. It looks like I’m going to have to go with the
VBA option. Since my resources don’t all use the same date ranges for their
fiscal years, rate table line items are need to calculate each task’s total
Cost. But, I now also want to see costs broken out and based on work
performed within the dates for each line entry of the rate table.

All my resources have three entries in their cost rate table. The first
line in each resource cost rate table represents FY06 (note: these date
ranges for FY06 differ slightly from resource to resource). Second line
represents FY07. And so on. I’m thinking I should be able to write a VBA
that can get Cost6 to be the cost of a task for work done within the date
ranges identified in the first line of the assigned resource cost rate table
(ie FY06). Then get Cost7 to be the cost of a task for work done within the
date ranges identified in the second line of resource cost rate table (ie
FY07). And so on. Is this something along the line you were thinking for a
VBA? If so, do you know the field names for the individual line items with
the cost rate table?

p.s. Hope my Word’s auto spell checker has embarrassed me again. ïŠ

--
Thanks,
Russ

Russ,
First let me address your current use of the cost rate tables. If I
understand your post correctly it sounds like you are using a single
cost rate schedule for all resources but not all resource pay rates are
synced to the fiscal year. The solution for that is to use
individualized cost rate tables for each resource that is different from
the rest. For example, let's say resources 1 through 10 are all on the
company fiscal year cost rate schedule but resources 11 & 12 are on a
different cost rate schedule (e.g. calendar year). Use fiscal year based
cost rate table "A" for the first ten resources and then create a
calendar year based cost rate table for resources 11 & 12. Both can be
table "A" or any of the other four tables. Note that cost rate table "A"
is the default. If a different cost rate table is used, it must be
individually selected on an assignment row in a Usage view (Resource or
Task).

With regard to your custom column idea. Just for reference, it's "Gantt"
not "gaunt". To see costs by fiscal year your best bet is to use one of
the Usage views set up with a fiscal year based timescale (i.e. select
the "use fiscal year" option in the Format/Timescale window - note, you
must also set Tools/Options/Calendar tab for the start month of the
fiscal year). The fiscal year data could be shown in a task view (e.g.
Gantt Chart view) in spare fields but it would require VBA to sum up the
timescale data and dump it into each spare field designated for each
year. Keep in mind though, if you set up the timescale display to show
costs by fiscal year, it will do so for all resource assignments, not
just those on a fiscal year schedule. If VBA is used, individualized
costs by time period (fiscal or calendar) could be shown in spare fields.

Hope this helps.
John
Project MVP

Hi John,

Thanks for your response. Since my resources don’t all use the same date
ranges for their fiscal years, it looks like I’m going to have to go with the
VBA option.

All my resources have three entries in their cost rate table. The first
line in each resource cost rate table represents FY06 . Second line
represents FY07. And so on(note: these date ranges for FYs differ slightly
from resource to resource). I’m thinking I should be able to write a VBA
that can get Cost6 to be the cost of a task for work done within the date
ranges identified in the first line of the assigned resource cost rate table
(ie FY06). Then get Cost7 to be the cost of a task for work done within the
date ranges identified in the second line of resource cost rate table (ie
FY07). And so on. Is this something along the lines you were thinking for a
VBA? If so, do you know the field names for the individual line items within
the cost rate table?

p.s. Hope my Word’s auto spell checker hasn't embarrassed me again.

 

[John]

Hi John,

Thanks for your response. It looks like I¹m going to have to go with the
VBA option. Since my resources don¹t all use the same date ranges for their
fiscal years, rate table line items are need to calculate each task¹s total
Cost. But, I now also want to see costs broken out and based on work
performed within the dates for each line entry of the rate table.

All my resources have three entries in their cost rate table. The first
line in each resource cost rate table represents FY06 (note: these date
ranges for FY06 differ slightly from resource to resource). Second line
represents FY07. And so on. I¹m thinking I should be able to write a VBA
that can get Cost6 to be the cost of a task for work done within the date
ranges identified in the first line of the assigned resource cost rate table
(ie FY06). Then get Cost7 to be the cost of a task for work done within the
date ranges identified in the second line of resource cost rate table (ie
FY07). And so on. Is this something along the line you were thinking for a
VBA? If so, do you know the field names for the individual line items with
the cost rate table?

p.s. Hope my Word¹s auto spell checker has embarrassed me again. ?

Russ,
Has embarrassed or hasn't embarrassed? I find an ounce of proofreading
is worth a ton of auto spell checking.

It sounds a little unusual to have people working on the same project
yet have different fiscal years. But whatever.

With regard to your proposed VBA approach, yes, I was thinking along the
lines of a separate spare field for each fiscal year's data, just like
you are proposing. Keep in mind it could get quite complex and somewhat
messy if more then one resource works on any given task. Then there is
also the issue of how many fields will be necessary for each fiscal
year. For example, let's say there are 3 different resource fiscal
years. Resource A's fiscal year starts on Jan 1, resource B's fiscal
year starts July 1, and resource C's fiscal year starts Oct 1. Would you
then have 3 spare fields for FY 06 (i.e. one for each specific FY
breakdown), another 3 for FY07 and so forth?

Cost rate table data is accessed through the CostRateTables and/or the
PayRates collection objects. However, if you already know the dates for
each fiscal year, why not just use the TimeScaleData Method to extract
the fiscal year cost. Hard code the fiscal start and end dates and get
the data directly. Although the timescale data can be extracted by
years, I'm not sure how it reacts when fiscal years are involved. I
would just extract the monthly data and then add it up before dumping it
into the designated spare fields. If you don't necessarily know the
fiscal year dates, you can certainly use the EffectiveDate Property of
the PayRate object to get the dates and then use the TimeScaleData
Method. For more information on the syntax, Methods and Properties for
any of the above, look at the object browser from the VB editor.

John
Project MVP

 

[Steve House]

It is the dates the work is performaed on individual tasks that determine
the line in the cost rate table to be used. Lets say you have a rate of $10
per hour up to 31 Dec 06 and then the rate goes to $15 an hour. A task that
runs for 40 hours 11-15 Dec would cost $400 while a task scheduled 40 hours
for 08-12 Jan would cost $600. A task that straddles the changeover date
would have any hours before the changeover date costed at $10 per hour and
its hours after the first costed at $15.

Viewing the costs in the usage views filtered for dates and/or with the
timeline set to appropriate date increments might do the trick for you
without having to resort to programming.

 

[Russ]

John,

Your help has been great! I looked at each of the option you proposed and
think I’m really close to a solution.

I am having a little trouble though, using the TimeScaleData Method. Here’s
a portion of the code that should make my Cost1 field equal my FY05 cost.
Cost2 and Cost3 will be my FT06 and FY07 costs respectively (code not shown).
The code works sometimes; but is finicky when there’s null or 0 hours
between dates. Maybe I’m using the TimeScaleData Method incorrectly. Any
ideas?


Sub Russ8()

Dim TSV As TimeScaleValues

For i = 1 To ActiveProject.Resources.Count
ActiveProject.Resources.Item(i).Cost1 = 0
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 = 0
Next j
Next i

For i = 1 To ActiveProject.Resources.Count
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count

SD = ActiveProject.Resources.Item(i).Assignments.Item(j).Start
FD =
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(2).EffectiveDate - 1

Set TSV =
ActiveProject.Resources.Item(i).Assignments.Item(j).TimeScaleData(SD, FD, _
TimescaleUnit:=pjTimescaleDays)

ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 =
TSV.Item(1).Value
ActiveProject.Resources.Item(i).Cost1 =
ActiveProject.Resources.Item(i).Cost1 + TSV.Item(1).Value

Next j
Next i

End Sub

 

[Russ]

Because not all our resources have the same dates as their fiscal year change
over dates, I'm having trouble rolling up my FY costs in a single report. I
just posted some code I've been trying to make work, back to John. I wish I
could just get it to work right.

 

[John]

John,

Your help has been great! I looked at each of the option you proposed and
think I¹m really close to a solution.

I am having a little trouble though, using the TimeScaleData Method. Here¹s
a portion of the code that should make my Cost1 field equal my FY05 cost.
Cost2 and Cost3 will be my FT06 and FY07 costs respectively (code not shown).
The code works sometimes; but is finicky when there¹s null or 0 hours
between dates. Maybe I¹m using the TimeScaleData Method incorrectly. Any
ideas?


Sub Russ8()

Dim TSV As TimeScaleValues

For i = 1 To ActiveProject.Resources.Count
ActiveProject.Resources.Item(i).Cost1 = 0
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 = 0
Next j
Next i

For i = 1 To ActiveProject.Resources.Count
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count

SD = ActiveProject.Resources.Item(i).Assignments.Item(j).Start
FD =
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(2).Effect
iveDate - 1

Set TSV =
ActiveProject.Resources.Item(i).Assignments.Item(j).TimeScaleData(SD, FD, _
TimescaleUnit:=pjTimescaleDays)

ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 =
TSV.Item(1).Value
ActiveProject.Resources.Item(i).Cost1 =
ActiveProject.Resources.Item(i).Cost1 + TSV.Item(1).Value

Next j
Next i

End Sub

Russ,
You're welcome. I'm glad you are getting what you need.

Your code is a bit busier than it needs to be - a little VBA shorthand
could significantly cut down on the bulk. For example, if you want to
loop through all resources in the project and each assignment of those
resources, here is the quick code:
For Each r In ActiveProject.Resources
r.Cost1 = 0
For Each a In r.Assignments
a.Cost1 = 0
Next a
Next r

A similar construct could be applied to the remainder of your macro.

With regard to handling null values, here is the method I use (I show it
for tasks, but a similar approach can be used for resources and/or
assignments):
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
Set tsv = t.TimeScaleData(t.Start, t.Finish,
pjTaskTimescaledCost, pjTimescaleDays)
For j = 1 To tsv.count
If tsv(j) = "" Then
Else
t.cost1 = t.cost1 + tsv(j)
End If
Next j
End If
Next t

One other thing I noticed about your code. It looks like you may be
replicating the code three times to pick up data for each of the fiscal
years. That's a lot of code duplication. I would test each assignment
date range and then funnel the timescale data into the proper cost
"bucket" (i.e. Cost1, Cost2 or Cost3). It is just a matter of setting up
your SD and FD based on the assignment date range. If some assignments
span more than one fiscal year, you could use multiple SD and FD
variables (e.g. SD1, SD2, etc.). But, if you are more comfortable with
your approach, then carry on.

Hope this helps.
John
Project MVP

 

[Russ]

Got it working. Thanks for all your help John! Here's the code:

Sub Russ10()

Dim TSV As TimeScaleValues
Dim sumcost As Variant

For i = 1 To ActiveProject.Resources.Count
ActiveProject.Resources.Item(i).Cost1 = 0
ActiveProject.Resources.Item(i).Cost2 = 0
ActiveProject.Resources.Item(i).Cost3 = 0
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 = 0
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost2 = 0
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost3 = 0
Next j
Next i

'COST1 FY05

For i = 1 To ActiveProject.Resources.Count
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
SD = ActiveProject.Resources.Item(i).Assignments.Item(j).Start
FD = ActiveProject.Resources.Item(i).Assignments.Item(j).Finish
Set TSV =
ActiveProject.Resources.Item(i).Assignments.Item(j).TimeScaleData(SD, FD,
pjAssignmentTimescaledCost, pjTimescaleDays)
sumcost = 0
For k = 1 To TSV.Count
If TSV.Item(k).Value = "" Then
Else
If TSV.Item(k).StartDate <
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(2).EffectiveDate Then
sumcost = sumcost + TSV.Item(k).Value
End If
End If
Next k
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost1 = sumcost
ActiveProject.Resources.Item(i).Cost1 =
ActiveProject.Resources.Item(i).Cost1 + sumcost
Next j
Next i

' COST2 FY06

For i = 1 To ActiveProject.Resources.Count
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
SD = ActiveProject.Resources.Item(i).Assignments.Item(j).Start
FD = ActiveProject.Resources.Item(i).Assignments.Item(j).Finish
Set TSV =
ActiveProject.Resources.Item(i).Assignments.Item(j).TimeScaleData(SD, FD,
pjAssignmentTimescaledCost, pjTimescaleDays)
sumcost = 0
For k = 1 To TSV.Count
If TSV.Item(k).Value = "" Then
Else
If TSV.Item(k).StartDate >=
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(2).EffectiveDate
And TSV.Item(k).StartDate <
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(3).EffectiveDate Then
sumcost = sumcost + TSV.Item(k).Value
End If
End If
Next k
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost2 = sumcost
ActiveProject.Resources.Item(i).Cost2 =
ActiveProject.Resources.Item(i).Cost2 + sumcost
Next j
Next i

' COST3 FY07

For i = 1 To ActiveProject.Resources.Count
For j = 1 To ActiveProject.Resources.Item(i).Assignments.Count
SD = ActiveProject.Resources.Item(i).Assignments.Item(j).Start
FD = ActiveProject.Resources.Item(i).Assignments.Item(j).Finish
Set TSV =
ActiveProject.Resources.Item(i).Assignments.Item(j).TimeScaleData(SD, FD,
pjAssignmentTimescaledCost, pjTimescaleDays)
sumcost = 0
For k = 1 To TSV.Count
If TSV.Item(k).Value = "" Then
Else
If TSV.Item(k).StartDate >=
ActiveProject.Resources.Item(i).CostRateTables.Item(1).PayRates.Item(3).EffectiveDate Then
sumcost = sumcost + TSV.Item(k).Value
End If
End If
Next k
ActiveProject.Resources.Item(i).Assignments.Item(j).Cost3 = sumcost
ActiveProject.Resources.Item(i).Cost3 =
ActiveProject.Resources.Item(i).Cost3 + sumcost
Next j
Next i
End Sub

 

[John]

Russ,
Great! And you're welcome.

John