Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.