Hi There,

A client of ours recently asked that a Project Server multi-value field be displayed in a single column in an SSRS report.  Though I am sure there are several ways to do this, I chose the method below.  It basically requires creating a function, that is passed the EntityUID of the multi-value field, then then returns all the selected values in a single cell using a COALESCE function.  I only went so far as sharing the SQL below, applying this to an SSRS report should be very easy for those familiar with SSRS. 

Happy coding!

End Result

clip_image002

 

 

Function code, as well as sample code leveraging function

image

 

Actual text (for copy/paste)

— Sample Function that takes an EntityUID as a parameter and returns item(s) in single column
ALTER FUNCTION [dbo].[GetMultipleValues]
(     @EntityUID      varchar(255)
)
RETURNS varchar(max)
AS
BEGIN    
    declare @output varchar(max)    
    Select @output = COALESCE(@output + ‘, ‘, ”) + MemberValue
    from MSPLT_Multiple_Userview mmu
    inner join MSPCFPRJ_multiple_AssociationView mmuv on (mmu.LookupMemberUID = mmuv.LookupMemberUID)
    inner join MSP_EpmProject_UserView mep on (mmuv.EntityUID = mep.ProjectUID)
    where mmuv.EntityUID = @EntityUID     
    return @output
END 

GO 

 

— Example code using function created above
select distinct projectname, mmuv.EntityUID, dbo.GetMultipleValues(mmuv.EntityUID) as MultipleValues
from msp_epmproject_userview mep
inner join MSPCFPRJ_multiple_AssociationView mmuv on (mep.ProjectUID = mmuv.EntityUID)