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




Function code, as well as sample code leveraging function



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)
    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



— 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)