The outline codes each have their own table with the
lookup values you set in MS Project. The trick is
getting the assigned values within each project. For
exammple, a project state may be In Planning, In
Progress, On Hold, etc... which one is in PROJ_ID 152?
Here is the SQL code to select the codes from the first 8
custom project outline code fields. Hope this helps.
Kevin
SELECT TOP 100 PERCENT CP.PROJ_UID, CP.PROJ_NAME,
CP.PROJ_PROP_TITLE, CP.ENTERPRISE_PROJECT_CODE1,
PO1.PROJ_OUTLINECODE_NAME,
PO1.PROJ_OUTLINECODE_DESCRIPTION,
CP.ENTERPRISE_PROJECT_CODE2, PO2.PROJ_OUTLINECODE_NAME AS
Expr1,
CP.ENTERPRISE_PROJECT_CODE3,
PO3.PROJ_OUTLINECODE_NAME AS Expr2,
CP.ENTERPRISE_PROJECT_CODE4,
PO4.PROJ_OUTLINECODE_NAME AS Expr3,
CP.ENTERPRISE_PROJECT_CODE5, PO5.PROJ_OUTLINECODE_NAME AS
Expr4,
PO5.PROJ_OUTLINECODE_DESCRIPTION AS
Expr5, CP.ENTERPRISE_PROJECT_CODE6,
PO6.PROJ_OUTLINECODE_NAME AS Expr6,
CP.ENTERPRISE_PROJECT_CODE7,
PO7.PROJ_OUTLINECODE_NAME AS Expr7,
CP.ENTERPRISE_PROJECT_CODE8,
PO8.PROJ_OUTLINECODE_NAME AS Expr8
FROM dbo.MSP_CUBE_PROJECTS CP INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_1 PO1 ON
CP.ENTERPRISE_PROJECT_CODE1 = PO1.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_2 PO2 ON
CP.ENTERPRISE_PROJECT_CODE2 = PO2.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_3 PO3 ON
CP.ENTERPRISE_PROJECT_CODE3 = PO3.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_4 PO4 ON
CP.ENTERPRISE_PROJECT_CODE4 = PO4.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_5 PO5 ON
CP.ENTERPRISE_PROJECT_CODE5 = PO5.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_6 PO6 ON
CP.ENTERPRISE_PROJECT_CODE6 = PO6.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_7 PO7 ON
CP.ENTERPRISE_PROJECT_CODE7 = PO7.PROJ_OUTLINECODE_ID
INNER JOIN
dbo.MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_8 PO8 ON
CP.ENTERPRISE_PROJECT_CODE8 = PO8.PROJ_OUTLINECODE_ID
ORDER BY CP.PROJ_UID
