Counting Overtime

[reza]

Hi All,

In MS Pro 2007 & PS 2007,
1. can we count total OT in a weeks or months maybe?
2. can we count total resources who work in projects for a month?

thank you

reza

 

[.jussi]

Hi Reza,

Both are easily done with a reporting services report. The data you need is
in the reporting database in the msp_epmassignmentbyday_userview view. It is
probably available through one of the olap cubes as well.

For the OT, something like this should do the trick:
SELECT
projects.projectname,
year(assignments.timebyday) as year,
month(assignments.timebyday) as month,
datepart(ww, assignments.timebyday) as week,
sum(assignments.assignmentactualovertimework) as overtime
FROM dbo.msp_epmproject_userview as projects

LEFT OUTER JOIN dbo.msp_epmassignmentbyday_userview as assignments
ON assignments.projectuid = projects.projectuid

WHERE assignments.assignmentactualovertimework > 0
GROUP BY
projects.projectname,
year(assignments.timebyday),
month(assignments.timebyday),
datepart(ww, assignments.timebyday)


Hope that helps,
- Jussi

 

[reza]

Jussi--

thank you

Hi Reza,

Both are easily done with a reporting services report. The data you need is
in the reporting database in the msp_epmassignmentbyday_userview view. It is
probably available through one of the olap cubes as well.

For the OT, something like this should do the trick:
SELECT
projects.projectname,
year(assignments.timebyday) as year,
month(assignments.timebyday) as month,
datepart(ww, assignments.timebyday) as week,
sum(assignments.assignmentactualovertimework) as overtime
FROM dbo.msp_epmproject_userview as projects

LEFT OUTER JOIN dbo.msp_epmassignmentbyday_userview as assignments
ON assignments.projectuid = projects.projectuid

WHERE assignments.assignmentactualovertimework > 0
GROUP BY
projects.projectname,
year(assignments.timebyday),
month(assignments.timebyday),
datepart(ww, assignments.timebyday)


Hope that helps,
- Jussi