Project Server 2016: Get The Enterprise Custom Fields

In this article, we will explain How to

Get the Enterprise Custom Fields In Project Server 2016 Database.


Scenario

In Project Server 2016/2013, In some cases, you may need to export the Enterprise Custom Fields to Excel.

but unfortunately, there is no export option in the Enterprise Custom Fields page as shown below.

Get the Enterprise Custom Fields From Project Server 2016 Database

In this case, one of the available workarounds is retrieving these data from the Project Server Database as the following:


Get the Enterprise Custom Fields In Project Server 2013 Database

In Project Server 2013, there are two Databases.

  • SharePoint Content database.
  • Project Web App Database.
    • The Project data is stored in the Project Web App Database.
    • The Project Web App Database has a predefined function called GetAllCustomFieldsInformation that used to retrieve the related Enterprise Custom fields data like FieldName, EntityName, DataType ..etc.
To retrieve the Enterprise Custom fields data from Project Server 2013 Database
Use [ProjectWebApp]
Select
* FROM
MFN_Epm_GetAllCustomFieldsInformation()
Project Server 2016: Get The Enterprise Custom Fields

Unfortunately, the above query returns DataType as Integer ID. be we need to show the corresponding data type as shown below.

Project Server 2016: Get The Enterprise Custom Fields

Below is the list of  the corresponding Custom Enterprise Field based on its Data type ID:
 Data Type ID Data Type
 Cost  9
 Date  4
 Duration  6
 Flag  17
 Number  15
 Text  21
To retrive the Custom Enterprise Fields information with the Data type name:
Use [ProjectWebApp-Demo]
SELECT
CustomFieldName,EntityName,
case
when
DataType = '9'
then
'Cost'
when
DataType = '4'
then
'Date'
when
DataType = '6'
then
'Duration'
when
DataType = '17'
then
'Flag'
when
DataType = '15'
then
'Number'
when
DataType = '21'
then
'Text'
end
as
'DataType',
CreatedDate,ModificationDate
FROM
MFN_Epm_GetAllCustomFieldsInformation()
Output
Project Server 2016: Get The Enterprise Custom Fields

Get the Enterprise Custom Fields In Project Server 2016 Database.

In Project Server 2016, there is no Project database, only single database (SharePoint Content Database) holds the project data and the content to facilitate the database
operations, such as backup and restore, migration, etc.

Although there’re a big changes in the database, we still can use GetAllCustomFieldsInformation to get the Custom Enterprise Fields list with a bit changes as mentioned below:

  • You can create a multiple PWA instance site in the same content database, so you should filter GetAllCustomFieldsInformation by SiteID as a parameter to scope the retrieved fields for a specific PWA Instance.
  • To use  GetAllCustomFieldsInformationyou should use the [pjrep] schema as [pjrep].GetAllCustomFieldsInformation.
To get the Site ID
Select
Id FROM
[dbo].[Sites]
To retrive the Enterprise Custom fields data based on the site ID
SELECT
* FROM
[pjrep].[MFN_Epm_GetAllCustomFieldsInformation] ('SiteID')
-- Site ID ex: 59E51D34-8597-4348-8589-F4D37E1EB85E

Applies To
  • Project Server 2013 Database.
  • Project Server 2016 Database.
Conclusion

In this article, we have learned

  • How to get the Enterprise Custom Fields In Project Server 2013 via T-SQL.
  • How to get the Enterprise Custom Fields In Project Server 2016 via T-SQL.
  • How to get the Enterprise Custom Fields Datatype based on the TypeID via T-SQL.
See Also

Enterprise custom fields and lookup tables in Project Web App

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s