none
Solution: How to use SQL to look up My Manager using the RBS in the 2010 Reporting DB RRS feed

  • Question

  • USE [EPMO_ProjectServer_Reporting]
    GO
    ------------------------ USE RBS structure to find my manager in SQL -----------------------------------
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    declare @MyResourceUID [uniqueidentifier]  
    set @MyResourceUID = '0C19548C-8006-4CFE-9405-62A952D4DB64'  -- This is My Resource Record Unique Identifier!
    /* Single Manager Select ------------------------------------------------------------------------------
    This select works only if the is one and only one resource linked to the manager level RBS.
    ------------------------------------------------------------------------------------------------------*/
    SELECT  
    [Staff Resource UID]=[r].[ResourceUID] , 
    [Staff Name]=[r].[ResourceName] , 
    [Staff RBS]=RBS_Lookup_Table_Staff.MemberFullValue,
    [Manager RBS Table UID]=RBS_Lookup_Table_Staff.ParentMemberUID,
    [Manager]= Manager.ResourceName
    FROM dbo.[MSP_EpmResource] r 
    	LEFT OUTER JOIN dbo.[MSP_EpmCPResUid0] as StaffCustomUIDFields 
    		ON StaffCustomUIDFields.EntityUID = r.ResourceUID   
    		  
    	LEFT OUTER JOIN MSP_EpmLookupTable as RBS_Lookup_Table_Staff 
    						ON RBS_Lookup_Table_Staff.LookupTableUID = '00008E67-65A3-4898-BAAA-D82D995BBB02' 
    						AND RBS_Lookup_Table_Staff.MemberUID=StaffCustomUIDFields.[CFVal0] 
    						 					
    	LEFT OUTER JOIN dbo.[MSP_EpmCPResUid0] as ManagerCustomUIDFields 
    						ON ManagerCustomUIDFields.[CFVal0] = RBS_Lookup_Table_Staff.ParentMemberUID
    						
    	LEFT OUTER JOIN dbo.[MSP_EpmResource] as Manager
    						ON ManagerCustomUIDFields.EntityUID = Manager.ResourceUID   
    where r.ResourceUID = @MyResourceUID
    /* Multi Manager Select ------------------------------------------------------------------------------
    However, supposing that you want to locate the Manager's administrative assistant with the Manager at the same level in
    the RBS so that the admin assistant has the same level of authority. In that case, the above query won't work because
    it returns multiple resource rows for one resource. Returning multiple rows like this really screws up exception reporting!
    Even if formal PMO policy is to have one and only one Resource assigned at the RBS manager levels, the maintenance burden
    of the Resource data means that this policy is regularly not honored. For instance, manager A is leaves and is marked as
    inactive. Manager B is promoted and takes over, but the RBS of Manager A is not changed.
    screw ups 
    There are several possible solutions to this but lets put all the manager names in 
    a single field as a comma delimited string using the XML extensions in SQL Server:
    ------------------------------------------------------------------------------------------------------*/
    SELECT  
    [Staff Resource UID]=[r].[ResourceUID] , 
    [Staff Name]=[r].[ResourceName] , 
    [Staff RBS]=RBS_Lookup_Table_Staff.MemberFullValue,
    [Manager RBS Table UID]=RBS_Lookup_Table_Staff.ParentMemberUID,
     
    [Manager Count] = (select COUNT(*) from dbo.[MSP_EpmCPResUid0] as ManagerCustomUIDFields1  where ManagerCustomUIDFields1.[CFVal0] = RBS_Lookup_Table_Staff.ParentMemberUID),
    [Manager Names]=STUFF((
    		select ','+ Manager.ResourceName from dbo.[MSP_EpmResource] as Manager
    					 inner join dbo.[MSP_EpmCPResUid0] as ManagerCustomUIDFields on ManagerCustomUIDFields.EntityUID = Manager.ResourceUID
    					    where ManagerCustomUIDFields.[CFVal0] = RBS_Lookup_Table_Staff.ParentMemberUID order by Manager.ResourceName
    		FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)')
    		,1,1,'') 
    FROM dbo.[MSP_EpmResource] r 
    	LEFT OUTER JOIN dbo.[MSP_EpmCPResUid0] as StaffCustomUIDFields 
    		ON StaffCustomUIDFields.EntityUID = r.ResourceUID   
    		  
    	LEFT OUTER JOIN MSP_EpmLookupTable as RBS_Lookup_Table_Staff 
    						ON RBS_Lookup_Table_Staff.LookupTableUID = '00008E67-65A3-4898-BAAA-D82D995BBB02' 
    						AND RBS_Lookup_Table_Staff.MemberUID=StaffCustomUIDFields.[CFVal0] 	
     
    where r.ResourceUID = @MyResourceUID

    Thursday, June 14, 2012 6:29 PM

Answers

  • Take a look at the ResourceIsActive/ResourceIsGeneric/ResourceIsTeam fields as you can use these Fields to filter out inactive/generic/team resources. Also, you may be able to simplify your query if you use the MSP_EpmResource_UserView rather than MSP_EpmResource table since all of your custom fields, including RBS, are already present in the view.

    Otherwise, nice job. This would be a great basis for a resource manager report.

    Treb Gatte

    • Marked as answer by Pelican Carl Thursday, June 21, 2012 5:55 PM
    Monday, June 18, 2012 8:18 AM
    Moderator