Query Multiple Values Lookup Table Field Project Server 2019

In this post, we’re gonna learn how to query multiple values lookup table field from Project Server 2019 database.

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

---

Query Multiple Values Lookup Table Field Project Server 2019

---

Missing Multiple Value Lookup Fields in MSP_EpmProject_Userview

In Project Server 2019, specifically, in the Project Center page, we have a custom view that shows an enterprise custom project field with multiple lookup table values called “Departments” field as shown below:

In this view, the”Department” field reads from a lookup table enterprise custom field with multiple values to be selected from this lookup table.

Query Project Info and Enterprise Custom Fields

As we know, for reporting purposes, most of the project info and custom fields can be queried from “pjrep.MSP_EpmProject_UserView“ view.

select * from pjrep.MSP_EpmProject_UserView

Unfortunately, when we tried to query the lookup table custom fields from “pjrep.MSP_EpmProject_UserView” view, we didn’t find the multiple value lookup table custom fields!

To get the related database for PWA Instance, Please check Get PWA Instance details via SharePoint Management Shell in Project Server.

---

Query multiple values lookup fields from Project Server Database

In this section, we’ll show how to query the enterprise custom project fields that allow multiple lookup table values as shown below:

Query Lookup Field Association View

You should be aware of each enterprise custom field that reads from a lookup table and allows multiple values has its own association view with the below syntax:

[MSPCFPRJ_FieldName_AssociationView]

Note: the “_FieldName_” in “[MSPCFPRJ_FieldName_AssociationView]” should b replaced with the correct field name.

Query Lookup Table Field Values

Each Lookup Table has its own values that can be added by editing the lookup table field in PWA Settings as shown below:

To get these related values for each Lookup Table field, you can query the “MSP_EpmLookupTable” In Project Server Database.

select * from pjrep.[MSP_EpmLookupTable]

Query Project Info with Lookup Field Values

To query the project info with the lookup table field from the Project Server database, we will need to join the following three objects.

[MSP_EpmProject_UserView]

It’s the main reporting project view that holds the project built-in fields and other Enterprise Custom Fields.

In our scenario, we will perform a query on “[MSP_EpmProject_UserView]” to show the following fields:

• ProjectUID (Mandatory).
• ProjectName.
• ProjectStartDate.
• ProjectFinishDate.

[MSPCFPRJ_FieldName_AssociationView]

It’s the association view for the multiple values lookup table custom field in the Project Server database.

In our case, we have a “Departments” field, so the association view should be “MSPCFPRJ_Departments_AssociationView“.

Don’t forget to update the FieldName in “MSPCFPRJ_FieldName_AssociationView” as per your enterprise custom field name.

[MSP_EpmLookupTable]

This table holds all values related to each lookup table custom field in Project Server.

Get Multiple values lookup fields from Project Server Database

In this section, we’re gonna perform a join operation on the above tables to get the related value for the “Departments” lookup field per each project in Project Server database.

T-SQL Query (1)

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

Output

As you can see in the above output, the project name is repeated per each related value in the lookup field “Departments”.

Using WITH and STUFF to show multiple values in one row

In this section, we will use WITH and STUFF SQL statements to show the related multiple values for the lookup table field in one row for each project as shown below:

Using WITH Statement

Using WITH statement, we can create a Temporary Result Set based on a specific query.

In our example, we will create a temporary result set based on the previous query (1) as the following:

T-SQL Query (2)

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

Using STUFF Statement

In this step, we’ll use the STUFF statement to show the related value (HR, IT) in one row, instead of multiple rows per each value.

T-SQL Query (3)

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

T_SQL: Query Multiple Values Lookup Table Custom Fields in Project Server

By combining Query (2) and Query (3), the final query should be like the following:

T-SQL Final Query

WITH MultipleValues_LookupField
AS (SELECT
  proj.ProjectUID,
  proj.ProjectName,
  proj.ProjectStartDate,
  Proj.ProjectFinishDate,
  CONVERT(nvarchar, Lookup_Table.MemberFullValue) AS 'Departments'
FROM pjrep.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN [pjrep].[MSPCFPRJ_Departments_AssociationView] AS Lookup_Association
  ON proj.ProjectUID = Lookup_Association.EntityUID
LEFT OUTER JOIN pjrep.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

Download the final query and check other Project Server Database Queries on Github.

---

Conclusion

In conclusion, we have learned how to

• Query multiple values lookup table field Project Server 2019 database.
• Why the custom fields that allow multiple lookup table values are missing in “pjrep.MSP_EpmProject_UserView“.
• Where’re Lookup table custom fields stored in Project Server Database.

Applies To

• Project Server 2019 Database.
• Project Server 2016 Database.

Note: for Project Server 2013, you just need to chanage the pjrep schema to dbo schema for all views. EX: “dbo.MSP_EpmProject_UserView”

Download

Download the final query and check other Project Server Database Queries on Github.

You may also like to read

• Install and Configure Project Server 2016.
• Migrate to Project Server 2016 Step By Step.
• Project Server 2013 Migration from Staging to Production Farm.
• Project Server 2016: Query Enterprise Custom Fields.

Have a Question?

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