This first query returns your resource's capacity. Specify a resource's
name by replacing "John Doe" or remove
[MSP_EpmResource_UserView.ResourceName] from the SELECT and the GROUP BY
sections to get all your resources' capacities in one shot.
This second query returns your resource's schedule work by project. Specify
a resource's name by replacing "John Doe" or remove
[MSP_EpmResource_UserView.ResourceName] from the SELECT and the GROUP BY
sections to get all your resources' work by project in one shot. If you
want overall work by resource without grouping by project then remove the
[MSP_EpmProject_UserView.ProjectName] from the SELECT and the GROUP BY
sections.
Both of these are showing the data by Month but you could modify it to show
by day, week or year.
SELECT MSP_EpmResource_UserView.ResourceName,
MONTH(MSP_EpmResourceByDay_UserView.TimeByDay) AS Month,
SUM(MSP_EpmResourceByDay_UserView.Capacity) AS
Capacity, MIN(MSP_EpmResourceByDay_UserView.TimeByDay)
AS FirstDayOfTheMonth
FROM MSP_EpmResource_UserView LEFT OUTER JOIN
MSP_EpmResourceByDay_UserView ON
MSP_EpmResource_UserView.ResourceUID =
MSP_EpmResourceByDay_UserView.ResourceUID
WHERE (MSP_EpmResourceByDay_UserView.TimeByDay BETWEEN '01/01/2009' AND
'12/31/2009')
GROUP BY MSP_EpmResource_UserView.ResourceName,
MONTH(MSP_EpmResourceByDay_UserView.TimeByDay)
HAVING (MSP_EpmResource_UserView.ResourceName = N'John Doe')
ORDER BY FirstDayOfTheMonth
SELECT MSP_EpmResource_UserView.ResourceName,
MSP_EpmProject_UserView.ProjectName,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay)
AS Month,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS AssignmentWork,
MIN(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS
FirstDayOfTheMonth
FROM MSP_EpmResource_UserView INNER JOIN
MSP_EpmTask_UserView INNER JOIN
MSP_EpmProject_UserView ON
MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER
JOIN
MSP_EpmAssignment ON MSP_EpmTask_UserView.ProjectUID =
MSP_EpmAssignment.ProjectUID AND
MSP_EpmTask_UserView.TaskUID =
MSP_EpmAssignment.TaskUID ON
MSP_EpmResource_UserView.ResourceUID =
MSP_EpmAssignment.ResourceUID INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignment.AssignmentUID =
MSP_EpmAssignmentByDay_UserView.AssignmentUID
WHERE (MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN '01/01/2009'
AND '12/31/2009')
GROUP BY MSP_EpmResource_UserView.ResourceName,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MSP_EpmProject_UserView.ProjectName
HAVING (MSP_EpmResource_UserView.ResourceName = N'John Doe')
ORDER BY FirstDayOfTheMonth
Hope this helps,
Jonathan Sofer
