Re: Resource availability report via SSRS

[Jonathan Sofer [MVP]]

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

"dleonard" <dleo...@discussions.microsoft.com> wrote in message
news:293D58A0-A6E8-48EC...@microsoft.com...
>I want to create a report similar to the resource availability report in
> resource centre using SRSS. Can anyone tell me which fields from which
> views
> I would need to use to achieve this?

[sara...@gmail.com]

Hi Jona,

Really very much appreciated this query, can you join the both in the single query.

Like capacity with the assignment.

Thanks for your effort.