Export Enterprise Custom Fields to Excel file in Project Server 2016

Get All Enterprise Custom Fields Information

In this post, we’ll explain how to export Enterprise Custom Fields to Excel file in Project Server 2016 by querying the Enterprise Custom Fields and Lookup tables details from Project Server 2016 Database.

Query Enterprise Custom Fields with data type in Project Server 2013

You may also like to read Query Enterprise Custom Fields from Project Server Database


Get All Custom Fields Information from Project Server Database

As we know, in Project Server, you can easily create new custom fields with a specific data type per each entity (Project, Task, and Resource) to can customize your EPM system based on your requirements.

Export Enterprise Custom Fields to Excel file from Project Server 2016 Database

In some rare scenarios, you may be asked to export Enterprise Custom Fields to Excel file, but as you can see, there is no export option on the Enterprise Custom Fields page as shown below:

Enterprise Custom Fields and Lookup Tables

So that, In this post, we will learn how we can retrieve the list of all custom fields information from Project Server database.

Mfn_epm_getallcustomfieldsinformation function In Project Server Database

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

In this database, there is a predefined function called “[Mfn_epm_getallcustomfieldsinformation]” that used to retrieve the Enterprise Custom fields information like FieldName, EntityName, DataType ..etc.

So before we getting started to use this function, you should first be aware of the following:

  • You can create multiple PWA instance sites in the same content database, therefore, you should filter GetAllCustomFieldsInformation by SiteID as a parameter to scope the retrieved fields for a specific PWA Instance.
  • Also, to use  GetAllCustomFieldsInformation, you should use the [pjrep] schema as [pjrep].GetAllCustomFieldsInformation.

You might also like to read New and deprecated in Project Server 2016?

Get Project Instances Site ID List in Project Server Database

You can get a Site ID list form the [Sites] table in the same Project Server Database.

Select
Id FROM
[dbo].[Sites]

Get Enterprise Custom fields data based on the site ID

SELECT * 
FROM   [pjrep].[Mfn_epm_getallcustomfieldsinformation] ('SiteID') 
-- Site ID ex: 59E51D34-8597-4348-8589-F4D37E1EB85E 

Output

Query the Enterprise Custom Fields In Project Server 2016 Database

Enterprise Custom fields Data Types

As you can see, the above query returns DataType as Integer ID!

Enterprise Custom fields Data Types

The below table list the corresponding Custom Enterprise Field based the Data type ID:

Data Type ID Data Type
 Cost  9
 Date  4
 Duration  6
 Flag  17
 Number  15
 Text  21

Retrieve Custom Enterprise Fields with the Data type name

The below query will get all Custom Enterprise Fields information with the corresponding Data Type name.

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 'Data Type', 
       CreatedDate, 
       ModificationDate 
FROM   pjrep.Mfn_epm_getallcustomfieldsinformation('003D8E9F-2EA1-4793-886A-B470A1131AC7'

Output

Get Enterprise Custom Fields details in Project Server 2016 Database

Get the Enterprise Custom Fields In Project Server 2013 Database

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

In Project Server 2013, there are two Databases.

  • SharePoint Content database.
  • Project Web App Database to store the project data.

So to be able to use “MFN_Epm_GetAllCustomFieldsInformation()“, you should use the “ProjectWebApp” database as shown below:

Use [ProjectWebApp]
Select
* FROM
MFN_Epm_GetAllCustomFieldsInformation()

Output

Query Enterprise Custom Fields in Project Server 2013

Again, to show the data type name for each custom field, you should use the below query.

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

Query Enterprise Custom Fields with data type in Project Server 2013

Export Enterprise Custom Fields to Excel file in Project Server 2016

Once you executed your query, you can easily save the query result to CSV file as shown below

Export Enterprise Custom Fields to Excel file
Export Enterprise Custom Fields to Excel file

Conclusion

In this article, we have learned how to query all Enterprise Custom Fields information from Project Server database.

Applies To
  • Project Server 2019 Database.
  • Project Server 2016 / 2013 Database.
Download

Download Get All Custom Fields Information on Github.

You may also like to read
Have a Question?

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

4 thoughts on “Export Enterprise Custom Fields to Excel file in Project Server 2016”

  1. Pingback: Project Server 2019: Query Task Settings and Display | SPGeeks

  2. Pingback: Project Server 2019: Query Multiple Values Lookup Field | SPGeeks

  3. Pingback: Can't find multiple value lookup table custom fields in EpmProject Userview | SPGeeks

  4. Нi, Mohamed.
    Fantastic “Poject Server 2016: Ꮐet The Enterprise Custom Fields ” іs truⅼy an іnteresting subject
    Merely wɑnted tο say thɑt I likeⅾ yoսr blog.

Leave a Reply