Monthly Archives: February 2014

SQL Function to display Multi-field values in a SSRS report

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.

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

SELECT

ProjectName,

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

FROM

MSP_EpmProject_UserView

c

Hope this helps you.