SQL Function to Display Multi-field Values in a SSRS Report

Posted By Posted by: EPM Partners on February 20, 2014

Background: I recently 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 multi-value custom field that contains values. View names for multi-value 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 the 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 to  Snapshot B.

a

                                      Snapshot A

 

b

                                         Snapshot B

Solution:

1. Create the below SQL function. It is ready to use, however you will need to change the highlighted text in yellow with relevant database, custom field, and lookup table names.

– Database name in which function to be created

USE [DatabaseName_ProjectWebApp]

GO

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

RETURN @r

END

GO

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 the above snapshot B. Bingo!

SELECT

ProjectName,

dbo.[getEPMDepartmentMultipleMemberValue](MSP_EpmProject_UserView.ProjectName) as Department

FROM

MSP_EpmProject_UserView

c

I hope this helps you.


Blog Posted In Blog Posted In: Project Server
Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *