Find cost rate table information in PS 2007

[Owe Evans]

Hi,

I need to find at which cost rate a resource is assigned to a task in a
project plan. I need to get to this value in a stored procedure.

Looking in Published database and at table MSP_Resources the standard rate
is readable from column RES_STD_RATE but the cost rate is in binary format in
column RES_RATES_TABLE. The relation between a assignment and cost rate table
in use is determined by looking at MSP_Assignments.Assn_Cost_Rate_Table

(In PS 2003 Cost Rates was stored in separate table and could be easily read)

Anyone having an idea how I can get to this data?

Thanks,
Owe

 

[Stephen Sanderlin]

Microsoft does not recommend that you look in the Published database --
please use the Reporting database.

The best way to get Rate information for Resources is through the PSI's
Resource web service. You would use the ReadResource() method, which
would return a ResourceDataSet. There will be a table, ResourceRates,
that will contain this information.

--
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.

 

[Owe Evans]

Thanks Stephen.

Yes, this is a way I have figured out. Since I need this information
available in T-SQL I will create a service extracting information from
Resource service once a day droping it to a table I can read from.

BR,
Owe

 

[Freddie]

Do I have to loop through all the resources to get the information? Or can
you share the t-sql for me?

 

[Owe Evans]

Basically you retreive a dataset from Resource.asmx via ReadResources method.
This has one table called ResourceRates where you find the cost rates for
resources.

I dump this to a table with SclClient.SqlBulkCopy. The table columns
matchning columns in ResourceRates data table.

If you are interested in getting the cost rate for a particular resource in
..Net code you will have to loop through or use FindBy against the dataset.

 

[Freddie]

I have done that but I get nothing in the ResourceRates table of the dataset.
I have doubled checked with Project Pro and I have users with multiple cost
rates table but nothing in the dataset. Can someone help me?

 

[Owe Evans]

This is the syntax I use:
resSvc.ReadResources(String.Empty , false)

When hitting a breakpoint at this row in code I use the "hourglass" to see
the data in the dataset. It is filled fine. Do not know why yours is not.

 

[Freddie]

I do not know why it is not populating. I can loop through the resources but
for that table there are no records. Do I have to use the resource on a task
with the different cost rates for it to populate the cost rates table?

 

[Owe Evans]

I don't know eighter ...

Most of my resources have only Cost Rate Table A in ResourceRates table.
Some have several tables (A, B, C) and only table A has a standard rate
other than 0.