Get Project Detail Project Server 2013

Project Detail Project server 2013:-

Project detail in project server 2013 is the collection of information regarding a particular project.Below query retrieve information of every project in project center, like how many number of resources are assign on particular project, Project start date, Project finish date,Project percentage complete,Project Issue,Project Risk,Project Duration, Milestone task etc. Below is query to Get Project Detail Project Server 2013:-

select EPUV.[ProjectName],EPUV.[ProjectUID],EPUV.[ProjectStartDate],EPUV.[ProjectFinishDate],EPUV.[ProjectDuration]/8 as [ProjectDuration] ,Convert(varchar(6000),EPUV.[ProjectDescription]) as Comments,
EPUV.[ProjectActualDuration]/8 as [ProjectActualDuration],EPUV.[ProjectActualStartDate],EPUV.[ProjectActualFinishDate],EPUV.[ProjectPercentCompleted],EPUV.[ProjectRemainingDuration]/8 as [ProjectRemainingDuration]
,(Select Count(*) from [MSP_WssIssue_OlapView] EIssue where EIssue.[ProjectUID] = EPUV.[ProjectUID]) IssueCount
,(select [ResourceName] FROM [MSP_EpmResource] as Resss WHERE Resss.ResourceUID = EPUV.[ProjectOwnerResourceUID]) ProjectManagerName
,(Select Count(*) from [MSP_WssRisk] ERisk where ERisk.[ProjectUID] = EPUV.[ProjectUID]) RiskCount
,(Select count(distinct Res.ResourceName) ResourceCount
from [ProjectWebApp_Projects].[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 where Assign.PROJ_UID= EPUV.[ProjectUID] group by Project.ProjectName) ResourceCount
,(Select count(distinct Task.TaskName) Upcoming
from [MSP_EpmTask_UserView] as Task
inner join [MSP_EpmProject_UserView] on
[MSP_EpmProject_UserView].ProjectUID=Task.ProjectUID
where Task.TaskBaseline0StartDate between GETDATE() And DATEADD(day,7,Getdate()) and
Task.TaskPercentCompleted!='100' and
Task.ProjectUID= EPUV.[ProjectUID]
group by Task.ProjectUID) as Upcoming
, (Select COUNT( Task.TaskName)
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
where Assign.PROJ_UID=EPUV.[ProjectUID] and getdate()> Task.TaskBaseline0StartDate and Task.TaskPercentCompleted!='100') Delay
,(select count([TaskIsMilestone]) FROM .[MSP_EpmTask_UserView] where [TaskIsMilestone]=1 and [MSP_EpmTask_UserView].ProjectUID=EPUV.ProjectUID ) TotalMilestone
,(select [PictureUrl] FROM [User Profile Service Application_ProfileDB_eba9663bc8d14d4a946ee5b6193c3c81].[dbo].[UserProfile_Full] Profiledb
inner join [MSP_EpmResource] on convert(varchar(max),Profiledb.preferredNAme) COLLATE DATABASE_DEFAULT =convert(varchar(max),[MSP_EpmResource].[resourcename]) COLLATE DATABASE_DEFAULT
where [MSP_EpmResource].ResourceUID=EPUV.ProjectOwnerResourceUID ) PictureURL
FROM [MSP_EpmProject_UserView] EPUV
INNER JOIN MSP_EpmResource_UserView R
ON EPUV.ProjectOwnerResourceUID = R.ResourceUID

Get Project Detail Project Server 2013

ProjectDetail2


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 !!