Documentation of custom enterprise fields and outlines (MSPS2003)

[Burghard S.]

How can I document all my customized fields and outlines without writing
everything manually into a Word or Excel file?
I would assume that the formulas and lookup tables are stored somewhere in
the Project Server database, but haven't found them there, only the
calculated values.
I've found the posting from 2005, but as the solutions are not listed...

Any help to avoid cumbersome typing is appreciated ;-)

Burghard

 

[Paul Conroy]

I don't have a query to get all the information you probably need, but these
will get the majority. It shouldn't be too much effort to fill in the gaps.

Run the query against the published database to get a list of CF's

SELECT MD_PROP_NAME
FROM dbo.MSP_CUSTOM_FIELDS_PUBLISHED_VIEW

Run this query against the published database to get a list of CF's and
associated lookup fields

SELECT dbo.MSP_CUSTOM_FIELDS_PUBLISHED_VIEW.MD_PROP_NAME,
dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW.LT_NAME
FROM dbo.MSP_CUSTOM_FIELDS_PUBLISHED_VIEW INNER JOIN
dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW ON

dbo.MSP_CUSTOM_FIELDS_PUBLISHED_VIEW.MD_LOOKUP_TABLE_UID =
dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW.LT_UID

Run this query against the published database to get a list of looktables
and their values.

SELECT dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW.LT_NAME,
dbo.LookupTableTrees.LT_VALUE_FULL
FROM dbo.LookupTableTrees INNER JOIN
dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW ON
dbo.LookupTableTrees.LT_UID = dbo.MSP_LOOKUP_TABLES_PUBLISHED_VIEW.LT_UID


--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com

 

[Paul Conroy]

Your questions stirred up some interest in me this morning, so I've created a
couple of basic SRS reports to display some rudimentary CF data.

You can download these reports from:

http://theprojectserverguru.spaces.live.com/blog/cns!D74683E5EE2E06E2!390.entry

--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com

 

[Burghard S.]

Paul,
thanks for the quick reply.
I have, however, a problem in understanding/using it:
What exactly is the "published database"? I assumed that this is the MSP
database with all the project data in it, but I couldn't find the
dbo.MSP_CUSTOM_FIELDS_PUBLISHED_VIEW or any of the other views you've listed.
Do I have to create them and, if so, what's the code for it?
I'm just starting with SQL so maybe I've missed something vital here.

Regards
Burghard

 

[Burghard S.]

I forgot to ask:
Are these views available only in MSPS2007? We're running 2003

 

[Paul Conroy]

Sorry Burghard, these only apply to 2007 !

I don't have an install of 2003 to hand

Paul
--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com

 

[Burghard S.]

EUREKA! (at least for names and formulas ...)
Here's what I've done:
create 3 new views:

One for the formulas:

CREATE VIEW dbo.ENT_Custom_Field_Formulas
AS
SELECT TOP 100 PERCENT dbo.MSP_CONVERSIONS.CONV_STRING AS [Project Field
Name],
dbo.MSP_ATTRIBUTE_STRINGS.AS_VALUE AS [Custom Field
Formula], dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_FIELD_ID
FROM dbo.MSP_CONVERSIONS INNER JOIN
dbo.MSP_FIELD_ATTRIBUTES ON
dbo.MSP_CONVERSIONS.CONV_VALUE = dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_FIELD_ID
INNER JOIN
dbo.MSP_ATTRIBUTE_STRINGS ON
dbo.MSP_FIELD_ATTRIBUTES.AS_ID = dbo.MSP_ATTRIBUTE_STRINGS.AS_ID INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_FIELD_ATTRIBUTES.PROJ_ID =
dbo.MSP_PROJECTS.PROJ_ID AND
dbo.MSP_ATTRIBUTE_STRINGS.PROJ_ID =
dbo.MSP_PROJECTS.PROJ_ID
WHERE (dbo.MSP_CONVERSIONS.CONV_STRING LIKE '% Enterprise%') AND
(dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_ID = 207) AND
(dbo.MSP_PROJECTS.PROJ_TYPE = 2)

One for the names:"
CREATE VIEW dbo.ENT_Custom_Field_Names
AS
SELECT TOP 100 PERCENT dbo.MSP_CONVERSIONS.CONV_STRING AS [Project Field
Name],
dbo.MSP_ATTRIBUTE_STRINGS.AS_VALUE AS [Custom Field
Name], dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_FIELD_ID
FROM dbo.MSP_CONVERSIONS INNER JOIN
dbo.MSP_FIELD_ATTRIBUTES ON
dbo.MSP_CONVERSIONS.CONV_VALUE = dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_FIELD_ID
INNER JOIN
dbo.MSP_ATTRIBUTE_STRINGS ON
dbo.MSP_FIELD_ATTRIBUTES.AS_ID = dbo.MSP_ATTRIBUTE_STRINGS.AS_ID INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_FIELD_ATTRIBUTES.PROJ_ID =
dbo.MSP_PROJECTS.PROJ_ID AND
dbo.MSP_ATTRIBUTE_STRINGS.PROJ_ID =
dbo.MSP_PROJECTS.PROJ_ID
WHERE (dbo.MSP_CONVERSIONS.CONV_STRING LIKE '% Enterprise%') AND
(dbo.MSP_FIELD_ATTRIBUTES.ATTRIB_ID = 206) AND
(dbo.MSP_PROJECTS.PROJ_TYPE = 2)
ORDER BY dbo.MSP_CONVERSIONS.CONV_STRING

and finally one for the combination:

CREATE VIEW dbo.ENT_Custom_Fields
AS
SELECT TOP 100 PERCENT dbo.ENT_Custom_Field_Names.[Project Field Name],
dbo.ENT_Custom_Field_Names.[Custom Field Name],
dbo.ENT_Custom_Field_Formulas.[Custom Field Formula]
FROM dbo.ENT_Custom_Field_Names LEFT OUTER JOIN
dbo.ENT_Custom_Field_Formulas ON
dbo.ENT_Custom_Field_Names.ATTRIB_FIELD_ID =
dbo.ENT_Custom_Field_Formulas.ATTRIB_FIELD_ID

This will list all customized enterprise fields which have at least a
modified name and their respective formula.