Task Reporting by Resource - All Projects

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
Fri Feb 18 08:52:55 CST 2005

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

"William Busby" <wbbusby@earthlink.net> wrote in message news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> 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.
>

Jan
Fri Feb 18 09:25:31 CST 2005

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

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/index.htm
32-495-300 620
"William Busby" <wbbusby@earthlink.net> schreef in bericht
news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> 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.
>
>

Dale
Fri Feb 18 11:35:33 CST 2005

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.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultant
http://www.msprojectexperts.com
"We wrote the book on Project Server"


"William Busby" <wbbusby@earthlink.net> wrote in message
news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> 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.
>

Ed
Fri Feb 18 14:53:18 CST 2005

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

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



"William Busby" <wbbusby@earthlink.net> wrote in message
news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> 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.
>
>

Paul
Wed Feb 23 12:19:56 CST 2005

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" <ed(dot)morrison at msProjectExperts(dot)com> wrote in message
news:OeuNxufFFHA.768@TK2MSFTNGP14.phx.gbl...
> 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
>
> --
> Ed Morrison
> msProjectExperts
> "We wrote the books on Project Server"
> http://www.msprojectexperts.com
>
>
>
> "William Busby" <wbbusby@earthlink.net> wrote in message
> news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> > 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.
> >
> >
>
>

Ed
Wed Feb 23 19:10:40 CST 2005

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



"Paul Schaefer" <pschaefer@medplus.com> wrote in message
news:OGgNIRdGFHA.2616@tk2msftngp13.phx.gbl...
> 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" <ed(dot)morrison at msProjectExperts(dot)com> wrote in
message
> news:OeuNxufFFHA.768@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> > --
> > Ed Morrison
> > msProjectExperts
> > "We wrote the books on Project Server"
> > http://www.msprojectexperts.com
> >
> >
> >
> > "William Busby" <wbbusby@earthlink.net> wrote in message
> > news:2VmRd.187$Ba3.72@newsread2.news.atl.earthlink.net...
> > > 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.
> > >
> > >
> >
> >
>
>