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