Task Reporting by Resource - All Projects
William Busby
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
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
"William Busby" <wbb...@earthlink.net> wrote in message news:2VmRd.187$Ba3...@newsread2.news.atl.earthlink.net...
Jan De Messemaeker
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
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/index.htm
32-495-300 620
"William Busby" <wbb...@earthlink.net> schreef in bericht
news:2VmRd.187$Ba3...@newsread2.news.atl.earthlink.net...
Dale Howard [MVP] dot at dot
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.
--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
http://www.msprojectexperts.com
"We wrote the book on Project Server"
"William Busby" <wbb...@earthlink.net> wrote in message
news:2VmRd.187$Ba3...@newsread2.news.atl.earthlink.net...
Ed Morrison dot dot
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
--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
"William Busby" <wbb...@earthlink.net> wrote in message
news:2VmRd.187$Ba3...@newsread2.news.atl.earthlink.net...
Paul Schaefer
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" <ed(dot)morrison at msProjectExperts(dot)com> wrote in message
news:OeuNxuf...@TK2MSFTNGP14.phx.gbl...
Ed Morrison dot dot
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
"Paul Schaefer" <psch...@medplus.com> wrote in message
news:OGgNIRdG...@tk2msftngp13.phx.gbl...