Which Enterprise Projects are using local resources?

Posted By Posted by: Peter Williams on May 4, 2016

When implementing Resource management in a Project Server environment, it can be useful to a PMO administrator to know which projects contain local resource assignments as opposed to the use of only Enterprise resources.

For an On-Premise installation of Project Server 2010 or 2013, where access to the underlying databases is possible (as opposed to Project Online where it is not accessible), the following SQL query will list the Project Names or projects that have local resources assigned to tasks.

 

USE -- ProjectServerDatabaseName
GO


SELECT Distinct
Assn.[ProjectUID]
,Prj.ProjectName
,Assn.[ResourceUID]
,Res.ResourceName


FROM [MSP_EpmAssignment_UserView] AS Assn
inner join MSP_EPMResource_Userview as Res on Assn.ResourceUID = Res.ResourceUID
inner join MSP_EPMProject_UserView AS Prj on Assn.ProjectUID = Prj.ProjectUID
Where Res.ResourceName like 'Generic Local Resource'
Order By Prj.ProjectName


Blog Posted In Blog Posted In: Blog, PMO, Project Server
Blog Posted In 

Leave a Reply

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