Updating Resource Rates

[Robin Roe]

Just wondering is there an easy way to update resource rates from a certain
date without having to manually go into each resource. I currently have all
the new rates in Excel.

Similarly, I would also like to add in a budget figure for increased rates
in future years which I hope to achieve by adding a standard % increase after
a specified date. (These figures will be firmed up when known as per above).
I know how to do this manually but, again is there an automated way using a
SQL script or similar to do this.

Thanks

--
Robin

[Chak]

Hi Robin and All

I seen many of the postings here about this issue. Thought to
contribute the commnity with my souce code. This following macro will
be automated the process of assigning the rates or updating the
existing rates on enterprise resource pool. This macro in working
condition as I am using often.

You need to run this macro with enterprise resource pool.

A) Requirements:

Before you start run this macro,you need to create a table on database
on SQL Server (I am using Project Server database here):

1)

Run SQL Script to create the table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ResRates]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ResRates]
GO

CREATE TABLE [dbo].[ResRates] (
[ResName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EffDate] [smalldatetime] NULL ,
[StdRate] [decimal](15, 2) NULL ,
[OvtRate] [decimal](18, 0) NULL ,
[UseCost] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

2) Fill the data in to table (Resource Name, Effective date, StdRate,
Overtime Rate, UseCost)

3) Get the details of Database:

Server Name, Database Name, UserId, Password to access the Data
from ResRates table

B) Automation Steps (Update the enterprise resource rates in Costs A
table):

1) Open the Enterprise resource pool with Read/Wrie checkout option

2) Tools->Macro->Visual Basic Editor

3) View -> Project Explorer

4) Click on VBAProject(Checked-out Enterprise Resources) -> Microsoft
Project Objects ->
ThisProject(checked-our Enterprise Resources)

5) Paste the below macro source code

6) Tools -> References

choose Microsoft Activex Data Objects 2.5 Library
choose Microsoft Project 11.0 Object Libarary
choose VisualBasic for Applications
Click OK

7)Specify the Servername, databasename, userid and passwd info under
Create database connection ( see the macro source code)

8) Run Macro (F5)

C)Macro Source code:
************************************************************************
Sub ResRate()
'Author: Chak Tukkadi
'Requisities: Sql Server Table: ResRates
'
Columns:ResName,EffDate,StdRate,OvtRate,UseCost
'

Dim objcn As ADODB.Connection
Dim objrs As ADODB.Recordset
Dim StrSQL As String
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates
Dim EffDt As String
Dim StndRate As String
Dim OvtRate As String
Dim UseCost As String

Set objcn = New ADODB.Connection

'Create Database Connection
'Specify DBServer Database userid and pwd info here
objcn.Open "Provider=SQLOLEDB;" & _
"SERVER=servername,DATABASE=dbname;uid=DBuserid;pwd=DBPwd"

'Define Recordser object
Set objrs = New ADODB.Recordset

'Query to get the operating plans list
StrSQL = "SELECT ResName,EffDate,StdRate,OvtRate,UseCost "
StrSQL = StrSQL & " FROM ResRates "
StrSQL = StrSQL & " ORDER BY ResName "

'Execute SQL Query to get the results in Recordset
objrs.Open StrSQL, objcn, 3, 3

'Scroll through the Recordset
While Not objrs.EOF

EffDt = Mid(objrs(1), 1, 10)
StndRate = CStr(objrs(2))
OvtRate = CStr(objrs(3))
UseCost = CStr(objrs(4))

Set rs = ActiveProject.Resources

'Scan the resource list in enterprise resource pool row by row and
' check the resource name is same as name in recordset. If So, then
'Update cost table A for the resource in resource pool, then read the
next resource from recordset

For Each r In rs

If r.Name = objrs("ResName") Then
Set prs = r.CostRateTables("A").PayRates

prs.Add EffectiveDate:=EffDt, StdRate:=StndRate, OvtRate:=OvtRate,
CostPerUse:=UseCost

Exit For

End If

Next r

objrs.MoveNext
Wend

objrs.Close
End Sub

************************************************************************************************
If you need any further help on this, send email to me.

Thanks
Chak

+++++++++++++++++++++++++++++++++++++++++++++++

[Aaron Tamblyn]

Won't changing the base rate for a resource potentially cause problems in
your existing projects when you republish them as they will they not
recalcalate all assignments for that resource to the new rate even entries
prior to the data that the rate change was made?

Project does have five resource rates and there are third party tools that
provide the ability to have more resource rates. My experience with resource
rates is that you need to carefully think it through.

[Chak]

Hi Aaron,

I assume that your are running this macro with enterprise resource
pool but not with individual project plan. Also the names in the table
should match with Eneterprise resource pool names.

Note: This macro written to change the resource rate on default ( Cost
A) table. It will update the standard rate. If the project plan's
tasks are using the rate from different table (like Cost B), then
accordingly you need to change the source code to update the respective
Cost table.

Test the macro: Once you run the macro, click on resource name and see
whether new rate got populated on Cost A as per your entry in SQL
Server table. Also see the Standard rate for that resource in Resource
pool. If it's a same of your SQL table, Save the Respurce pool and
exit the application.

Recommendation: Please test this macro in development environment prior
to using in production.

Thanks
Chak

****************************************************************************************