Querying Multi-Value Custom Fields
Scenario
You have a report where the need is to show multiple values for a given custom field. For example, you have a multi-value Project custom field for Impacted Business Organizations.
You want to see your values as a comma delimited list so that this can be used in an Excel pivot table or SSRS tablix report. You might need something like:
Project Impacted Business Orgs Project XYZ IT, HR, Operations
The Background
When a Project text custom field with associated lookup table is made multi-value, a number of changes are made in the Reporting Database. First, the field is removed from the MSP_EpmProject_UserView as that view only supports single select Project text custom fields with associated lookup table. Second, a new Association View view is created which has the following naming convention: MSPCFPRJ_YourMultiValueCustomFieldName_AssociationView
MSP prefixes all Microsoft views, CF for Custom Field and PRJ for the Project entity. This association view contains a record for each of the multiple custom field values selected, linking the Project record to the lookup table values in the MSPLT_YourMultiValueCustomFieldLookUpTableName_UserView view. LT in this case, stands for lookup table, so there is a MSPLT view for each lookup table in the Reporting Database.
This mechanism was first documented in the Project Server 2007 Report Pack that I wrote and can be found here: http://msdn.microsoft.com/en-us/library/office/bb428828(v=office.12).aspx The Portfolio report also provides another way to utilize the multi-value field.
The Query
This query uses the XML functionality to build the concatenated string, based on a technique documented on StackOverflow here.
Once I modified the STUFF statement for specific use for Project Server, I wrapped it with an outer SELECT to combine it with all of the data from MSP_EpmProject_UserView. Note, if you have multiple multi-value fields, you will have to duplicate this inner piece in the parentheses for each field. The places to replace with your own field names are highlighted.
SELECT MSP_EpmProject_UserView.* , MVList.[YourMultiValueCustomFieldNameValues] FROM MSP_EpmProject_UserView INNER JOIN (SELECT MSP_EpmProject_UserView.ProjectUID ,ISNULL(STUFF((SELECT ', '+ MemberValue FROM [MSPLT_VP Lookup_UserView] INNER JOIN [MSPCFPRJ_YourMultiValueCustomFieldName_AssociationView] ON [MSPLT_YourMultiValueCustomFieldLookUpTableName_UserView].LookupMemberUID = [MSPCFPRJ_YourMultiValueCustomFieldName_AssociationView].LookupMemberUID WHERE [MSPCFPRJ_YourMultiValueCustomFieldName_AssociationView].EntityUID = MSP_EpmProject_UserView.ProjectUID FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,2, ''),'')AS YourMultiValueCustomFieldNameValues FROM MSP_EpmProject_UserView GROUP BY ProjectUID ) MVList ON MSP_EpmProject_UserView.ProjectUID = MVList.ProjectUID
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }
The Output
The output will yield a comma delimited list of values in the last column of the dataset. If you need that comma delimited list sorted, add an ORDER BY MemberValue statement right before the GROUP BY ProjectUID) MVList statement.