Where is time in "My Tasks" stored?

C

Chupacabra

I need to extract time that people have entered into various tasks under "My
Tasks" for an external application, but can't seem to find it in the
database anywhere. I was able to get timesheet data, but we've decided to
put everything into "My Tasks" instead.

Is this something that's only available via PSI, or is it in the database
somewhere and I just can't find it?

Thanks!
 
J

James Fraser

I need to extract time that people have entered into various tasks under "My
Tasks" for an external application, but can't seem to find it in the
database anywhere. I was able to get timesheet data, but we've decided to
put everything into "My Tasks" instead.

Is this something that's only available via PSI, or is it in the database
somewhere and I just can't find it?
Click to expand...

This data is impressively difficult to get at before it is in the
project plans.
From an earlier post I made:This data is stored in binary in the tables. If I recall correctly the
table is the MSP_ASSIGNMENT_TRANSACTIONS table, and the
ASSN_CHANGE_DATA holds the binary with the data you're looking for.

Yeah, I wasn't too happy about this either...
<<<
I think even getting this through the PSI is difficult, as you need to
impersonate different users to see what updates they have pending.

I have no idea why this data is not more readily available.


James Fraser
 
C

Chupacabra

This data is impressively difficult to get at before it is in the
project plans.
From an earlier post I made:
This data is stored in binary in the tables. If I recall correctly the
table is the MSP_ASSIGNMENT_TRANSACTIONS table, and the
ASSN_CHANGE_DATA holds the binary with the data you're looking for.
Click to expand...

I did some SQL Profiler tracing, and found it also in the MSP_TASKS_SAVED
and MSP_ASSIGNMENTS_SAVED tables in the Published database. While it also
appears to be in the binary data, I found it in specific fields such as

TASK_WORK, TASK_REG_WORK and TASK_ACT_WORK in the MSP_TASKS_SAVED table, and
in

ASSN_WORK, ASSN_REG_WORK, and ASSN_ACT_WORK in the MSP_ASSIGNMENTS_SAVED
table.

So far, I don't know what happens to the data when it gets approved/rejected
by a manager (or if it gets approved for that matter). I do know that
whatever time I put in My Tasks is showing up in those fields as
milliminutes (i.e. 100 hours is stored as 6,000,000), at least in rehearsal!

I think I'm on the right track, it's the only place I've found the data so
far....

Thanks!
 
C

Chupacabra

I may have spoken too soon.

If I put time in for a week, there's still only one entry in the database
with a start and stop time covering all the time I entered and a sum of all
the time contained therein. I need to be able to slice down to the day to
get the time. Obviously it's stored in there somewhere since PWA can break
it out by day.

I will look at the fields you mentioned as well to see what I can find....
 
C

Chupacabra

You are correct, I can see how it's adding additional binary data to the
ASSN_CHANGE_DATA field for each day's data.

The delta between each day's data looks like this:

0A00000F0C00000000004D2200004E220B00000000000000004C1D410000000000000000

Do you recall how these digits break down? The first two segments appear to
represent the date and time of the entry, then the date/time of the
submitted time. Guessing that the rest represent the actual time entered
for that day.
 
C

Chupacabra

As a follow up, is there an easy way to automate the importing of time from
the My Timesheet area into My Tasks? I have found a PSI call of
Statusing.ImportTimesheet Method, but this states that it is for importing
from My Tasks into My Timesheet.

We have a need to automate the flow from My Tasks into My Timesheet. Anyone
ever done this, or have any suggestions on how I might accomplish this?

Thanks!
 
J

James Fraser

In Project Server 2007 I would use the Reporting db and the Views already
created for your there. The most useful ones are the View names ending in
userview as they already have relevant custom fields appended to the end
automatically.
Click to expand...

The reporting views depend on a project publish, and will only include
the data accepted in to the project. I have no problem with this, but
often customers need reports on data that may not have been published.

A time-phased "Where's the time" report indicating whether the time
has been entered, or submitted is one of our customer's favorite
reports. This allows our sponsors to know if the the under-reported
time is due to non-compliance at a resource level or PM level, and
where training/ reminders are needed. For example, when a key PM is on
vacation for a couple of weeks, the PMO doesn't ding the resources
that are reporting their time. This report is particularly crucial in
environments using the Project numbers as billing; it lets executives
and PM's know quickly if they had full compliance from resources in
the previous week and correct that before the time is forgotten.


James Fraser
 
J

James Fraser

As a follow up, is there an easy way to automate the importing of time from
the My Timesheet area into My Tasks? I have found a PSI call of
Statusing.ImportTimesheet Method, but this states that it is for importing
from My Tasks into My Timesheet.

We have a need to automate the flow from My Tasks into My Timesheet. Anyone
ever done this, or have any suggestions on how I might accomplish this?

Thanks!
Click to expand...


How about going the other way?
http://www.codeplex.com/epmtsst/
 
Top