Report of Actual Hours by Day by Resource

[JodyJ]

I'm trying to get a data dump of each resources timesheet. We have resources
log time each day.

I found a previous posting that indicated that I should use
msp_view_proj_assn_tp_by_day. I've tried using assignmenttimeactualwork and
assignmenttimeactualworkprotected. There are values in these fields that are
no where on anyone's timesheet or in MS Project.

Is there some other table I should be using? Has anyone else encountered
this issue.

 

[Gary L. Chefetz [MVP]]

Jody:

On your server in the Project Server installation directory help files
folder you'll find two htm documents: svrdb and prjdb which are data
dictionaries for the database. These have sample queries for the time-phased
tables which use a "space-saving" algorithm to store repetitive data.

 

[JodyJ]

This is the query I'm running. It came right from a stored procedure in the
SDK.

select substring(r.res_name,0,15) as Resource,
substring(p.proj_name,0,15) as Project,
td.AssignmentTimeStart,
td.AssignmentTimeFinish,
(td.AssignmentTimeActualWork / 1000) / 60 as Hours
from msp_web_projects p,
msp_web_assignments a,
msp_web_resources r,
msp_view_proj_assn_tp_by_day td
where p.wproj_id = a.wproj_id
and a.wres_id = r.wres_id
and a.assn_uid = td.AssignmentUniqueID
and AssignmentTimeActualWork is not null
--and a.wres_id=133
order by assignmenttimestart


There are all sorts of actual hours in the results that are no where to be
found on anyone's timesheet or in MS Project.

Is there something else I need to do to get the right data?

 

[JodyJ]

Also, is there somewhere else I can find these data dictionaries. My windows
server admin says that they aren't on the box or on the installation cd??????

 

[Gary L. Chefetz [MVP]]

Jody:

MSP_WEB_WORK contains the data from the timesheets the way it was reported
by the resources. Keep in mind that actual work can change significantly
when it gets updated to the project plan as it is subject to the standard
Project behaviors as affected by task type and other task settings as well
as your collection methods.

The documents are located at:

<your install drive>Program Files\Microsoft Office Project Server\Help\<your
language code>

PJDB.HTM
PJSVRDB.HTM

I misquoted the name of the server side document in my last reply

 

[JodyJ]

I found the docs..thanks..

My fundamental problem is that MSP_Web_Work appears to show cumulative time.
The timephased table shows the by day detail but there are actuals in the
various actual work columns that aren't on the timesheet. For example, I
have a resource that logged 8 hours to a task on a single day.....the
timephased data shows 60 hrs for the same day......The issue doesn't exist
for just one resource, it exists for just about every sample I've taken.

 

[Gary L. Chefetz [MVP]]

Jody:

Again, my fundamental answer is to learn how to parse MSP_WEB_WORK if you
want to retrieve the data from the timesheets. Find the documents.

 

[JodyJ]

Ok...I got ya' now...sorry for being a little slow this morning......

thanks again for your help

 

[Gary L. Chefetz [MVP]]

Probably has something to do with looking at all that naked data in the
tables.<g>

 

[JDC_Consulting]

Gary -

Going to tag onto this thread. Using MSP_WEB_WORK as the source table. The
problem we are having is accessing time that is either (if you were looking
at a timesheet in Adjust Actuals) Red - entered by the resource, but not yet
approved by the Project Manager or Blue - entered by the resource, but not
sent for approval ("Updated"). Would like the report we are doing to reflect
all time entered during the week.

Thanks - Jeff