Query Enterprise Custom Fields from Project Server Database

Project Server Enterprise Custom Fields

In this article, we will take an overview of the Project Server Database changes to know Where Project data stored in SQL Server to query Enterprise Custom Fields from Project Server database.

Applies To

  • Project Server 2019.
  • Project Server 2016.
  • Project Server 2013.
  • Project Server 2010.

PWA Data Structure in Project Server

Before we getting started to query the custom fields from Project Server Database, let’s first have a quick journey to briefly explore the PWA structure in Project Server.

What’s PWA Instance?

The Project Web App Instance is a SharePoint site collection with a PWA site template that requires an independent license to be able to enable the PWA features on it.

In PWA instance, we have

  • A project center that holds project info.
  • Each project consists of related tasks and each task can be assigned to one or more resources!
Project Center Totalize
  • Each project has its project site as a sub site under the PWA instance.
  • This project site by default has risk and issue lists and document library to upload documents and files for its related project.
Project Site in Project Server

Therefore, we can initially categorize the data stored in Project Server to main two categories:

  1. PWA info
    1. Projects.
    2. Tasks.
    3. Resources.
    4. Configuration settings.
    5. Other data.
  2. Project Sites.
    1. Lists.
    2. Document Library.
    3. Custom Pages.

You might also like to read Install and Configure Project Server 2016 step by step.

In this article, we will mainly concentrate on how to query enterprise custom fields from Project Server Database in Project Server versions, so please, let us first explore what’s the custom fields and how to manage and create it in Project Server.

Custom Fields in Project Server

In Project server as well as Project online, besides the default fields like Project Name and Start, End ..etc, you can easily manage Enterprise Custom Fields and Lookup Tables to match your business requirements in a flexible manner.

Enterprise Custom Fields

In PWA Settings, below “Enterprise Data“, you have an option to manage, create, and view a summary of the custom fields and lookup tables defined for Project Web App.

Enterprise Custom Fields and Lookup Tables in Project Server

From this page, you can also drill in to create a new custom field or lookup table, or modify the definition of an existing custom field or lookup table.

Enterprise Custom Fields and Lookup Tables

You might also like to read Export Enterprise Custom Fields Details in Project Server

Create a new custom field in Project Server

You can easily click on “New field” to create a new custom field or on “Copy field” to create a new custom field from an existing custom field.

When you create a new field, you should specify the following:

  • Field Name is mandatory and it must be unique and not used before.
  • Field Description is optional to describe your field name in more detail.
  • Entity: select which entity the new custom field will belong to.
    • Project.
    • Task.
    • Resource.
Enitiy and type in Project Server
  • Type: select the data type of the new custom field.
Custom field Data types in Project Server
  • Custom Attributes: Choose whether the field has
    • Single line of text,
    • Multiple lines of text,
    • Lookup table, or
    • Calculated formula.
Custom field attributes in Project Server

Note: Fields with multiple lines of text will not be available in the Project client.

  • Department: is a default lookup table field that you can fill it with your own values to classify Each Custom Field can belong to a specific Department.
Departments Lookup Table custom field

Note: If a Department is not selected then the Custom Field will be assumed to be available across the server.

Values to Display: Choose whether you want just the data or graphical indicators to be displayed.

Graphical Indicators in Custom Field

Note: Graphical indicators are not displayed in all areas of Project Web App.

Behavior: These settings affect the behavior of this custom field. If the behavior is controlled by workflow, this custom field can be made required or read-only at the workflow stage level.

Custom field behavior in Project Server

Lookup Tables

As you can see, when you create a new custom filed one of the attributes options is “Lookup Table” to create a field from a predefined list.

Allow multiple values to be selected from lookup table in Project Server 2019

The predefined list can be configured by creating a new lookup table.

Lookup tables for custom fields

Again, to create a new Lookup table, you should specify its unique name and type. Also, you should Specify the “Code mask” for the lookup table. The code mask defines for each level the number and sequence of characters that are allowed.

  • For Length, you can type Any or a number from 1 through 255.
  • For the separator, type from one through three characters.
Code Mask in Lookup table

Read also, One or more code values in the lookup table either do not match the mask defined for the code or contain more than 255 characters

Moreover, you can specify the lookup tables values and build its hierarchy and control the order items as you prefer.

Edit Lookup table in Project Server

Where Project Data stored in SQL Server?

Now, Let’s go back to our main question that is Where Project Server data is stored in SQL Server?

As we earlier mentioned, we have categorized the Project Server data into two main categories Project Info and Project Sites.

Of course, these data stored in specific databases hosted on SQL Server. but each Project Server version has its own structure to save it. So, in the next section, we will take an overview of the database changes for each Project Server version from Project Server 2010 to Project Server 2019.

You might also like to read What’s new and deprecated in Project Server 2016?

Project Server Databases

Actually, there are continuous enhancements for the Project Server databases to facilitate task operations, such as backup and restore, migration, etc. however, many tables and views still do the same job in all Project Server versions with some changes like the database name and schema …etc.

In this section, we will take a quick overview of the database structure changes. Also, we will show how to retrieve all custom field information details. Moreover, we will learn how to query Custom Fields from Project Server database.

You might also like to read PWA Content Database Consideration In Project Server.

Project Server 2010 Databases

In Project Server 2010, the data is partitioned into five databases:

  • Four databases for the PWA data, and
  • One content database that holds the PWA Site Collection and its project sites content.

PWA info is stored in the following 4 databases:

  • Archive Database.
    • The archive database is used to store online backups of data from the rest of the site.
  • Draft Database.
    • The Draft database contains tables for editing unpublished projects from Microsoft Project Professional 2010 and the Project Server Interface Web services. Project data in the Draft database cannot be accessed by using Microsoft Project Web App. This database also hosts the tables that are used by the Project Queue.
  • Published Database.
    • The Published database contains all of the published projects. Published projects are visible in Project Web App. The Published database also contains tables that are specific to Project Web App (timesheets, models, views, and so on), and global data tables (which contain resources, custom fields, security definitions, and other metadata). This database also hosts the tables that are used by the Timesheet Queue.
  • Reporting Database.
    • The Reporting database contains the same data as the Published database but optimized for generating reports and online analytical processing (OLAP) cubes. Data in the Reporting database is updated almost in real-time, is comprehensive, and is optimized for read-only report generation.

Project Site is stored in a content database:

  • Content Database.
    • The Content database contains project site content such as custom pages, workflows, management settings, documents, and lists of issues, risks, …etc

Query Custom Fields from Project Server Database in Project Server 2010

In Project Server 2010, you should use the “Reporting” database to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:

EPM Project User View

In”MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.

SELECT * FROM [MSP_EpmProject_UserView]

EPM Task User View

In”MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.

SELECT * FROM [MSP_EpmTask_UserView]

EPM Resource User View

In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.

SELECT * FROM [MSP_EpmResource_UserView]

Project Server 2013 Database

In Project Server 2013, the 4 Project databases (Draft, Archive, Publishing, and Reporting) have been consolidated to one ProjectWebApp Database, plus one content database that holds the PWA Site Collection and its project sites content.

Project Server 2013 Databases

Query Custom Fields from Project Server Database in Project Server 2013

In Project Server 2013, you should use the “ProjectWebApp” database to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:

EPM Project User View

In”MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.

SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmProject_UserView]

EPM Task User View

In”MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.

SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmTask_UserView]

EPM Resource User View

In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.

SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmResource_UserView]

Project Server 2016 Database

In Project Server 2016, the Project PWA database and the content database have been consolidated to a single SharePoint Content Database that holds the PWA Site Collection with its project site content as well as project data.

Project Server 2016 Databases

Query Custom Fields from Project Server Database in Project Server 2016

In Project Server 2016, you should use the “pjrep.” schema to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:

EPM Project User View

In”pjrep.MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.

SELECT * FROM pjrep.MSP_EpmProject_UserView

EPM Task User View

In”pjrep.MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.

SELECT * FROM pjrep.MSP_EpmTask_UserView

EPM Resource User View

In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.

SELECT * FROM pjrep.MSP_EpmResource_UserView

Project Server 2019 Database

In Project Server 2019, it has the same structure and schema as Project Server 2016.

Note: in all Project Server versions, the Enterprise custom fields with a lookup table single value will be listed in “MSP_EpmProject_UserView“. but the fields with a lookup table multiple values enabled will not be listed directly in “MSP_EpmProject_UserView”. it will be stored in an association table as mentioned at Query Multiple Values Lookup Table Field in Project Server.


Conclusion

In conclusion, we have explored the PWA data structure to know Where Project data stored in SQL Server to be able to query Enterprise Custom Fields from Project Server database.

See Also
Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.

Subscribe to Blog


Leave a Reply