Task Reporting by Resource - All Projects

[William Busby]

We just completed an implementation of Project Pro Server 2003. After one
month of time reporting we're finding it increasingly difficult to extract
the level of detail our IT management desires. The holy grail would be a
report (via Portfolio Analyzer, Crystal, MS Reporting Services, whatever)
that allows us to extract how much time was reported by each resource
against all tasks (and owning project) for any date range.

In digging through the ERD, I can't find the right table relationships that
link resources to tasks and projects in such a way that I can did out time
reported by day. I can't even find values that I can interpret in attributes
labled as 'ACT_WORK', etc.

How have others resolved this? We've got the technical expertise on staff to
accomplish this if we had the information that defines where the data is
stored and how it's encoded. Do we need to contract such an effort out to
get that type of data? Surely, we're not the first ones to tackle this
issue.

 

[John Sitka]

No way this is going to be plaug and play but better than nothing I suppose,
shows some of the relationships and definitely not the only ones.
This is part of a stored procedure used for a report
OC_NAME derived from an enterprise outline code
ecf_OWE derived from an enterprise custom field which represents a value calculated by our CNC cutterpath generating software.
@sonom represents a variable all our enterprise projects have a task naming convention.


SELECT I.TASK_NAME as [Job# & Task],J.OC_NAME as [Job Type], CAST(I.pro_ACTUALWORK as NUMERIC(10,2)) as [Actual Work]
,CAST(I.ecf_OWE as NUMERIC(10,2)) as [Original Estimate],CAST(I.variance as NUMERIC(10,2)) as [Variance],I.quotedhours as [Quoted
Hours],I.timecardhours as [Time Card Hours], J.PROJ_NAME, J.RES_NAME
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X Sched Total'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) - CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.OC_NAME, HH.PROJ_NAME, PP.RES_NAME
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID = FF.ENT_TaskUniqueID
LEFT JOIN
dbo.[ab_eoc_Job Order Types] GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
LEFT JOIN
dbo.[ab_msp_pro_id_by_names] HH
ON EE.PROJ_ID = HH.PROJ_ID
LEFT JOIN
(
SELECT A.PROJ_ID,A.TASK_UID, B.RES_NAME FROM MSP_ASSIGNMENTS A
LEFT JOIN MSP_RESOURCES B
ON A.PROJ_ID = B.PROJ_ID AND A.RES_UID = B.RES_UID
--ORDER BY A.TASK_UID
) PP
ON EE.PROJ_ID = PP.PROJ_ID AND EE.TASK_UID = PP.TASK_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

 

[Jan De Messemaeker]

First, please don't crosspost, when writing this I'm al$ready wondering
whether the question isn't answered in another NG.

This is the Resource Usage view of a master file inclusing all projects,
is't it?
HTH

 

[Dale Howard [MVP]]

William --

If you do not have to see the individual tasks to which each resource is
assigned in each project, you could create a Portfolio Analyzer view that
would meet your reporting requirements, I believe. Here's how to set up the
View:

1. Drag the Resources field to the Row fields drop area
2. Drag the Projects field to the immediate right of the Resources field in
the Row fields drop area
3. Drag the Actual Work field to the Total fields drop area
4. Drag the Time dimension to the Column fields drop area and set it to
show Days

Hope this helps.

 

[Ed Morrison]

The following SQL statement returns actual hours as published by the PM.

Select p.proj_name, r.res_name, a.task_name, a.assn_act_work/60000 as
actual_hours
from msp_web_resources r
inner join msp_web_assignments a on a.wres_id=r.wres_id
inner join msp_web_projects p on p.wproj_id=a.wproj_id
order by proj_name, res_name, task_name

 

[Paul Schaefer]

Hi Ed,

What tool would you recommended someone use to query the MS Project DB for
this type of report? I am not a SQL expert, but we have staff that are. Is
this something done via a custom .asp web page, or more the lines of an
application like TOAD? If a tool like TOAD is preferred, do you know of any
risks/issues with using this type of tool?

Thanks for this awesome query string

Paul Schaefer

 

[Ed Morrison]

TOAD is an Oracle tool and this is an MS SQL 2000 database. Just use the
Enterprise Manager that comes with SQL to create queries. If you desire a
better report than a SQL dump, us a reporting tool such as Crystal Reports
or MS SQL Server Reporting Services.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com