The procedure below will get the time phase data you need. I found this in
the PRSVRDB.HTM file that ships with Project Server. I'm not sure how you
want to integrate the data with the calendar or if this will do.
declare @res_name as nvarchar(510)
declare @begin_date as datetime
declare @end_date as datetime
declare @work_type as int
-- set these variables
select @res_name = 'Resource Name'
select @begin_date = '2003-01-01'
select @end_date = '2005-12-31'
select @work_type = 0 -- 0=scheduled, 1=actual, 2=overtime
-- end user variables
-- function variables
declare @assn_id as int
declare @td_start as datetime
declare @total_days as int
declare @td_value as decimal(25,6)
declare @td_cur_date as datetime
declare @p_name as nvarchar(510)
declare @t_name as nvarchar(510)
-- end function variables
-- create temporary table
create table #tp_data ( td_date datetime, td_hours decimal(25,9),
task_name nvarchar(510), proj_name nvarchar(510) )
-- create cursor for data collection
declare td cursor for
select
a.WASSN_ID,
WWORK_START,
datediff(day, WWORK_START, WWORK_FINISH)+1,
WWORK_VALUE
from
MSP_WEB_WORK w,
MSP_WEB_RESOURCES r,
MSP_WEB_ASSIGNMENTS a
where
a.WRES_ID = r.WRES_ID
and a.WASSN_ID = w.WASSN_ID
and r.RES_NAME = @res_name
and w.WWORK_TYPE = @work_type
and (@begin_date <= WWORK_FINISH or @end_date >= WWORK_START)
order by
WWORK_START
-- loop through cursor to explode timephased data
open td
fetch next from td into @assn_id, @td_start, @total_days, @td_value
while @@fetch_status <> -1
begin
select @td_cur_date = @td_start
while @total_days > 0
begin
-- get the task name
select @t_name =
( select TASK_NAME
from MSP_WEB_ASSIGNMENTS
where WASSN_ID = @assn_id )
-- get the project name
select @p_name =
( select PROJ_NAME
from MSP_WEB_PROJECTS p, MSP_WEB_ASSIGNMENTS a
where a.WASSN_ID = @assn_id and a.WPROJ_ID = p.WPROJ_ID )
-- insert the data row into the temp table
insert #tp_data values ( @td_cur_date, @td_value,
@t_name, @p_name )
select @td_cur_date = DATEADD(d, 1, @td_cur_date)
select @total_days = @total_days - 1
end
-- get next row from cursor
fetch next from td into @assn_id, @td_start, @total_days, @td_value
end
close td
deallocate td
-- display data from temporary table with grouping and rollup
select
proj_name as Project,
task_name as Task,
td_date as 'Date',
SUM(td_hours/60000) as 'Total_Work'
from
#tp_data
group by proj_name, task_name, td_date with rollup
-- clean up that temporary table
drop table #tp_data
--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ -
http://www.projectserverexperts.com