Notice few bugs

[Hyder Zaidi]

How come it is possible that one ResourceUID have two different
ResourceNameUID in Project Server 2007.

Whereas, SDK gives complete different definition i.e. GUID (ResourceNameUID
uniqueidentifier(36) of dbo.MSP_TimesheetResource)of the resource
name, remains constant even though the resource name can change.

Use ProjectServer_Reporting

SELECT
MSP_EpmResource_UserView.ResourceName,MSP_EpmResource_UserView.ResourceUID,

(SELECT COUNT(*) AS Expr1

FROM MSP_TimesheetPeriod

WHERE (StartDate >= '7/1/2009') AND (EndDate
<= '12/31/2009')) AS TotalPeriods,

(SELECT COUNT(*) AS FilledTimesheet

FROM MSP_TimesheetResource INNER JOIN

MSP_Timesheet ON
MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID
INNER JOIN

MSP_TimesheetPeriod ON
MSP_Timesheet.PeriodUID = MSP_TimesheetPeriod.PeriodUID

WHERE (MSP_Timesheet.TimesheetStatusID = 3)
AND (MSP_TimesheetPeriod.StartDate >= '7/1/2009') AND


(MSP_TimesheetPeriod.EndDate <= '12/31/2009') AND


(MSP_TimesheetResource.ResourceUID = MSP_EpmResource_UserView.ResourceUID))
AS TotalApproved

FROM MSP_TimesheetResource RIGHT OUTER JOIN

MSP_EpmResource_UserView ON
MSP_TimesheetResource.ResourceUID = MSP_EpmResource_UserView.ResourceUID

WHERE (MSP_EpmResource_UserView.ResourceIsActive = 1)

Group BY
MSP_EpmResource_UserView.ResourceName,MSP_EpmResource_UserView.ResourceUID

Having Count(*) >1

Query Results

ResourceUID = A282D49A-FFC6-40C7-8664-4E35E0842BFD

ResourceName = ABC

Timesheet Associated (ResourceNameUID =
D854908D-C433-4116-949E-3601B6905A2F )= 1

Timesheet Associated (ResourceNameUID =
E6673ADA-922F-4B80-84C8-837DB0AF1506 )= 39

ResourceUID = 0580CC81-8772-4480-939C-91DE212A7825

ResorceName = DEF

Timesheet Associated (ResourceNameUID =
41D18550-4134-454A-9424-345C3AFB5DB6) = 5

Timesheet Associated (ResourceNameUID =
ACB45DFF-7437-42E2-BF90-383F18BD175D)= 16


ResourceUID = B2D5408F-C0F6-4E81-8375-ACC079CFA0A5

ResorceName = GHI

Timesheet Associated (ResourceNameUID =
56AC5B96-620E-4B87-A887-2B273AF8E399) = 12

Timesheet Associated (ResourceNameUID =
0073EE1D-7CE9-4B53-A916-E837BD21182F)= 9


--------------------------------

Similarly, a very strange thing which I have seen and totally confused is
how come it is possible that a task percentage is 100% completed with a value
in actualfinishdate

SELECT MSP_EpmResource_UserView.ResourceUID,
MSP_EpmResource_UserView.ResourceName, COUNT(DISTINCT MSP_EpmTask.ProjectUID)

AS TotalProjects, COUNT(MSP_EpmTask.TaskUID) AS
TotalTasks,

SUM(CASE WHEN AssignmentBaseline0FinishDate = AssignmentActualFinishDate
THEN 1 ELSE 0 END) AS OnTime,

--SUM(CASE WHEN TaskPercentCompleted = 100 THEN 1 ELSE 0 END) AS
AllCompleted,

--SUM(CASE WHEN TaskPercentCompleted = 100 And Not
AssignmentBaseline0FinishDate = TaskActualFinishDate THEN 1 ELSE 0 END) AS
DelayedCompleted,

SUM(CASE WHEN AssignmentActualFinishDate >
AssignmentBaseline0FinishDate THEN 1 ELSE 0 END) AS LateFinish,

SUM(CASE WHEN AssignmentActualFinishDate < AssignmentBaseline0FinishDate
THEN 1 ELSE 0 END) AS EarlyFinish,

SUM(CASE WHEN AssignmentActualStartDate Is Not Null
And AssignmentActualFinishDate Is Null And AssignmentPercentWorkCompleted
<100 THEN 1 ELSE 0 END) AS InProgress,

SUM(CASE WHEN AssignmentActualStartDate Is Null THEN
1 ELSE 0 END) AS NotStarted,

SUM(CASE WHEN AssignmentActualFinishDate Is Null And
AssignmentPercentWorkCompleted = 100 THEN 1 ELSE 0 END) AS StrangeCondition,

SUM(CASE WHEN AssignmentActualFinishDate Is Not Null
And AssignmentPercentWorkCompleted < 100 THEN 1 ELSE 0 END) AS Ambigious

FROM MSP_EpmTask INNER JOIN

MSP_EpmAssignment_UserView ON MSP_EpmTask.ProjectUID =
MSP_EpmAssignment_UserView.ProjectUID AND

MSP_EpmTask.TaskUID =
MSP_EpmAssignment_UserView.TaskUID INNER JOIN

MSP_EpmResource_UserView ON
MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

GROUP BY MSP_EpmResource_UserView.ResourceUID,
MSP_EpmResource_UserView.ResourceName

ORDER BY MSP_EpmResource_UserView.ResourceName

Query Results

1.

ResourceUID= E74894DD-0A6B-4A4F-B1D2-6BE9B4490EF4

ResourceName=Aamir A- Rehman Thanawala

TotalProject=3

TotalTasks=158

OnTime=110

EarlyFinish=2

LateFinish=2

InProgress=0

NotStarted=36

StrangeCondition=3

Ambigious=0

Thanks in advance for your support


--
Regards,
Hyder Zaidi

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...fe93061bab&dg=microsoft.public.project.server