Custom Fields and the Reporting Database

Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010

In this article
Simple Queries with Custom Fields
Organization of Data in the Column Pool Tables
Finding Custom Field Data in the RDB
Changing Custom Fields in Timesheets
Localization of Lookup Tables

The Reporting database (RDB) in Microsoft Project Server 2010 includes tables and views that are designed to help make queries for reports of Project Server data easier than querying the Published database. The RDB aggregates custom field data in special column pool tables. Data in the RDB is updated in near real-time (within a few minutes) when there are changes in the Published database.

This article includes the following sections:

• Simple Queries with Custom Fields

• Organization of Data in the Column Pool Tables

• Finding Custom Field Data in the RDB

• Changing Custom Fields in Timesheets

• Localization of Lookup Tables

For a reference of the RDB schema, see pj14_ReportingDB.chm in the Project 2010 SDK download. For a link to the download, see Project 2010 SDK Documentation.

Simple Queries with Custom Fields

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 applications that use the Project Server Interface (PSI). The column pool tables in the RDB aggregate custom field data for projects, resources, tasks, assignments, and timesheets, thereby making reports that include custom fields easier to create.

The following user views include custom field data that the RDB extracts from the column pool tables. For example, MSP_EpmAssignment_UserView includes columns for the RBS custom field and the Cost Type custom field in a default installation of Project Server. The names of these custom fields in MSP_EpmAssignment_UserView are RBS_R and Cost Type_R, because they are resource custom fields. The field names for the same fields in MSP_EpmResource_UserView are RBS and Cost Type. MSP_TimesheetLine_UserViewCF includes the RBS custom field by default and other custom fields that are saved in custom timesheet views:

• MSP_EpmAssignment_UserView

• MSP_EpmProject_UserView

• MSP_EpmResource_UserView

• MSP_EpmTask_UserView

• MSP_TimesheetLine_UserViewCF

When you create a custom field and save data for the custom field, Project Server adds the custom fields to the relevant RDB tables. For example, if you create a task custom field named Task Info and save a project with data in the Task Info field, MSP_EpmTask_UserView includes the Task Info field. When you create a custom timesheet view that includes the Task Info custom field, and save a timesheet that uses the custom view, Project Server adds the Task Info_T_AT field to the MSP_TimesheetLine_UserViewCF view. Project Server creates the _AT suffix to maintain timesheet metadata independently from the core project, task, and resource metadata. For a resource custom field named Resource Info, MSP_EpmAssignment_UserView would add a field named Resource Info_R and MSP_TimesheetLine_UserViewCF would add a field named Resource Info_R_AR.

It is not necessary to use the column pool tables in simple queries for entities that have specified custom field values; in fact, you should avoid using JOIN statements with the column pool tables to query for entities that have custom field data. The RDB can reorganize column pool data during updates, and the user views maintain current data for custom fields. For example, create an enterprise project text custom field named Sample Field, add the value Test data to Sample Field in some projects, and then publish the projects. The following query finds all of the projects that have the Sample Field project custom field with the value "Test data."

SELECT ProjectUid, ProjectName
FROM MSP_EpmProject_UserView
WHERE [Sample Field] = N'Test data'

Organization of Data in the Column Pool Tables

In a new installation of Project Server, there are no published projects and approximately 20 default enterprise custom fields. Many of the new default custom fields in Project Server 2010 are for portfolio analysis, such as Sample Business Need and Sample Approved Finish Date. The Project Departments and Resource Departments custom fields remain empty until the Department lookup table contains text values. The default Cost Type, RBS, and State lookup tables are empty; they contain no text values.

Column pool table names all start with MSP_EpmCP. After you publish some projects that contain custom field data, the RDB contains the following column pool tables for the Project, Resource, Task, and Assignment entities:

• Project tables: MSP_EpmCPPrjStr0, MSP_EpmCPPrjUid0, and MSP_EpmCPPrjVar0

• Resource tables: MSP_EpmCPResStr0, MSP_EpmCPResUid0, and MSP_EpmCPResVar0

• Task tables: MSP_EpmCPTaskStr0, MSP_EpmCPTaskUid0, and MSP_EpmCPTaskVar0

• Assignment tables: MSP_EpmCPAssnStr0, MSP_EpmCPAssnUid0, and MSP_EpmCPAssnVar0

The column pool table names that contain Uid include GUIDs of the entities and GUIDs of the custom field values of the related lookup tables, which are stored in the MSP_EpmLookupTable table of the RDB. Column pool table names that contain Str include the entity GUIDs with values of the text custom fields. Column pool table names that contain Var include entity GUIDs with numeric values of other types of custom fields such as Flag, Number, Cost, Duration, Date, and so on. As you add enterprise custom fields and publish projects that use the custom fields, whether they have lookup tables or values entered manually, Project Server creates additional column pool tables as needed.

For example, in a sample database that contains text values in the lookup tables, the MSP_EpmCPPrjStr0 table shows the values for up to 50 project custom fields in all of the published projects. If there are more than 50 project custom fields, the RDB adds MSP_EpmCPPrjStr1; for more than 100 project custom fields, the RDB adds MSP_EpmCPPrjStr2; and so on. Table 1 shows part of a sample MSP_EpmCPPrjStr0 table.

Table 1. Part of a sample MSP_EpmCPPrjStr0 table

The EntityUID column contains the project GUIDs. You can use the following sample query to get the project name and status date from the MSP_EpmProject table, together 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

Finding Custom Field Data in the RDB

To find custom field data in the column pool tables, you can use the following query on the Reporting database in Microsoft SQL Server. The query uses the MFN_Epm_GetAllCustomFieldsInformation user defined function (UDF) in the Project Server 2010 RDB.

-- Change EntityType to Project, Resource, Task, Assignment, or Timesheet.
 
SELECT CustomFieldName, ColumnPoolTableName, ColumnPoolColumnName
FROM MFN_Epm_GetAllCustomFieldsInformation()
WHERE EntityName = N'EntityType'

For example, the following query shows all 17 columns for task custom fields, including the custom field data type, lookup table name, and modification date:

SELECT * FROM MFN_Epm_GetAllCustomFieldsInformation()
WHERE EntityName = N'Task'

Multivalue custom fields   The RDB includes an association table view for each multivalue custom field that contains values. View names for multivalue custom fields are of the form MSPCFxxx_Custom Field Name_AssociationView, where xxx can be PRJ, RES, or TASK, for a project, resource, or task custom field. For example, MSPCFPRJ_Project Departments_AssociationView contains multivalue associations for the Project Departments custom field. To determine whether a text custom field uses a lookup table and allows multiple values, open the custom field definition in the Enterprise Custom Fields and Lookup Tables page in Project Web App, and then examine the Custom Attributes section.

The OLAP databases for Project Server do not include support for multivalue custom fields. However, multivalue custom field data is accessible in the RDB, where you can write a query to show the values for a specific entity. For example, change the Project Departments custom field to allow multiple values, and then add some values in the Department lookup table. Create a project by using Project Professional. In the Project Information dialog box, select more than one value in the Department drop-down list, and then save and publish the project.

In Microsoft SQL Server Management Studio, create the following query for the Reporting database, and then run the query. The LookupTableUID value is the same in all Project Server 2010 installations.

SELECT proj.ProjectName, lt.MemberFullValue AS N'Department(s)'
FROM dbo.MSP_EpmProject_UserView AS proj
-- Use the association table view.
INNER JOIN [dbo].[MSPCFPRJ_Project Departments_AssociationView] AS depassoc
   ON proj.ProjectUID = depassoc.EntityUID
INNER JOIN dbo.MSP_EpmLookupTable AS lt
   ON depassoc.LookupMemberUID = lt.MemberUID
WHERE lt.LCID = 1033
AND lt.LookupTableUID = N'e7397277-1ab0-4096-b2dd-57029a055ba4' -- Department Lookup Table

Table 2 shows the result set for the TestProj project, where four values of the Project Departments custom field are selected. The Department lookup table in this example also contains two levels of values.

Table 2. Result set for the TestProj project

Changing Custom Fields in Timesheets

Timesheet views can include custom fields. When you create a timesheet by using a view that includes custom fields, and then save the timesheet, it provides a snapshot of the value of each custom field at the time of saving. If you later delete a custom field, and then recreate it with the same name, the RDB maintains a history of the values and types of the custom field for each saved timesheet. In effect, saved timesheets are Type 2 slowly changing dimensions (SCDs) because they contain groups (dimensions) of data that slowly change. For more information, see Slowly Changing Dimension.

You can get the history of changes when a custom field is recreated by using the MSP_TS_GetTimesheetCustomFieldsInformation stored procedure. The following procedure shows an example. The procedure assumes that weekly time reporting periods have been created on the Time Reporting Periods page (https://ServerName/ProjectServerName/_layouts/pwa/Admin/TimePeriod.aspx) in Project Web App.

To show the history of changes in a timesheet custom field

1. In Project Web App, go to the Enterprise Custom Fields and Lookup Tables page (https://ServerName/ProjectServerName/_layouts/pwa/Admin/CustomizeFields.aspx), and then create a task text custom field that uses a text lookup table. For example, create a lookup table of type Text named TestLUT_Text, add some values, and then create a custom field named Assignment Health for the Task entity of type Text.

2. Go to the Manage Views page (https://ServerName/ProjectServerName/_layouts/pwa/Admin/ViewsMain.aspx), and then create a timesheet view that includes the Assignment Health custom field. For example, name the view TS View with Assignment Health 1.

3. Create a project that has tasks that span more than one timesheet period, and then assign the tasks to you or to a team member who understands how to proceed with the test. To quickly complete the test, set the project dates in the past for time periods that have no timesheets already created for the person doing the test. Save and publish the project to Project Server. For example, name the project Test Assignment Health.

4. On the Timesheet page ribbon, click Select Period, and then set the timesheet period for the first week of the test project.

5. In the View drop-down list, select TS View with Assignment Health 1. Fill in some work time, and then, when you click an Assignment Health cell, select a value for each task from the TestLUT_Text list of values. Save the timesheet, and then click Send Status to get approval for the tasks. In Microsoft SQL Server Management Studio on the Project Server computer, the MSP_TimesheetLine_UserViewCF view in the RDB now includes the Assignment Health_T_AT field, which is of SQL Server data type nvarchar.

6. Go to the Enterprise Custom Fields and Lookup Tables page, and then delete the Assignment Health custom field. Create a lookup table of type Duration named TestLUT_Duration, add some values (such as 1d, 2d, 1w, 2w), and then create another custom field named Assignment Health for the Task entity of type Duration.

7. On the Manage Views page, create a timesheet view that includes the new Assignment Health custom field. For example, name the view TS View with Assignment Health 2.

   Note

   Because the previous Assignment Health field does not exist, the TS View with Assignment Health 1 view no longer works. You could also change the first view to use the new Assignment Health field.

8. On the Timesheet page ribbon, click Select Period, and then set the timesheet period for the second week of the Test Assignment Health project.

9. In the View drop-down list, select TS View with Assignment Health 2. Fill in some work time, and then, when you click an Assignment Health cell, select a value for each task from the TestLUT_Duration list of values (Figure 1). Save the timesheet, and then click Send Status to get approval for the tasks. In Microsoft SQL Server Management Studio on the Project Server computer, the MSP_TimesheetLine_UserViewCF view in the RDB still includes the Assignment Health_T_AT field, but the SQL Server data type is now decimal.

   Figure 1. Setting the duration value for the Assignment Health custom field

   

10. To get the history of changes for the Assignment Health custom field in saved timesheets, use the following query:

    -- Get the history of changes and current information for a 
    -- specified timesheet custom field. 
    DECLARE @RC              int
    DECLARE @CustomFieldName NAME
    
    -- TODO: Set the custom field name for the 
    -- MSP_TS_GetTimesheetCustomFieldsInformation parameter.
    SET @CustomFieldName = N'Assignment Health_T_AT'
    
    EXECUTE @RC = [ProjectServer_Reporting].dbo.MSP_TS_GetTimesheetCustomFieldsInformation 
       @CustomFieldName
    GO
    

Table 3 shows the query result set.

Table 3. Query result set

Project Server creates the MSP_TimesheetCPUid0 table in the RDB when the first timesheet that contains a custom field is saved. The custom field data type 21 is Text and data type 6 is Duration. For information about custom field types, see the CustomField.Type enumeration.

Additional columns in the result set include IsRollDown, PresentInUserView, CreatedDate, and ModificationDate. Even though custom fields can be deleted and recreated with the same name, timesheet data at the time of submission is not changed. Timesheet reports can accurately show historical data.

Localization of Lookup Tables

Lookup tables can be localized through the PSI. Project Web App and Project Professional do not include a way to localize lookup tables. Project Web App always shows lookup table values by using the highest preference Internet Explorer language available. If no lookup table language is found that matches an Internet Explorer language, Project Web App 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, English, Project Web App shows the lookup table in Spanish. To set the Internet Explorer language preference and order:

1. On the Tools menu, click Internet Options.

2. On the General tab, click Languages.

3. In the Language Preference dialog box, set language preference and order.

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 application 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 in Project Server 2010.

See Also

Concepts

Local and Enterprise Custom Fields

Creating Lookup Tables and Enterprise Custom Fields

Rollup and Roll-Down Custom Fields

Using Formulas and Graphical Indicators with Custom Fields

Custom Fields and OLAP Cubes

Using the ProjTool Test Application in Project Server 2010

Other Resources

Using the Reporting Database

Locale ID (LCID) Chart

Slowly Changing Dimension