Local and Enterprise Custom Fields

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Custom fields extend the attributes of tasks, resources, or projects. Lookup tables for custom fields enable data to be collected in a consistent manner. You can filter, group, sort, and create OLAP cubes and reports based on custom field values. Local custom fields are limited in number and to the scope of one project. Enterprise custom fields are essentially unlimited; they are defined in Project Web Access and stored in Microsoft Office Project Server for use in all projects within an organization.

This article includes the following sections:

• Comparing Local and Enterprise Custom Fields

• Creating Lookup Tables and Enterprise Custom Fields

• Assignment Roll-down Custom Fields

• Formulas and Graphical Indicators

• Custom Fields and OLAP Cubes

• Custom Fields and the Reporting Database

• Localization of Lookup Tables

Comparing Local and Enterprise Custom Fields

In Project 2003, local custom fields and enterprise custom fields are limited to the same built-in types. Enterprise custom fields are stored in the enterprise global template and you can use Project Professional 2003 to edit enterprise custom fields when you open the enterprise global template. In Project Server 2007, enterprise custom fields are stored in the Published database and are created or edited using Project Web Access.

Note
You cannot edit enterprise custom fields in Microsoft Office Project Professional 2007 when you open the enterprise global project. Instead, use Project Web Access—or a custom application that uses the Project Server Interface (PSI)—to create and edit enterprise custom fields.

Local Custom Fields

As in Microsoft Office Project 2003, local custom fields are limited to the built-in types in Project 2007. You can rename local custom fields and assign values or lookup tables to them using Microsoft Office Project Standard or Microsoft Office Project Professional.

Project 2003 and Project 2007 have the same types of local custom fields, as follows:

• Cost1 to Cost10

• Date1 to Date10

• Duration1 to Duration10

• Finish1 to Finish10

• Flag1 to Flag20

• Number1 to Number20

• OutlineCode1 to OutlineCode10

• Start1 to Start10

• Text1 to Text30

Local Text custom fields can have value lists, but not hierarchical lookup tables. There is a separate OutlineCode type of local custom field that can use hierarchical lookup tables. You can publish a project that contains local custom fields to the Published database, but the local custom fields do not get copied to the Reporting database.

You can assign local custom fields to resources and tasks, but not to projects. In practice, if you use interim plans you should not use the task Start1-10 or Finish1-10 custom fields because Project uses those fields to store dates for interim baseline plans.

You can use Microsoft Visual Basic for Applications (VBA) to create and manipulate local custom fields, and to access enterprise custom fields stored in the enterprise global template. You cannot use the PSI to create or interact with local custom fields in projects.

To migrate a local custom field to an enterprise custom field, open the Custom Fields dialog box in Project Professional (on the Tools menu, click Customize and then click Fields). Select Task or Resource and the field type, click the local custom field you want to migrate, and then click Add Field to Enterprise. If you add a task or resource outline code as an enterprise custom field, it becomes an enterprise Text custom field with a hierarchical lookup table.

For more information about local custom fields and how to use the VBA object model to create them, see Using VBA with Local Custom Fields and Outline Codes.

Enterprise Custom Fields

Enterprise custom fields are stored in the Published database in Project Server. They are typically used by the Project Management Office (PMO) to collect data for roll-up reporting across an organization.

Enterprise custom fields can be assigned to projects, resources, and tasks in Project Server. Enterprise custom field types include the following:

• Cost

• Date

• Duration

• Flag

• Number

• Text

Enterprise custom fields of type Text that have hierarchical lookup tables act as outline codes. Unlike for local custom fields, there is no distinct OutlineCode type of enterprise custom field. Projects that are published to Project Server can contain both local and enterprise custom fields (Figure 1). Enterprise custom fields are shown with (Server) added to the name. If a local custom field is renamed, it is shown with the name of the field appended, such as (Text1). You can rename and configure local custom fields using Project Professional. To add a local custom field to the collection of enterprise custom fields, click Add Field to Enterprise in the Custom Fields dialog box.

Figure 1. A project can include both local and enterprise custom fields

While the number of local custom fields is limited to the 130 built-in types, the number of enterprise custom fields is unlimited for practical purposes.

Note
Project Server performance is reduced if there are very many enterprise custom fields. Project Server is tested for acceptable performance with up to 1,000 custom fields.

Creating Lookup Tables and Enterprise Custom Fields

You can use Project Web Access or a custom PSI application to create and edit enterprise custom fields and lookup tables. Project Professional 2007 allows you to view and select enterprise custom fields but not to edit them.

If an enterprise custom field requires a lookup table, create the lookup table first. Lookup tables replace value lists in earlier versions of Project, and can be shared by multiple custom fields.

Creating Lookup Tables with Project Web Access

In Project Web Access, click Server Settings in the Quick Launch, and then click Enterprise Custom Field Definition in the Enterprise Data section of the Server Settings page. In the Lookup Tables for Custom Fields section of the Custom Fields page, click New Lookup Table. Figure 2 shows the New Lookup Table page.

Figure 2. Creating a lookup table in Project Web Access

You can create the following types of lookup tables. There is no Flag type for lookup tables.

• Cost

• Date

• Duration

• Number

• Text

All lookup tables can be multivalue in Project 2007. In earlier versions of Project, only a few custom fields, for example, resource custom fields, could have multivalue lookup tables.

A lookup table of type Text can be hierarchical and includes a code mask. A hierarchical text lookup table acts as an outline code. For example in Figure 2, the top-level values are limited to two uppercase letters. The sublevels can include any number of characters; levels are separated by a dot (.) in the code mask. Lookup tables of type Cost, Date, Duration, and Number can include a list of values but cannot be hierarchical and do not include a code mask. Lookup table values of any type can include a description and phonetics value for pronunciation.

Figure 3. List of lookup tables in the Custom Fields page

Note
You cannot delete or rename the default lookup tables, which include RBS for the resource breakdown structure and State for the state of project proposals. RBS does not have any default values; a Project Server administrator can edit the RBS code mask and values. The default State values (Proposed, Approved, and Rejected) cannot not be changed.

Creating Custom Fields with Project Web Access

To create a new enterprise custom field in Project Web Access, click New Field on the Custom Fields page. You can choose one of the following Project Server entities:

• Project

• Resource

• Task

In the Custom Attributes section (Figure 4), you can choose whether the custom field uses a lookup table, a formula, or has no custom attribute. Some custom field types are limited in custom attributes; for example, a Flag custom field cannot have a lookup table or use a formula. If you select a lookup table, you can optionally select a default value and whether to allow hierarchical codes and multiple values. Other options include whether to display data or graphical indicators, and whether selecting a field value is required in enterprise projects.

Figure 4. Creating an enterprise custom field

After you click Save, the Custom Fields page (Figure 5) shows the new custom field.

Figure 5. List of enterprise custom fields in Project Web Access

Creating Lookup Tables and Custom Fields with the PSI

Because Project Web Access uses the PSI to create lookup tables and custom fields, you can use the same methods and datasets in a custom application. For example, you can import data from a line of business application and create a new lookup table for a resource custom field or modify a default lookup table such as RBS.

For an example class that uses the LookupTable Web service to create a hierarchical text lookup table and a new custom field, see Walkthrough: Creating a Hierarchical Lookup Table. For a sample method that uses the CustomFields Web service, see How to: Create an Enterprise Custom Field. For a description of creating simple and multilanguage lookup tables for testing, see Using the ProjTool Test Application.

Assignment Roll-down Custom Fields

You can roll-down task and resource custom fields to track assignments. On the New Custom Field or the Edit Custom Field page in Project Web Access, click Roll down, unless manually specified in the Calculation for Assignment rows section.

You can add task custom fields to the Task Usage view in Project Professional to see the custom field values for assignments. Similarly, you can add resource custom fields to the Resource Usage view to see or edit the custom field values for assignments there.

Assignment custom fields are Project Server entities that are also available in Project Web Access and the Reporting database. You can see assignment custom fields in statusing and timesheet pages in Project Web Access, as well as in Project Professional.

Figure 6 shows the Task Usage view with a column added for a task custom field named Task Location. The Task Location custom field uses the State - County - City lookup table created in Figure 1. You could also create a resource custom field that uses the same lookup table, and add the custom field column to the Resource Usage view.

Figure 6. Setting an assignment custom field in the Task Usage view

Note
An enterprise or a local assignment custom field in Project Server 2007 can be different in the Resource Usage and Task Usage views, even if the resource custom field and task custom field on which it is based uses the same lookup table.

Formulas and Graphical Indicators

Project managers often use formulas to calculate values for custom fields or to use with graphical indicators. For descriptions and syntax of the functions available for writing formulas, see the Reference section of Help in Project Standard or Project Professional, or see Project functions in Office Online. Project 2007 uses the same Jet Expression service for formulas that Microsoft Office Access 2007 uses. For general examples and more information about formulas and expressions, see Guide to expression syntax and Functions (arranged by category) in the online Help for Access.

You can create and edit formulas and add graphical indicators for enterprise custom fields on the New Custom Field and Edit Custom Field pages in Project Web Access. Formulas for resource custom fields are calculated only in Project Professional. For example, if you use the Project Web Access Resource Center page to add a resource custom field that has a formula, the custom field shows a blank value in Project Web Access. Formulas for enterprise custom fields in tasks and projects are calculated in Project Server.

To validate formulas and graphical indicators, Project Web Access calls Project Professional. Project Professional only validates the formula and graphical indicator definitions. When custom fields that have formulas or graphical indicators are applied to tasks and projects (but not to resources), Project Server calculates the values.

To validate formulas and graphical indicators, two conditions are necessary:

• The computer running Project Web Access must have the ActiveX grid control that Project Web Access uses installed. If the ActiveX control is not installed, Project Web Access asks you to install it.

• You must have Project Professional installed on the same computer where you are running Project Web Access.

Note

Exit Project Professional before you use Project Web Access to edit an enterprise custom field and change the formula or graphical indicators. When you click Save on the New Custom Field page, Project Web Access starts Project Professional to validate the formula and graphical indicators. You must also restart Project Professional to see the new or changed custom field in the lists of custom fields.

When you need to create a new formula, it can be easier to create a similar local custom field using Project Professional. Create a local custom field in the Custom Fields dialog box, and then click Add Field to Enterprise. You can delete the local custom field when you are done testing. The Edit Custom Fields page in Project Web Access (Figure 7) does not include a common function toolbar or a way to pick field names.

Figure 7. Creating custom field formulas in Project Web Access

The PSI CreateCustomFields and UpdateCustomFields methods allow you to create or edit formulas using the MD_PROP_FORMULA property in the CustomFieldDataSet.

Note
The CustomFieldDataSet must use a localization-independent string for a formula in an enterprise custom field. Project Professional converts custom field formulas to the localization-independent syntax.

To create a localization-independent formula using the PSI:

1. Create and save an enterprise custom field that includes a formula. Use one of the following methods:

   • In Project Web Access, create the custom field and formula and save it. Project Web Access opens Project Professional to validate the formula.

   • In Project Professional, create a local custom field and formula, using the Custom Fields dialog box. When the field is complete, click Add Field to Enterprise. Exit Project Professional, and then in Project Web Access ensure that the enterprise custom field is present and contains the formula.

2. Create a utility application to read the custom field with the converted formula. You can use any one of the following PSI CustomField methods to get a CustomFieldDataSet that contains the formula: ReadCustomFields, ReadCustomFieldsByEntity, or ReadCustomFieldsByMdPropUids.

3. Examine the CustomFieldDataSet in the Microsoft Visual Studio 2005 debugger, or save the formula string value you need to a file.

4. Paste the converted formula string into the code that sets the MD_PROP_FORMULA property for the CustomFieldDataSet in your new application. Set the other properties as necessary.

5. Use the CreateCustomFields or UpdateCustomFields method with the new CustomFieldDataSet.

Custom Fields and OLAP Cubes

The OLAP database for Project Server 2007 includes 14 default cubes, not just one default cube as in earlier versions. For information about the default cubes, see Cube Build Service.

You can easily add enterprise custom fields to the Project Server OLAP cubes by using the Cube Configuration page in Project Web Access (Figure 8). Unlike earlier versions of Project Server, you can modify the OLAP cube for new data such as custom fields without any custom development effort. On the Server Settings page, click Configuration in the Cube section.

Figure 8. Adding custom fields to OLAP cubes

Through the Cube Configuration page, you can add calculated members to any of the non-timephased cubes. You can see a calculated member as a measure in Project Server data in Microsoft SQL Server Analysis Services.

Note
Only the non-timephased cubes can contain calculated members.

The calculated member expression must use the Analysis Services MDX format. If the MDX script has an error, the cube does not build. For example, you can create a calculated member with the custom field name [Work Variance], where the MDX expression is [Work] - [Actual Work].

Calculated members can be very useful, for example, where an organization has its own definition of resource availability that differs from the default Project Server definition. The organization can define resource availability for internal reports that use any of the Project Server non-timephased cubes. You can also rename a field by using a calculated measure or create custom definitions for fields, such as resource capacity. For more information, see How to: Calculate Resource Availability in OLAP Cubes.

You can use custom fields as dimensions or measures. For example, you can use a Number custom field as a measure to sum values along a task dimension. The Project Server Cube Build Service (CBS) has many rules for including custom fields in OLAP cubes. Figures 9 and 10 summarize CBS features and the limitations for adding custom fields to the OLAP cube. The CBS rules include the following:

• Custom fields, in general:

  • Cannot be added as measures for the timephased cubes.

  • Cannot be added with formulas.

• Dimensions:

  • Support only custom fields with lookup tables.

  • Support Flag custom fields, which cannot have a lookup table.

• Task custom fields:

  • If the task custom field has a lookup table, it cannot roll up to summary tasks and cannot be used as a measure.

• Measures:

  • Support only Cost, Duration, and Number custom fields. Those custom fields for Resource and Project entities, either with or without a lookup table, can be measures.

  • If a Cost, Duration, or Number custom field for a Task entity has a lookup table, it cannot be a measure.

    If a Task custom field has a Sum rollup for task summary rows, it cannot be a measure.

  • Fields that have an Assignment roll-down and attributes set to None can be dimensions or measures.

• Multivalue fields:

  • Cannot be included in the cubes because of a limitation in Analysis Services 2000.

• Summary tasks:

  • The CBS does sum values of lookup tables for the summary tasks. However, if the lookup table does not include a value, Project Professional shows #Error for summary task rows.

Figure 9 summarizes the CBS rules for adding custom fields as dimensions to the OLAP cubes. In the custom field attributes, None means the custom field has no lookup table. For example, the only type of custom field that can be a cube dimension is a Flag custom field (of either a Project, Task, or Resource entity).

The Roll up section for Task Summary Rows applies only to Task custom fields; in that case, None means that the Task custom field is not rolled up to task summary rows. For example, a Duration custom field for a Task entity that has no roll up can be a cube dimension. However, if the Task Duration custom field has any of the available roll up parameters, it cannot be added as a cube dimension.

Figure 9. Cube Build Service support for custom fields as dimensions

On the Cube Configuration page, the Custom Field Entity drop-down lists for cube dimensions and measures include Project, Resource, Task, and Assignment. If a custom field satisfies all of the CBS rules for a dimension or measure, it shows in the Available fields list. If none of the custom fields satisfy the CBS rules, then the Available fields list (Figure 8) is empty.

Note
Figures 9 and 10 do not show which custom field entities apply to which cubes. For example, a Cost custom field for the Resource entity can be used only in Resource cubes. The Cube Configuration page in Project Web Access shows the Resource custom fields only when you select a Resource cube.

Figure 10 summarizes the rules for adding custom fields as measures to the OLAP cubes. To check some of the CBS rules, create lookup tables and custom fields by using the Custom Fields and Lookup Tables page in Project Web Access. For example, first create a lookup table named TaskDur, and then create the three Task custom fields, as shown in the following table.

Custom field name	Entity	Type	Lookup table	Roll up	Roll down
TaskDurLUT	Task	Duration	TaskDur	N/A	Yes
TaskDurNLNR	Task	Duration	None	None	Yes
TaskDurNLR	Task	Duration	None	Sum	Yes

On the Cube Configuration page, select the different cubes to see which of the custom fields in the previous table are available for dimensions and measures. If the custom field is listed, that qualifies as a YES in Figures 9 and 10. The following table shows where you can use the example custom fields.

Dimension or measure	Cube	Available fields
Dimension	Task Assignment	TaskDurLUT_Task TaskDurLUT_T_Assignment TaskDurLUT_Task
Measure	Task Assignment	TaskDurNLR TaskDurLUT_T TaskDurNLNR_T TaskDurNLR_T

Note
Project Web Access modifies custom field names to distinguish among Assignment, Task, and Resource custom fields in the Available fields list.

Project Web Access appends the entity name or abbreviation to a custom field name where necessary to help select the correct custom field. For example, if you create a Resource Cost custom field named Department Tax that has a rolldown for assignments, the list of available fields for the Assignment cube dimension shows both Department Tax_R_Assignment and Department Tax_Resource. The list of available fields for the Assignment cube measures shows Department Tax_R.

Figure 10. Cube Build Service support for custom fields as measures

Custom Fields and the Reporting Database

The Reporting database (RDB) includes tables and views that are designed for easy queries to make reports of Project Server data. Data in the RDB is updated in near real-time (within a few minutes) when there are changes in the Published database.

The Published database contains all of the custom field and lookup table data, but extracting custom field and lookup table data from the Published database requires complex JOIN statements or time-consuming development of PSI applications. The RDB provides column pool tables that aggregate custom field data for projects, resources, tasks, and assignments. The column pool table names all start with MSP_EpmCP.

In a new installation of Project Server, there are no published projects and only five enterprise custom fields—all of type Text: Cost Type, RBS, and Team Name for the Resource entity; Health for the Task entity; and State for the Project entity. Furthermore, the Cost Type, RBS, and State lookup tables are empty; they contain no text values. The RDB in a new installation includes only the MSP_EpmCPPrjUid0, MSP_EpmCPResUid0, and MSP_EpmCPTaskUid0 column pool tables. As you add enterprise custom fields with lookup table values and publish projects that use the custom fields, Project Server creates additional column pool tables as needed. Text lookup table values are stored in string column pool tables, Number lookup table values are stored in numeric column pool tables, and so forth.

For example, in a sample database that contains text values in the lookup tables, the RDB adds the MSP_EpmCPPrjStr0 table which shows the values for up to 15 project custom fields in all of the published projects. If there are more than 15 project custom fields, the RDB adds MSP_EpmCPPrjStr1; for more than 30 project custom fields, the RDB adds MSP_EpmCPPrjStr2; and so forth. Following is part of a sample MSP_EpmCPPrjStr0 table.

EntityUID	CFVal0	CFVal1	CFVal2	CFVal3 …
AF129A8C-DCB5-4FB0- 9E30-406458614A31	Under budget	On schedule	15	NULL
4D607B14-E40C-4549- 8E92-45A3A96D6892	No baseline	No baseline	NULL	NULL
8496EA23-4B25-4DBE- B68A-755A27246842	Overbudget	On schedule	15	NULL
8DFAD3DE-5CEC-4238- 932D-78D0F1BA12C8	Under budget	Late by more than 5 days	10	NULL
DCCD00EB-3301-4155- 9A71-9BE4B67F60AD	Overbudget	Late by more than 5 days	10	NULL
F8C71F4A-FD26-4EB8- A199-A3E737DBEC3C	Overbudget by 20% or more	Late by more than 5 days	NULL	NULL

The EntityUID column contains the project GUIDs; for example, the first item in the previous table is for the Windows Deployment_Published project from the Sample database that was migrated from Project Server 2003 to Project Server 2007. You can use the following sample query to get the project name and status date from the MSP_EpmProject table along with the custom field data in the RDB.

SELECT MSP_EpmProject.ProjectName, MSP_EpmProject.ProjectStatusDate, 
       MSP_EpmCPPrjStr0.CFVal0, MSP_EpmCPPrjStr0.CFVal1, MSP_EpmCPPrjStr0.CFVal2
FROM MSP_EpmCPPrjStr0 INNER JOIN 
       MSP_EpmProject ON 
           MSP_EpmCPPrjStr0.EntityUID = MSP_EpmProject.ProjectUID

For more information about the RDB, see Using the Reporting Database. For a reference of the RDB schema, see pj12ReportingDB.chm in the SDK download.

Localization of Lookup Tables

Lookup tables can be localized through the PSI. Neither Project Web Access nor Project Professional includes a way to localize lookup tables. Project Web Access always shows lookup table values using the highest preference Internet Explorer language available. If no lookup table language is found that matches an Internet Explorer language, then Project Web Access uses the primary lookup table language.

For example, if a lookup table contains multilanguage values in Spanish and English and you set the language preference in Internet Explorer in order of French, Spanish, and English, then Project Web Access shows the lookup table in Spanish. To set the Internet Explorer language preference and order, click Internet Options on the Tools menu, and then click Languages on the General tab to see the Language Preference dialog box.

Note
For multilanguage custom fields to work in Project Web Access, you must install both the Microsoft Windows SharePoint Services and the Project 2007 language packs for the languages you want on the Project Web Access server. In Project 2007, you can use the Microsoft Office 2007 Language Options utility to set the display language.

In addition to checking in, checking out, and deleting lookup tables, the PSI LookupTable class includes four methods to read, create, or update lookup tables: CreateLookupTables, ReadLookupTables, ReadLookupTablesByUids, and UpdateLookupTables. Each method uses or returns a LookupTableDataSet and also has a multilanguage equivalent, as follows:

• CreateLookupTablesMultiLang

• ReadLookupTablesMultiLang

• UpdateLookupTablesMultiLang

• ReadLookupTablesMultiLangByUids

The multilanguage methods use or return a LookupTableMultiLangDataSet. Rows in the LookupTableLanguagesDataTable include the LCID (locale identifier) property that specifies the language. For a list of LCIDs, see Locale ID (LCID) Chart in the MSDN Library. For example, the value 0x409 in the table is 1033 decimal for English (US).

The ProjTool test application in the Project 2007 SDK Samples download includes code that creates and manages multilanguage lookup tables for a test installation of Project Server. For more information, see Using the ProjTool Test Application.

See Also

Tasks

How to: Calculate Resource Availability in OLAP Cubes

Walkthrough: Creating a Hierarchical Lookup Table

How to: Create an Enterprise Custom Field

Concepts

Using VBA with Local Custom Fields and Outline Codes

Resource Management Data

Using the ProjTool Test Application

Other Resources

Locale ID (LCID) Chart

Project functions

Guide to expression syntax

Functions (arranged by category)

Cube Build Service

Using the Reporting Database