Background: Recently, I had a requirement from a client requiring the display of multi-value custom field values in a SSRS report.
Technical Information: The RDB includes an association table view for each multivalue custom field that contains values. View names for multivalue custom fields are of the form MSPCFxxx_Custom Field Name_AssociationView, where xxx can be PRJ, RES, or TSK, for a project, resource, or task custom field.
Environment: Project Server 2013, SQL Server 2012, SQL Server Data Tool 2010
Objective: As per snapshot below, the Project manager has selected the two department values ‘PMO’ and ‘ICT’ for a Project ‘XYZ’ which is to be displayed with coma separated values in the SSRS report Department section. Refer Snapshot B.
1. Create the below SQL function. It is ready to use however you will need to change the highlighted text in yellow color with relevant database, Custom field, and Lookup table names.
— Database name in which function to be created
/* Object: UserDefinedFunction [dbo].[getEPMDepartmentMultipleMemberValue] */
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
— Function Name
CREATE FUNCTION [dbo].[getEPMDepartmentMultipleMemberValue] (
/* Declare the projectname variable */
@ProjectName VARCHAR(300) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @r VARCHAR(8000) SELECT @r = ISNULL(@r+’,’, ”) + MemberValue FROM MSP_EpmProject_UserView P LEFT OUTER JOIN [MSPCFPRJ_CustomfieldName_AssociationView]LAV ON P.ProjectUID = LAV.EntityUID INNER JOIN [MSPLT_LookTableName_UserView]LUV ON LAV.LookupMemberUID = LUV.LookupMemberUID
WHERE (ProjectName = @ProjectName)
2. Once you have created the above SQL function successfully it can be invoked in the SQL script as per below highlighted text which will give you a desired result as per above snapshot B. Bingo!
dbo.[getEPMDepartmentMultipleMemberValue](MSP_EpmProject_UserView.ProjectName) as Department
Hope this helps you.