Export Enterprise Custom Fields to Excel file in Project Server 2016

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.

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.

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:

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

Enterprise Custom fields Data Types

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

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 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

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

---

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

---

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

• Install and Configure Project Server 2016.
• New and deprecated in SharePoint Server 2016?
• Query Multiple Values Lookup Table Field in Project Server

Have a Question?

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