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?
https://gallery.technet.microsoft.com/site/view/file/181549/1/PS%20Datababase.jpg
Scenario
We have a custom view in Project Center that show an enterprise custom project fields with multiple lookup table values that called "Departments" as 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.
https://gallery.technet.microsoft.com/site/view/file/184624/1/MSP_EpmLookupTable.png
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").
- [MSP_EpmProject_UserView] : specify the project fields that you need to show. in our case we will show the following fields:
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
- T-SQL: Query Task and Display Settings In Project Server 2016.
- T-SQL: Query The Enterprise Custom Fields In Project Server 2016 /2013.
- Project Server: Show Task Hierarchy using SQL.