Anatess, Stephen and Jonathan,
Stephen and Jonathan: Thanks for your active participation and helping
Anatess.
Anatess: Thanks for your kind compliments and I hope we will get some
solution for your needs. Sorry, I was busy in conducting training
sessions for our PMs in my organization and unable to catch you on
this discussion thread.
Now, coming to the actual discussion:
All of you noticed the table name called “MSP_Resource_Rates”. The
issue is Microsoft desgined the application to store resource cost
tables information in binary format. They are using application layer
to display this information on enterprise resource pool. The good
thing here is, MSP_Resource_Rates always holds the project resources
information with all details like standard rate, effective date, cost
table name etc. We can take advantage of this feature.
Jonathan: You are absolutely in correct direction. You can have dummy
project and add all the enterprise resources to get SQL view to
generate the required resource information.
Anatess: Here are two solutions that I can recommend to address your
needs. One solution as Jonathan proposed to you and other solution as
Stephen proposed to you.
Solution 1: SQL view; advantage of this approach is no development
activity required; the down side is, some manual process is required
to manage this approach.
Solution 2: VBA, I seen Stephen was talking about this approach on his
postings. I am giving the sample macro to read enterprise resource
information. Instead of message box, you can capture info into csv
file or insert into new table
I am giving the detailed steps for both the solutions here.
******************************************************************
Solution 1: Dummy project and SQL Approach:
Steps:
1) Create a dummy project called “EPM Resource Rates”
2) Add all the enterprise resources using Build Team or similar
method
3) Save project plan in to server
4) Find the PROJ_ID for EPM Resource Rates by running following
SQL
SELECT PROJ_ID FROM MSP_PROJECTS
WHERE PROJ_NAME = ‘EPM Resource Rates’
5) Assume, you have PROJ_ID is 603
So run the following SQL to get the enterprise resource rate info
for each cost table with effective dates
SELECT MSP_RESOURCES.RES_NAME,
CASE MSP_RESOURCE_RATES.RR_RATE_TABLE
WHEN 0 THEN 'Cost Table A'
WHEN 1 THEN 'Cost Table B'
WHEN 2 THEN 'Cost Table C'
WHEN 3 THEN 'Cost Table D'
WHEN 4 THEN 'Cost Table E'
END AS RATE_TABLE,
MSP_RESOURCE_RATES.RR_FROM_DATE AS EFFECTIVE_DATE,
MSP_RESOURCE_RATES.RR_TO_DATE AS TILL_DATE,
MSP_RESOURCE_RATES.RR_STD_RATE, MSP_RESOURCE_RATES.RR_OVT_RATE
FROM MSP_RESOURCE_RATES INNER JOIN
MSP_RESOURCES ON MSP_RESOURCE_RATES.PROJ_ID =
MSP_RESOURCES.PROJ_ID
WHERE (MSP_RESOURCE_RATES.PROJ_ID = 603) AND
(MSP_RESOURCES.RES_NAME IS NOT NULL)
ORDER BY MSP_RESOURCES.RES_NAME
***********************************************************************************************************
Solution 2: VBA Approach
You no need to save this macro. Just copy and paste this code to
verify the results. Please remember, this code gives the complete idea
to develop code to read enterprise resource info with cost tables and
effective date info.
Please understand the purpose of below statements to expand the code
for other tables.
a) Set prs = r.CostRateTables("A").PayRates
Here we are referring to table A, if you want to get the info from
other tables, please change the CostRateTables accordingly
b) prs(1).EffectiveDate
Here we are getting the information from row 1; if you have multiple
row info, you have to give prs(2), prs(3) and so on next statements
STEPS:
1) Tools menu -> Enterprise Options -> Open Enterprise Resource Pool
2) Select one resource by selecting the check box against resource
name on Open Enterprise Resources dailog box
3) Click on "Open/Add" to open the enterprise resource info in
resource pool
4) Tools menu -> Macro -> Visual Basic Editor
5) Double click on "ThisProject(checked-ot Enterprise Resources)"on
project explorer from left pane to open the code window
6) Paste the following code and run the macro
Sub GetRates()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates
Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTables("A").PayRates
Dim dtEffective
Dim dblStandRate
dtEffective = prs(1).EffectiveDate
dblStandRate = prs(1).StandardRate
MsgBox ("Resource name is " & r.Name & " and with effective
from " & dtEffective & " and standard rate is " & dblStandRate)
Next r
End Sub
***************************************************************
Thanks
Chak
http://www.epmcentral.com
