historic resource costs

[Jenn]

I am having some trouble with historic and current resource cost rates. I
need both in my project as the project spans multiple fiscal years and salary
changes occur between and during fiscal years.

We use the project import wizard to update our employee (resource)
information. The excel file contains employee name (which become resource
name in MSP) and cost rate (which becomes standard rate in MSP) along with
some other info like phone #, etc. This file is generated from our corporate
server which updates employee rates weekly.

We have 75-100 employees. We update the resource portion of the project file
quarterly. (This is due to the fact that our salary information for some of
our staff can change every 3-4 months). What I would like to do is perform
the import from excel using the same file described above, but when I import
I would like to be able to set the Effective Date for the resource cost rates
equal to the day that I perform the import. Then I would like the cost rate
I am importing to be the new standard rate for present and future tasks.
As it is now, when I import the data I can either override all of the past
employee salary rates (so I get the new cost rates applied to both past and
future tasks) or I can keep the employee cost rates the original values
entered initially (so the old rate is applied to the present and future
tasks). What I want is to be able to tell project “For all tasks performed in
the past keep the employees rates before the import date and for this day and
after use the new rate that I am importing.â€. I know I can do this manually
by going into the cost tab and typing in the Effective Date and its
corresponding rate but for 75+ employees that would be very tedious to do
every quarter.

Does anyone know if there is a way to do this?
I know some basic VBA so if any one has any suggestions or could point me in
the right direction I would really appreciate it.
TIA!

 

[John]

I am having some trouble with historic and current resource cost rates. I
need both in my project as the project spans multiple fiscal years and salary
changes occur between and during fiscal years.

We use the project import wizard to update our employee (resource)
information. The excel file contains employee name (which become resource
name in MSP) and cost rate (which becomes standard rate in MSP) along with
some other info like phone #, etc. This file is generated from our corporate
server which updates employee rates weekly.

We have 75-100 employees. We update the resource portion of the project file
quarterly. (This is due to the fact that our salary information for some of
our staff can change every 3-4 months). What I would like to do is perform
the import from excel using the same file described above, but when I import
I would like to be able to set the Effective Date for the resource cost rates
equal to the day that I perform the import. Then I would like the cost rate
I am importing to be the new standard rate for present and future tasks.
As it is now, when I import the data I can either override all of the past
employee salary rates (so I get the new cost rates applied to both past and
future tasks) or I can keep the employee cost rates the original values
entered initially (so the old rate is applied to the present and future
tasks). What I want is to be able to tell project “For all tasks performed in
the past keep the employees rates before the import date and for this day and
after use the new rate that I am importing.â€. I know I can do this manually
by going into the cost tab and typing in the Effective Date and its
corresponding rate but for 75+ employees that would be very tedious to do
every quarter.

Does anyone know if there is a way to do this?
I know some basic VBA so if any one has any suggestions or could point me in
the right direction I would really appreciate it.
TIA!

Jenn,
I recently wrote a VBA macro for a client whose needs are similar to
yours, although the data is only updated annually (fiscal Oct) and the
values do not come from an external source file (the clients values are
entered via a userform with automatic escallation using a user defined
rate for each future fiscal year). Nonetheless, the principals still
apply.

I might also note that there were two posts recently asking how to
update the Project cost rate tables from Excel. I offered the suggestion
of using the import wizard to dump the various values into the Standard
Rate field and several spare resource text fields. The spare field data
can then be copied to the appropriate place in the Cost Rate Table. Done
manually this approach would be rather tedious in your case but the
copying could be automated with VBA (you mentioned you have dome
experience with VBA).

If I were in your place, I would write a custom macro to pull the data
from Excel and populate the Cost Rate Tables directly. Once it is
written, the update becomes a simple one-button process. Here are some
things I leaned when I wrote the macro for my client.
1. Be advised that each of the 5 cost rate tables can only hold 25
different rates (this may or may not be an issue in your case). However
by using all 5 tables, that's 125 different rates per resource.
2. When using the PayRates Method it should be possible to specify a
date as the index (according to VBA help), however this is not true. The
method will only reliably accept an integer as the index.
3. To start from the current year, I used a loop to find the current
effective date, deleted any rates beyond the current date and then
re-populated the table with the new rates. This approach solves the
problem of maintaining historical rates.
4. Time of day is part of the Effective Date. I found that if rates are
entered manually or by using the drop down calendar, Project uses the
Start time which is by default 8:00 AM. However, if the Effective Date
is entered via VBA, and no time is specified, the Effective Date will
start at 12:00 AM. In my mind, the latter is more realistic. For
example, what if an employee is scheduled to start earlier than the
normal default start on the first day of the fiscal year - the new rate
should apply.

Finally, during development of the VBA code I had occasions where I
needed to completely delete all pay rates. To do that I wrote the
following short macro. It may or may not be helpful if you choose to
write your own macro.

Sub PurgeRateTables()
Dim ans As Variant

ans = MsgBox(" C A U T I O N !" & Chr(13) &
Chr(13) & _
"This procedure will completely purge all pay rate" & Chr(13) & _
"information including, Std. Rate, Ovt. Rate, and" & Chr(13) & _
"Cost/Use from pay rate tables in this file" & Chr(13) & Chr(13) & _
"Proceed?", vbYesNo + vbExclamation + vbDefaultButton2, "Resource
Rate Escalator Utility")
If ans = vbNo Then Exit Sub
For Each R In ActiveProject.Resources
If Not R Is Nothing Then
For i = 1 To 5
Set PR = R.CostRateTables(i).PayRates
PR(1).StandardRate = 0
PR(1).OvertimeRate = 0
PR(1).CostPerUse = 0
If PR.Count > 1 Then
For j = PR.Count To 2 Step -1
PR(j).Delete
Next j
End If
Next i
End If
Next R
MsgBox "Pay rate information has been purged from this file", vbOKOnly,
"Resource Rate Escalator Utility"
End Sub

If you have any further questions, post again or you may contact me
direct via e-mail.

Hope this helps.
John
Project MVP