Project server 2003 SP1 task level cost report

K

Kiran.ac

Hi,

We have project server 2003 SP1. I am trying to get a report on cost of each
project with cost being shown for different levels of tasks with cost roll up
happening to the super level. I would want the proj_name,task_wbs,task_name
and the cost to appear in the report which is a cross tab crystal report. As
of now I am using the following query to get the actuals but the problem is
this query does not show all the tasks available and I have failed in getting
the cost roll up to the super level. So is there any way by which i can
modify or get a new query that will help me achieve the report.

Query:

select r.RES_NAME,
p.PROJ_NAME,
a.TASK_NAME,
w.WWORK_START,
w.WWORK_FINISH,
w.WWORK_VALUE,
k.RES_STD_RATE,
CONVERT(CHAR(25),q.ResourceGroup) AS ResourceGroup,
p.PROJ_ID,
p.WPROJ_ID,
a.TASK_ID,
a.TASK_UID,
t.task_outline_level,
t.task_wbs,
t.task_is_summary
from MSP_WEB_RESOURCES r left outer join MSP_RESOURCES k
on r.RES_EUID = k.RES_EUID,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_PROJECTS p,
MSP_WEB_WORK w,
MSP_VIEW_RES_STD q,
MSP_TASKS t
where w.WWORK_TYPE = 1 -- actual work
and w.WASSN_ID = a.WASSN_ID
and a.WPROJ_ID = p.WPROJ_ID
and a.WRES_ID = r.WRES_ID
and q.ResourceName = r.RES_NAME
and t.TASK_UID = a.TASK_UID
and t.PROJ_ID = p.proj_id
and ((w.WWORK_START between @startdate and @enddate) or (w.WWORK_FINISH
between @startdate and @enddate))
and w.WWORK_UPDATE_STATUS = 0 AND k.PROJ_ID = 1 and a.WASSN_DELETED_IN_PROJ
= 0 --and r.WRES_IS_ENABLED = 1
order by 1, 2, 3, 4

Thanks in advance and any ideas will be helpful as I am completely stuck now.
 
Top