Timesheet data query

H

Harold

Hi

I am working with project server 2003 and I would like to
query the number of hours/day entered in the timesheet
(once approved, so I can reconcile this information with
an external source.
I would need to see resource name, resource_code (my code
number) date, hours
Any suggestions
Thanks

Harold
 
H

Harold

I found these tables that let you find total actual work
per day per resource. However, I am finding it
challenging to link these with the actual task

--- QUERY 1 DESCRIPTION: TOTAL HOURS PER DAY PER RESOURCE
PER PAY PERIOD
USE ProjectServer8205
Declare @PayPeriodStartDate DateTime
Declare @PayPeriodEndDate DateTime
Declare @ProjectUID as Int
Set @PayPeriodStartDate = '11/1/2003'
Set @PayPeriodEndDate = '11/15/2003'
Set @ProjectUID = 9

select a.Proj_UID, a.Res_EntrUID, b.ResourceName,
a.Time_ID, a.Time_Date, a.ASSN_ACTUALWORK
from dbo.MSP_CUBE_ASSN_FACT a, MSP_Cube_Resources b
where a.Res_EntrUID = b.RES_EntrUID
and a.Res_ENTRUID > 0
and a.Proj_UID = @ProjectUID
and a.Time_Date between @PayPeriodStartDate and
@PayPeriodEndDate
order by b.ResourceName asc, a.Time_Date asc
 
D

David Gage

The problem you are running into is that you are using the MSP_CUBE tables,
which don't have task level information. In my work with the MSP_CUBE and
MSP_VIEW tables, I have found it very cumbersome to reconcile that data with
the MSP or MSP_WEB tables. My recommendation is to work with the MSP or
MSP_WEB tables if at all possible. Or look at the MSP_VIEW tables as they
do have some task info.

Some resources that might help you are the PROJDB.HTM file on the Project
Pro CD and the SVRDB.HTM file on the Project Server CD.

David
 
Top