Accessing Actual Hours

[Trev]

I am using Project Pro 2003 Server and my end users update their actual time
via web client. I am trying to build a SQL view that will return all actual
time entered between a given date range, typically the previous week. In
Project when I go to Task Usage, the right pane I have selected to Actual
Work. This is what I need returned in the view. Could someone tell me where
this data is stored or if you want, to share a select statement you may use
today. I have been looking at MSP_WEB_WORK table but either its not the
right place or I am not seeing all time here. My end goal for this view is
to run a Crystal Subreport that shows a summary per person of how much time
they spent on the project last week. If there is more information you need
I can provide it..

Trevor

 

[Gary L. Chefetz [MVP]]

Trev:

On your Project Server machine, under the help directory for Project Server,
locate a file called svrdb.htm. This will help you understand how data is
stored in the web_work table and contains sample queries. Note that
prjdb.htm contains the data dictionary for the project tables, while the
first document contains the server tables.

--


Gary L. Chefetz, MVP
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com
For Project Server Books: http://www.projectserverbooks.com
For Project Server Training: http://www.projectservertraining.com
For Project FAQS: http://www.mvps.org/project

 

[Trev]

After some data mining I discovered why my query into MSP_WEB_WORK wasnt
working. If a user updates the same task for two sequential days with the
same amount of hours Eg. Enters 8 hours for 01/01/2007 and 01/02/2007 on the
same task MS stores this as ONE row in the table, 8 hour duration, start date
01/01/2007, end date 01/02/2007. Therefore the select statement was not
returning one row for each day column i was seeing in the task view within
project. End result was tweaking the query as below to find the difference
between dates and multilying that by the time spent. This will give you all
resources actual hours last week. I use it in Crystal as a subreport grouped
by project.

ALTER VIEW dbo.DPSI_INCOMPLETE_TASKS_PREVIOUS
AS
SELECT a.TASK_NAME, a.WPROJ_ID, w.WWORK_VALUE, (datediff
(d,WWORK_START,WWORK_FINISH) + 1)as no_of_days, r.RES_NAME, w.WWORK_START,
w.WWORK_FINISH
FROM dbo.MSP_WEB_WORK w INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON w.WRES_ID = a.WRES_ID AND
w.WASSN_ID = a.WASSN_ID INNER JOIN
dbo.MSP_WEB_RESOURCES r ON a.WRES_ID = r.WRES_ID
WHERE (w.WWORK_TYPE = 1)