Get Assigned Resource on Project Task Project Server 2013

Get Assigned Resource on Project Task:-

A Project has multiple task created by project manager and he or she assign to resource on particular task. A project task may have multiple resource or single resource. Two way to get Get Assigned Resource on Project Task. Here is sql query bwlow to Get Assigned Resource on Project Task:-

Select Project.ProjectUID,Project.ProjectName,Res.ResourceName,Count(Task.TaskName) TaskCount,ASSN_IS_OVERALLOCATED
,[User Profile Service Application_ProfileDB_eba9663bc8d14d4a946ee5b6193c3c81].[dbo].[UserProfile_Full].[PictureUrl] as ResourcePictureURL
,(Select Count(*) from [MSP_WssIssue] EIssue where EIssue.[ProjectUID] = Project.[ProjectUID]
and EIssue.[AssignedToUserClaimsAccount]=Res.[ResourceNTAccount]) IssueCount,(Select Count(*)
from [MSP_WssRisk] ERisk
where ERisk.[ProjectUID] = Project.[ProjectUID] and ERisk.[AssignedToUserClaimsAccount]=Res.[ResourceNTAccount]) RiskCount
from [diag].[pub_MSP_ASSIGNMENTS] as Assign INNER JOIN [MSP_EpmResource_UserView] as Res
ON Res.ResourceUID = Assign.RES_UID INNER JOIN [MSP_EpmTask_UserView] as Task ON Task.TaskUID = Assign.TASK_UID
INNER JOIN [MSP_EpmProject_UserView] as Project ON Project.ProjectUID = Assign.PROJ_UID
inner join [User Profile Service Application_ProfileDB_eba9663bc8d14d4a946ee5b6193c3c81].[dbo].[UserProfile_Full]
on [User Profile Service Application_ProfileDB_eba9663bc8d14d4a946ee5b6193c3c81].[dbo].[UserProfile_Full].[PreferredName]
=Res.[ResourceName] COLLATE Latin1_General_CI_AS
group by Project.ProjectUID,Project.ProjectName,Res.ResourceName,Res.[ResourceNTAccount],ASSN_IS_OVERALLOCATED,[User Profile Service Application_ProfileDB_eba9663bc8d14d4a946ee5b6193c3c81].[dbo].[UserProfile_Full].[PictureUrl]

Here is Output :

Get Assigned Resource on Project Task


Comments

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

error: Content is protected !!