Local Custom Fields

Article
11/12/2007

Introduction

This post contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.

This post contains two sections:

- Schema fragments, showing the core tables required to traverse local custom field data

- Sample queries, showing how to extract custom field and lookup table data for a specific project

Many of the principles of local custom fields were initially developed in earlier Project Server versions and further questions may be answered by the server schema documentation supplied with those versions, for example:

DOCS/PJDB.HTM // Primary source of custom field schema documentation

DOCS/PJSVR.HTM // Additional enumerations

Both of these files are supplied on the Project Server 2003 installation media.

Joining Publishing and Reporting

It is recommended that you implement views in the Reporting database that reference the four tables below as well as MSP_PROJECT_RESOURCES (as local resources are aggregated in the Reporting database)

For example:

 CREATE VIEW dbo.MSP_PROJECT_RESOURCES AS
SELECT * FROM [PUBLISHED_DB].dbo.MSP_PROJECT_RESOURCES

Note that if the Reporting Database has been installed on a separate SQL Server instance that you will have to use a four part name in the views including the linked server name.

Schema (Fragment)

This section documents the relevant attributes and entities required to extract local custom field data for reporting purposes.

This table holds the master list of all local custom fields in use, keyed on the Proj_UID. Obtain the MS_PROP_UID (which keys the custom field values in the next two tables) from the MD_PROP_NAME or by using the MSP_WEB_VIEW_FIELDS table (get the WFIELD_UID that corresponds to the WFIELD_NAME_OLEDB of the local custom field that you want) – we use “Magic GUIDs” to identify local custom fields (ie 000039b7-8bbe-4ceb-82c4-fa8c0b400033 is always TaskText1)

This table contains the values (or a pointer to a lookup table value) for task local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry MD_LOOKUP_TABLE_UID in the previous table data is held in the appropriate *_VALUE column.

This table contains the values (or a pointer to a lookup table value) for resource local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry, MD_LOOKUP_TABLE_UID in the Project Custom Fields table, data is held in the appropriate *_VALUE column.

This table contains lookup table values pointed to by the previous two tables. Assuming either text or hierarchical selection the LT_VALUE_TEXT and LT_VALUE_FULL are most interesting. (_FULL contextualizes the selection and corresponds to the value stored in the enterprise equivalents in the Reporting database.

Working With Project Task and Resource Custom Fields

This section contains a series of queries to get you started. These all return tables that can be joined (use an outer join to ensure that data doesn’t get stripped) onto our Reporting USERVIEW views to obtain custom field data.

Note 1: I’m assuming that the majority of data will be in text custom fields or outline codes, the *_custom_field_values tables contain other buckets for the other field types.

Note 2: I haven’t qualified these query snippets with a PROJ_UID so they will return all data, you should remember to add this field for project-specific reports otherwise performance will be sub optimal.

Note 3: I’ve packaged the resource outline code query in two ways, the second as a sample function to make usage a little bit simpler, as the function abstracts out all the joining to get the values.

Join Table of Task_UID and a text Custom Field

 SELECT task.task_uid, tlcf.text_value 
FROM dbo.msp_tasks AS task 
INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
ON (task.task_uid = tlcf.task_uid) 
INNER JOIN dbo.msp_project_custom_fields AS plcf 
ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
WHERE plcf.md_prop_name = N'CustomFieldName'

Join Table of Res_UID and a text Custom Field

 SELECT res.res_uid, rlcf.text_value 
FROM dbo.msp_project_resources AS res 
INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
ON (res.res_uid = rlcf.res_uid) 
INNER JOIN dbo.msp_project_custom_fields AS plcf 
ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
WHERE plcf.md_prop_name = N'RescourceCustomFieldName'

Join Table of Task_UID and an Outline Code

 SELECT task.task_uid, tloclt.lt_value_full, tloclt.lt_value_text 
FROM dbo.msp_tasks AS task 
INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
ON (task.task_uid = tlcf.task_uid) 
INNER JOIN dbo.msp_project_lookup_table_structures AS tloclt 
ON (tlcf.code_value = tloclt.lt_struct_uid) 
INNER JOIN dbo.msp_project_custom_fields AS plcf 
ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
WHERE plcf.md_prop_name = N'TaskOutlineCodeName'

Join Table of Res_UID and an Outline Code

 SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
FROM dbo.msp_project_resources AS res 
INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
ON (res.res_uid = rlcf.res_uid) 
INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
ON (rlcf.code_value = rloclt.lt_struct_uid) 
INNER JOIN dbo.msp_project_custom_fields AS plcf 
ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
WHERE plcf.md_prop_name = N'RescourceOutlineCodeName'

Sample Function for Ease of Use

 CREATE FUNCTION dbo.Custom_ResOC (@OutlineCodename NVARCHAR(50)) 
RETURNS TABLE AS 
RETURN (SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
FROM dbo.msp_project_resources AS res 
INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
ON (res.res_uid = rlcf.res_uid) 
INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
ON (rlcf.code_value = rloclt.lt_struct_uid) 
INNER JOIN dbo.msp_project_custom_fields AS plcf 
ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
WHERE plcf.md_prop_name = @OutLineCodename) 
GO 
-- Can be used in a join clause
SELECT * FROM dbo.Custom_ResOC(N'ResourceOutlineCodeName') 
GO