Actual works for the period

[Grigory Bushuev]

How can I get the actual works for the period from MSP_ tables for each day

 

[Rod Gill]

You can't. You need to look at the time phased tables which are populated
only if you set this to happen in Tools, Options, Save tab then Expand time
phased data in the database.

This data needs to be reviewed with calendar data and resource availability
for accuracy. personally I find it significantly easier to open the project
using automation and read timephased data directly.

Read the prjdb.htm file in one of Project's program folders for more
information.

If you have Project Server 2003 then look at the MSP_View tables
If you have Project Server 2007 then read the Reporting database.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Grigory Bushuev]

Thank you, exactly. You're right.
We're using Project Server 2003
I'm tryed to get it from MSP_View tables

select proj.ProjectTitle, task.TaskName, res.ResourceName
from MSP_VIEW_PROJ_ASSN_TP_BY_DAY assnByDay
inner join MSP_VIEW_PROJ_PROJECTS_STD proj
on assnByDay.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_ASSN_STD assnStd
on assnByDay.AssignmentUniqueID = assnStd.AssignmentUniqueID
inner join MSP_WEB_PROJECTS webProj
on assnByDay.WPROJ_ID = webProj.WPROJ_ID
and proj.WPROJ_ID = webProj.WPROJ_ID
and assnStd.WPROJ_ID = webProj.WPROJ_ID
and assnStd.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_RES_STD res
on assnStd.ResourceEnterpriseUniqueID = res.ResourceEnterpriseUniqueID
and assnStd.ResourceUniqueID = res.ResourceUniqueID
and res.WPROJ_ID = webProj.WPROJ_ID
and res.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_TASKS_STD task
on assnStd.TaskUniqueID = task.TaskUniqueID
and task.WPROJ_ID = webProj.WPROJ_ID
and task.ProjectUniqueID = proj.ProjectUniqueID
where assnByDay.AssignmentTimeStart <= @FinalDate and
assnByDay.AssignmentTimeFinish >= @StartDate
order by ResourceName

but i'm can't find some projects

I'm tryed to get it from MSP_Web tables

declare @res_name as nvarchar(255)
declare @assn_id as int
declare @assnPM_id as int
declare @total_days as int
declare @td_value as int
declare @td_start as datetime
declare @ApprovalStatus as int

declare @ActualPendingValue as decimal(25,6)--ожидающие акцепта трудозатраты
declare @ActualApprovedValue as decimal(25,6)--акцептованные трудозатраты

declare @td_cur_date as datetime
declare @project_name as nvarchar(510)
declare @task_name as nvarchar(510)
declare @pm_name as nvarchar(255)

--возвращаемые запланированные данные
declare @ScheduledReturnValuesDataTable table(
TaskDate datetime,
Value decimal(25,9),
TaskName nvarchar(510),
ProjectName nvarchar(255),
ResourceName nvarchar(255),
PM nvarchar(255))
--возвращаемые отчетные данные
declare @ActualReturnValuesDataTable table(
TaskDate datetime,
PendingValue decimal(25,9),
AprrovedValue decimal(25,9),
TaskName nvarchar(510),
ProjectName nvarchar(255),
ResourceName nvarchar(255),
PM nvarchar(255))


declare td cursor for
select distinct
a.WASSN_ID,
a. WRES_ID_MGR,
w.WWORK_START,
r.RES_NAME,
datediff(day, WWORK_START, WWORK_FINISH)+1,
w.WWORK_VALUE,
w.WWORK_UPDATE_STATUS
from
dbo.MSP_WEB_WORK as w
inner join dbo.MSP_WEB_ASSIGNMENTS as a
on a.WASSN_ID = w.WASSN_ID
inner join dbo.MSP_WEB_RESOURCES as r
on a.WRES_ID = r.WRES_ID
inner join dbo.MSP_VIEW_PROJ_RES_ENT as e
on r.WRES_RBS_UID = e.ResourceEnterpriseOutlineCode30ID
where
w.WWORK_TYPE = @WorkType
and ( @StartDate <= WWORK_FINISH and @FinalDate >= WWORK_START)
and e.ResourceEnterpriseOutlineCode30ID <> -1
and e.ResourceEnterpriseOutlineCode30ID <> 310
and r.WRES_IS_ENABLED = 1
and r.WRES_CAN_LOGIN = 1
and SUBSTRING(e.ResourceEnterpriseText1, 0, 255) = @Department
order by WWORK_START


-- вытаÑкиваем данные из курÑора
open td
fetch next from td into @assn_id, @assnPM_id, @td_start, @res_name,
@total_days, @td_value, @ApprovalStatus
while @@fetch_status <> -1
begin
select @td_cur_date = @td_start
while @total_days > 0
begin

select @task_name =
( select TASK_NAME
from MSP_WEB_ASSIGNMENTS
where WASSN_ID = @assn_id )

select @project_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 )

select @pm_name =
( select RES_NAME from dbo.MSP_WEB_RESOURCES where WRES_ID = @assnPM_id)

--еÑли запрашиваютÑÑ Ð·Ð°Ð¿Ð»Ð°Ð½Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð½Ñ‹Ðµ данные
if @WorkType = 0
begin
insert @ScheduledReturnValuesDataTable values ( @td_cur_date, @td_value,
@task_name, @project_name, @res_name, @pm_name)
end
else--отчетные
begin
set @ActualPendingValue = 0
set @ActualApprovedValue = 0
if @ApprovalStatus = 0
begin
set @ActualApprovedValue = @td_value
end
else
set @ActualPendingValue = @td_value
insert @ActualReturnValuesDataTable values ( @td_cur_date,
@ActualPendingValue, @ActualApprovedValue,
@task_name, @project_name, @res_name, @pm_name)
end
select @td_cur_date = DATEADD(d, 1, @td_cur_date)
select @total_days = @total_days - 1
end
-- возвращаем Ñледующую Ñтроку из курÑора
fetch next from td into @assn_id, @assnPM_id, @td_start, @res_name,
@total_days, @td_value, @ApprovalStatus
end
close td
deallocate td

--еÑли запрашиваютÑÑ Ð·Ð°Ð¿Ð»Ð°Ð½Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð½Ñ‹Ðµ данные
if @WorkType = 0
begin
select
ProjectName,
TaskName,
PM,
ResourceName,
TaskDate,
SUM(Value/60000) as Value
from
@ScheduledReturnValuesDataTable
group by ProjectName, TaskName, ResourceName, TaskDate, PM
order by ResourceName
end
else--отчетные
begin
select
ProjectName,
TaskName,
PM,
ResourceName,
TaskDate,
SUM(PendingValue/60000) as PendingValue,
SUM(AprrovedValue/60000) as AprrovedValue
from
@ActualReturnValuesDataTable
group by ProjectName, TaskName, ResourceName, TaskDate, PM
order by ResourceName
end
but i'm can't find some projects too

 

[Grigory Bushuev]

I'm made it for day, you helped me!

select substring(projEnt.ProjectEnterpriseName, 4,
len(projEnt.ProjectEnterpriseName)) ProjectName, task.TaskName,
res.ResourceName, AssignmentTimeActualWork/60000
from MSP_VIEW_PROJ_ASSN_TP_BY_DAY assnByDay
inner join MSP_VIEW_PROJ_PROJECTS_STD proj
on assnByDay.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_ASSN_STD assnStd
on assnByDay.AssignmentUniqueID = assnStd.AssignmentUniqueID
inner join MSP_WEB_PROJECTS webProj
on assnByDay.WPROJ_ID = webProj.WPROJ_ID
and proj.WPROJ_ID = webProj.WPROJ_ID
and assnStd.WPROJ_ID = webProj.WPROJ_ID
and assnStd.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_RES_STD res
on assnStd.ResourceEnterpriseUniqueID = res.ResourceEnterpriseUniqueID
and assnStd.ResourceUniqueID = res.ResourceUniqueID
and res.WPROJ_ID = webProj.WPROJ_ID
and res.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_TASKS_STD task
on assnStd.TaskUniqueID = task.TaskUniqueID
and task.WPROJ_ID = webProj.WPROJ_ID
and task.ProjectUniqueID = proj.ProjectUniqueID
inner join MSP_VIEW_PROJ_PROJECTS_ENT projEnt
on projEnt.WPROJ_ID = proj.WPROJ_ID
and projEnt.ENT_ProjectUniqueID = proj.ProjectUniqueID

where assnByDay.AssignmentTimeStart <= @FinalDate and
assnByDay.AssignmentTimeFinish > @StartDate
and AssignmentTimeActualWork > 0
order by ResourceName

 

[Rod Gill]

Excellent. By having that code in a SQL Server View, then it's easy to
translate to use Project Server 2007 reporting database when you upgrade and
so make the transition smoother.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Maurício Kobren]

Hi,

I'm buildind a report in Project 2003 tables to see the hours that the team
worked last week. I don´t use de cube because I need to approve all the task
updates.

I'm using this script:
SELECT
td.WPROJ_ID,
r.WRES_ID,
r.RES_NAME,
a.TASK_NAME,
(td.AssignmentTimeWork/480000) AS 'Plan',
(td.AssignmentTimeActualWork/480000) AS 'Real',
Convert(Varchar,Convert(DateTime,td.AssignmentTimeStart,121),103) AS 'Start',
Convert(Varchar,Convert(DateTime,td.AssignmentTimeFinish,121),103) AS
'Finish'

FROM
msp_view_proj_assn_tp_by_day td,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_RESOURCES r
WHERE
(a.WPROJ_ID=td.WPROJ_ID) AND
(a.ASSN_UID=td.AssignmentUniqueID) AND
(a.WRES_ID=r.WRES_ID) AND
(r.WRES_IS_ENABLED = 1 ) AND
(td.AssignmentTimeStart Between '2007-05-7 00:00:00' And '2007-05-11
00:00:00')
ORDER BY
td.AssignmentTimeStart

The SELECT works good, but it has a problem: the data returned don´t has the
task updates that I didn´t approve.
Where or how I can access this task updates?

Tks,

Maurício Kobren