|
|
|
Resource Usage (Timephase data by day)
NOTE: The following query need to be test, please make sure it is working prior to use it for live data.
This view generates the details of Resource Name, Project Name, Work, Actual Work, Assignment Date for all the projects in MS Project Server for specified given date intervals. This view also displays the Week Start date for each day. Week Start date is calculated as Monday of that assignment day.
This scripts generates the data same as Resource Usage View in project plan.
How to Run: Run the following query by selecting Reporting database.
Please specify the your required date ranges for @StartDt and @EndDt
DECLARE
@StartDt As DateTime,
@EndDt As DateTime
SET @StartDt = '1/1/2007'
SET @EndDt = '12/1/2007'
SELECT MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName,
MSP_EpmAssignment.AssignmentStartDate,MSP_EpmAssignment.AssignmentFinishDate,
MSP_EpmAssignment.AssignmentActualStartDate,MSP_EpmAssignment.AssignmentActualFinishDate,
MSP_EpmAssignment.AssignmentIsOverallocated,MSP_EpmAssignment.AssignmentPercentWorkCompleted,
MSP_EpmAssignmentByDay_UserView.TimeByDay, MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmAssignmentByDay_UserView.AssignmentActualWork, MSP_EpmAssignmentByDay_UserView.AssignmentRemainingWork,
MSP_EpmAssignmentByDay_UserView.AssignmentActualRegularWork,
MSP_EpmResource_UserView.ResourceName
FROM MSP_EpmAssignmentByDay_UserView INNER JOIN
MSP_EpmProject_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID AND
MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
MSP_EpmAssignment ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment.AssignmentUID AND
MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID AND
MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmAssignment.TaskUID INNER JOIN
MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
WHERE (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
(MSP_EpmAssignment.AssignmentStartDate >= @StartDt
AND MSP_EpmAssignment.AssignmentFinishDate <= @EndDt)
| |
|
|
