SQL Query Analyzer- Help with pulling backend info from resource s

[Napone]

I'm trying to use SQL Query Analyzer tool to pull the following information
from resource sheet info:

Name,Enterprise Text1,Enterprise Resource Outline Code1,Enterprise
Number1,Standard Rate,RBS,Base Calendar, and Group.

I've identify the tables, msp_numbers_fields, msp_resources,enterprise
text1=205521442. I have issue with pulling Enterprise Text1 in relation to
msp_resources table. Any ideas?

-Napone

-Napone

 

[Ed Morrison]

SELECT r.RES_NAME, t1.TEXT_VALUE AS Res_Ent_Text1, n.NUM_VALUE AS
Res_Ent_Num1, oc1.OC_CACHED_FULL_NAME AS RBS,
oc2.OC_CACHED_FULL_NAME AS Res_Ent_OC1,
oc1.OC_FIELD_ID, dbo.MSP_CALENDARS.CAL_NAME
FROM dbo.MSP_TEXT_FIELDS t1 RIGHT OUTER JOIN
dbo.MSP_RESOURCES r INNER JOIN
dbo.MSP_CALENDARS ON r.PROJ_ID =
dbo.MSP_CALENDARS.PROJ_ID AND r.RES_UID = dbo.MSP_CALENDARS.RES_UID ON
t1.PROJ_ID = r.PROJ_ID AND t1.TEXT_REF_UID = r.RES_UID
LEFT OUTER JOIN
dbo.MSP_NUMBER_FIELDS n ON r.PROJ_ID = n.PROJ_ID AND
r.RES_UID = n.NUM_REF_UID LEFT OUTER JOIN
dbo.MSP_PROJECTS p2 INNER JOIN
dbo.MSP_OUTLINE_CODES oc2 ON p2.PROJ_ID = oc2.PROJ_ID
INNER JOIN
dbo.MSP_CODE_FIELDS c2 ON oc2.CODE_UID = c2.CODE_UID
ON r.PROJ_ID = c2.PROJ_ID AND r.RES_UID = c2.CODE_REF_UID LEFT OUTER JOIN
dbo.MSP_OUTLINE_CODES oc1 INNER JOIN
dbo.MSP_CODE_FIELDS c1 ON oc1.CODE_UID = c1.CODE_UID
INNER JOIN
dbo.MSP_PROJECTS p1 ON oc1.PROJ_ID = p1.PROJ_ID ON
r.PROJ_ID = c1.PROJ_ID AND r.RES_UID = c1.CODE_REF_UID
WHERE (r.PROJ_ID = 1) AND (r.RES_TYPE = 1) AND (n.NUM_FIELD_ID =
205521442) AND (p1.PROJ_TYPE = 2) AND (p2.PROJ_TYPE = 2) AND
(oc2.OC_FIELD_ID = 205521482) AND (t1.TEXT_FIELD_ID =
205521542) AND (oc1.OC_FIELD_ID = 205521540)
Standard rates and calendars are in the resource's binary.

Hope this helps.

 

[Napone]

Ed, thanks for a quick reply. I tried the sql script below and no output.
The query commands are good with no errors but no outpu. I know I have more
than 300 resource pools.

Let me clarify my report and make it a small start. Generate a report for
each published projects to include total actual work and work hours per month
per resource assigned.

I appreciate your efforts in this. Yes, I bought your EPM learning books
and they are very very useful. I'm doing integratoin more of the backend
side for Project Server 2003 with other systems - financials.

-Napone

 

[Ed Morrison]

Napone,
Sorry, my query assumes that all resources have all values associated. Just
tweak the joins to account for null records in the custom field tables.

In \Program Files\Microsoft Office Project Server 2003\HELP\1033\pjsrvdb.htm
on your Project Server machine you'll find "Find all timephased data for a
particular project". I think this procedure will meet your needs.

Glad to hear you enjoyed the books and best of luck integrating with your
financial system.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[Napone]

Ed,

Thank you very much for your help. This is what I need.

 

[Joe Mullen]

You may simply use this tool:
http://www.bogdanov-associates.com/eng.asp?rubr_id=484

J

 

[Gary L. Chefetz [MVP]]

Joe:

Post some help, not just advertisements, otherwise your posts will be
considered spam.