EPM Central Project Server Tutorials
Home Deployment    MS Project PWA SharePoint Articles Administrators FAQs Consulting Guest Book Search Site Map
  Error Messages

Project Server 2007

Databases

Extend Workspace

Delete Workspace

Fiscal Periods

Administrative Categories

Timesheet Periods

Lock Timesheets

Queue Administration

2007 SQL Queries

Video - PWA Permissions

Video - Manage Groups

Video - Manage Categories

Video - RBS

 
 
Administration -> MS Project Server 2007 -> SQL Queries
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)





Copyright 2013. EPM Central. All rights reserved.