Introduction

In this article, we will explain 

How to query the enterprise custom project fields that allows multiple lookup table values from Project Server 2016 Database?


Scenario

We have a custom view in Project Center that show an enterprise custom project fields with multiple lookup table values that called "Departmentsas shown below:

Our goal is to query the same data from Project database as shown below:



The challenge here is to query the enterprise custom field  (in our case, it's Department custom field) that allows multiple values to be selected from a lookup table and show it in one row like (HR,IT).


Solution

Before we getting started, you should be aware of the following:
  • The Project Information and the enterprise custom project fields values are stored in "dbo.MSP_EpmProject_UserView" view.
  • The enterprise custom field that allows multiple lookup table values is not stored in "dbo.MSP_EpmProject_UserView" view.
  • Each enterprise custom field that read from a lookup table and allows multiple values has its own association view with this syntax "[MSPCFPRJ_FieldName_AssociationView]".
  • The related lookup data for this field is stored in "MSP_EpmLookupTable" table.


To query a 
multiple value lookup table custom field, you should do the following:
  • Query the 
    • [MSP_EpmProject_UserView] : specify the project fields that you need to show. in our case we will show the following fields:
      • ProjectUID (Mandatory).
      • ProjectName.
      • ProjectStartDate.
      • ProjectFinishDate.
    • [MSPCFPRJ_Departments_AssociationView] : it's the association view of the multiple value lookup table custom field. (in our case it's "Departments").
    • [MSP_EpmLookupTable] : this table holds the related lookup table values of the custom field  (in our case it's "Departments").
The query should be as the following:

SELECT
  proj.ProjectUID,
  proj.ProjectName,
  proj.ProjectStartDate,
  Proj.ProjectFinishDate,
  CONVERT(nvarchar, Lookup_Table.MemberFullValue) AS 'Departments'
FROM dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN [dbo].[MSPCFPRJ_Departments_AssociationView] AS Lookup_Association
  ON proj.ProjectUID = Lookup_Association.EntityUID
LEFT OUTER JOIN dbo.MSP_EpmLookupTable AS Lookup_Table
  ON Lookup_Association.LookupMemberUID = Lookup_Table.MemberUID

Output

  • Now, specifies a temporary named result set (CTE) for the above query with an appropriate name (MultipleValues_LookupField)
WITH MultipleValues_LookupField
AS (SELECT
  proj.ProjectUID,
  proj.ProjectName,
  proj.ProjectStartDate,
  Proj.ProjectFinishDate,
  CONVERT(nvarchar, Lookup_Table.MemberFullValue) AS 'Departments'
FROM dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN [dbo].[MSPCFPRJ_Departments_AssociationView] AS Lookup_Association
  ON proj.ProjectUID = Lookup_Association.EntityUID
LEFT OUTER JOIN dbo.MSP_EpmLookupTable AS Lookup_Table
  ON Lookup_Association.LookupMemberUID = Lookup_Table.MemberUID)

Read more about WITH common_table_expression (Transact-SQL).
  • Using STUFF, query the temporary result set "MultipleValues_LookupField" to get the multiple values in one row as (HR, IT).
SELECT
  STUFF((SELECT DISTINCT
    ', ' + MultipleValues_Field.Departments
  FROM MultipleValues_LookupField AS MultipleValues_Field
  WHERE Cusfields.[ProjectUID] = MultipleValues_Field.[ProjectUID]
  FOR xml PATH (''), TYPE)
  .value('.''NVARCHAR(MAX)'), 1, 2, '') [Departments]
FROM MultipleValues_LookupField Cusfields

Output
Read more about STUFF (Transact-SQL)
  • Finally, customize your query with your required fields as you prefer.
The final query should be as the following:

WITH MultipleValues_LookupField
AS (SELECT
  proj.ProjectUID,
  proj.ProjectName,
  proj.ProjectStartDate,
  Proj.ProjectFinishDate,
  CONVERT(nvarchar, Lookup_Table.MemberFullValue) AS 'Departments'
FROM dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN [dbo].[MSPCFPRJ_Departments_AssociationView] AS Lookup_Association
  ON proj.ProjectUID = Lookup_Association.EntityUID
LEFT OUTER JOIN dbo.MSP_EpmLookupTable AS Lookup_Table
  ON Lookup_Association.LookupMemberUID = Lookup_Table.MemberUID)
 
 
SELECT DISTINCT
  Cusfields.[ProjectName],
  Cusfields.ProjectStartDate,
  Cusfields.ProjectFinishDate,
  STUFF((SELECT DISTINCT
    ', ' + MultipleValues_Field.Departments
  FROM MultipleValues_LookupField AS MultipleValues_Field
  WHERE Cusfields.[ProjectUID] = MultipleValues_Field.[ProjectUID]
  FOR xml PATH (''), TYPE)
  .value('.''NVARCHAR(MAX)'), 1, 2, '') [Departments]
FROM MultipleValues_LookupField Cusfields
ORDER BY ProjectName

Output


That is the same result of the Project Center view 

Applies To

  • Project Server 2016 Content Database.
  • Project Server 2013 ProjectWebApp Database.

Conclusion

In this article, we have explained 
  • How to query the enterprise custom project fields with multiple lookup table values from the database In Project Server 2016?
  • Why the custom fields that allow multiple lookup table values are missing in "dbo.MSP_EpmProject_UserView".
  • How the Project Server database stores the custom fields that allow multiple lookup table values?
  • How CTE and STUFF helped us to achieve our goal?

Reference 

See Also