Query Multiple Values Lookup Table Field Project Server 2019

Missing-the-multiple-values-custom-field-in-MSP_EpmProject_UserView-in-Project-Server-2019-database

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

Query Multiple Values Lookup Table Field Project Server 2019
Query Multiple Values Lookup Table Field Project Server 2019

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:

Missing Multiple Value Lookup Fields in MSP_EpmProject_Userview

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

Allow multiple values to be selected from lookup table in Project Server 2019

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
pjrep.MSP_EpmProject_UserView in  Project Server 2019

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!

Get multiple values lookup field in MSP_EpmProject_UserView
Missing Multiple Value Lookup Fields in MSP_EpmProject_Userview

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:

Retrieve multiple values lookup custom fields in Project Server 2019

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]
Association view in Project Server database

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:

Lookup table values in Project Server Database

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]
get lookup table values in Project Server

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
Query related multiple values for lookup table custom fields in Project Server 2019

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:

Query multiple values lookup custom fields in Project Server 2019
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
Missing the multiple values custom field in MSP_EpmProject_UserView in Project Server 2019

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

Read multiple values lookup custom fields in Project Server 2019
Query Multiple Values Lookup Table Field Project Server 2019

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
Have a Question?

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

1 thought on “Query Multiple Values Lookup Table Field Project Server 2019”

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

Leave a Reply