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:

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

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.