Do More Than Manage
Gord Schmidt's Ideas for Doing More with Microsoft Project Server
Summary Task

SQL to return an Outline Code Value

Sometimes one needs to access Project Outline Code values using SQL. Below is an example of a stored procedure that returns a list of projects with the project name, the value of Enterprise Number 1, the value of Enterprise Text 1 and the value of Enterprise Project Outline Code 2.

The SELECT statement will reference five tables: one for the list of projects, one for the enterprise fields (non-outline) and three for the outline codes.

TablePurpose/Function
msp_web_projectsThe list of projects published to Project Server.
msp_view_proj_projects_entEnterprise information for projects.
msp_outline_codesData about custom outline code definitions associated with a project.
msp_field_attributesData about field attributes such as custom WBS, custom field name aliases, and custom field formulae.
msp_attributes_strings

Custom WBS code definition, alias, and formulae defined in msp_field_attributes.

The first part of the stored procedure determines the Project Id for the latest copy of the Global Template. The Global Template is stored in the Project Server database as just like another project (other than the Project Type). Every time the Global Template is updated though, a new record is created and the old version remains.

The next part of the stored procedure sets the constant for the Enterprise Project Outline Code #2. For the complete list of constants for the Enterprise Fields, see this page on the Microsoft site.

The final part of the proc generates the recordset. Left joins are used in case one of the Enterprise fields has not been set.

CREATE PROCEDURE GetProjectList AS

BEGIN

 -- Determine Project Id for latest Global Template
 DECLARE @global_template_proj_id int
 SELECT  @global_template_proj_id =
         (SELECT max(proj_id) FROM msp_projects WHERE proj_type = 2)
         
 -- Define field code for Enterprise Project Outline Code 2
 DECLARE @epoc2_field_code int
 SELECT  @epoc2_field_code = 188744590

 -- Create list of projects
 SELECT
   projects.proj_name ,
   ent.ProjectEnterpriseNumber1 ,
   ent.ProjectEnterpriseText1 ,
   epoc2.oc_cached_full_name AS ProjectEnterpriseOutlineCode2

 FROM
   msp_web_projects AS projects

   -- Use left joins in case a project does not have a value
   -- for the outline code.
   LEFT JOIN msp_view_proj_projects_ent AS ent
    ON  ent.wproj_id           = projects.wproj_id

   LEFT JOIN msp_outline_codes AS epoc2
    ON  epoc2.code_uid         = ent.ProjectEnterpriseOutlineCode2ID
    AND epoc2.proj_id          = @global_template_proj_id
    AND epoc2.oc_field_id      = @epoc2_field_code

   LEFT JOIN msp_field_attributes AS epoc2a
    ON  epoc2a.proj_id         = @global_template_proj_id
    AND epoc2a.attrib_field_id = @epoc2_field_code
    AND epoc2a.attrib_id       = 206
    AND epoc2a.attrib_value    = -1

   LEFT JOIN msp_attribute_strings AS epoc2s
    ON  epoc2s.proj_id         = epoc2a.proj_id
    AND epoc2s.as_id           = epoc2a.as_id
    AND epoc2s.as_position     = 0
 
 WHERE
   projects.wproj_type = 2

 RETURN
 
END
GO

If you are planning on diving into the SQL Server database for Project Server in order to create a custom report, make sure to set aside several hours. There is a lot of functionality in Project Server and an equal quantity of tables/fields to support it.

You can start by referring to the Microsoft Office Project 2003 Data Reference (PjDB.htm) and the Microsoft Office Project Server 2003 Data Reference (SvrDB.htm). These documents are on the Project Server installation CD. They are also part of the downloadable Project Server 2003 In-Box Documentation. Another good reference is the Field ID Values for Enterprise Custom Fields and Outline Codes.

Posted by Gord Schmidt on Monday, January 31, 2005 | Permalink | Extend | Resources

Milestone