MS Project Timephased Data

[Steve Crago \(Hotmail\)]

Help please...

We would like to report Timephased data on a day by day basis for each
Project Task Assignment in a query tool such as MS Access. The query result
would give us the "Work" hours per day as per the Task Usage view in MS
Project.

The MS Project data is in a SQL Server database. We are currently using the
MS Project OLEDB Provider to read this information however need to be able
to read this information without requiring MS Project to be installed. The
MS Project OLE DB Providor requires MS Project to be installed.

We are using MS Project 2003 Professional and are not using Project Server.

We have attempted to use the SQL Server Stored Procedures
"sp_pjactualsbyweek.sql", "sp_psactualsbyday.sql" and
"sp_psactualsbyweek.sql" however these do not provide the timephased data on
a day by day basis as they appear in the TaskUsage view in MS Project.

Thanks very much for your assistance.

Regards,

Steve

 

[Kishore]

Hi Steve,
You can consider exporting the Project into MS Access mdb file and then open
the MSP_TIMEPHASED_DATA table and read it. You may have to apply some
transformation to get the format you are looking for but, I guess it will
serve your purpose. Pl. let me know if you need any further info.

Regards,
Kishore
ps: If you like the answer please rate the post.

 

[Aby]

Hi...

Few clarifications...

1. Are you referring to the work assigned or actual work done(timesheet) ?

2. The task usage view of MSP and output of sp_psactualsbyday.sql are very
similar... If you are further looking at work on each day instead of work
for a range of dates(AssignmentTimeStart to AssignmentTimeFinish), it is
more ideal to use the output of sp_psactualsbyday.sql in a reporting tool or
program (vb, asp etc..) to parse it.

I have the following SQL statement to do a similar job from MSP_WEB_WORK
table. (Hope you have data in it.. I use project server)

For the SQL to work, you need to have a dummy table, named DUAL, with a
single column, named NUM, type integer, have values from 0 to 1000. I have
used the date range '09-17-2005' to '09-21-2005'. (This is for assignment...
if you are looking for timesheet data, filter wwork_type for 1 instead of 0)


select wres_id,wassn_id,
cast('09-17-2005' as datetime)+(datediff(day,'09-17-2005',wwork_start)
+abs(datediff(day,'09-17-2005',wwork_start)))/2+num,
wwork_value
from msp_web_work, dual where num<(datediff(day,'09-17-2005','09-21-2005') +
1 -
(datediff(day,'09-17-2005',wwork_start)+abs(datediff(day,'09-17-2005',
wwork_start)))/2 -
(datediff(day,wwork_finish,'09-21-2005')+abs(datediff(day,wwork_finish,
'09-21-2005')))/2) and
wwork_type=0 and wwork_start<='09-21-2005' and wwork_finish>='09-17-2005'
order by 3

Cheers...