Timesheet and Timesheet Lines approval statuses

[Konstantinos Christodoulakis]

Dear all,

I have the following scenario and question:

Employee1 filled-in the actual hours for Week-1 (01.01.2010-08.01.2010)
having 5 Timesheet Lines (for different tasks) and submitted them to his
TimesheetManager1.

TimesheetManager1 approved it and the whole Timesheet appears as "Approved".

QUESTIONS:

1. In which table of ProjectServer_Published database can I find the
Timesheet Approval Statuses?

2. Do the TimesheetLines having different statuses during approval workflow?
Where are they stored (as statuses) in ProjectServer_Published database?

3. ASSUMPTION: My example Timesheet has 5 TimesheetLines inside the database
and every TimesheetLine has an approval status.
Is that right?

Hope to have answer to this question.

Kind regards,

Konstantinos Christodoulakis
Senior Project Manager

 

[Jonathan Sofer [MVP]]

Hi Konstantinos,

I suggest your pull your timesheet data from the reporting database. The
data is laid out more cleanly there and is supported by Microsoft to report
off of the reporting database only.

To answer your questions:
1) Timesheet status is found in the field
dbo.MSP_TimesheetStatus.Description. The query below run on the reporting
database will return timesheet line items and other important related
timesheet data like timesheet status and timesheet approvers etc:
SELECT dbo.MSP_TimesheetProject.ProjectUID,
dbo.MSP_TimesheetProject.ProjectName, dbo.MSP_TimesheetResource.ResourceUID,
dbo.MSP_TimesheetResource.ResourceName,
dbo.MSP_TimesheetActual.TimeByDay,
dbo.MSP_TimesheetActual.TimeByDay_DayOfWeek,
dbo.MSP_TimesheetActual.TimeByDay_DayOfMonth,
dbo.MSP_TimesheetActual.ActualWorkBillable,
dbo.MSP_TimesheetPeriod.PeriodName,
dbo.MSP_TimesheetPeriod.StartDate,
dbo.MSP_TimesheetPeriod.EndDate, YEAR(dbo.MSP_TimesheetPeriod.EndDate) AS
PeriodEndYear,
{ fn QUARTER(dbo.MSP_TimesheetPeriod.EndDate) } AS
PeriodEndQuarter, MONTH(dbo.MSP_TimesheetPeriod.EndDate) AS PeriodEndMonth,
dbo.MSP_TimesheetPeriodStatus.Description AS
PeriodStatus, dbo.MSP_TimesheetStatus.Description AS TimeSheetStatus,
dbo.MSP_TimesheetActual.ActualWorkNonBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkNonBillable,
dbo.MSP_TimesheetActual.PlannedWork,
dbo.MSP_TimesheetStatus.TimesheetStatusID,
dbo.MSP_TimesheetPeriodStatus.PeriodStatusID,
dbo.MSP_TimesheetLine.TimesheetUID,
dbo.MSP_EpmResource_UserView.ResourceStandardRate,
dbo.MSP_EpmResource_UserView.ResourceStandardRate *
dbo.MSP_TimesheetActual.ActualWorkBillable AS ActualCostBillable,
dbo.MSP_TimesheetLine.ValidationType,
dbo.MSP_TimesheetTask.TaskUID, dbo.MSP_TimesheetTask.TaskName,
dbo.MSP_EpmResource_UserView.ResourceTimesheetManagerUID,
MSP_EpmResource_UserView_1.ResourceName AS
TimesheetApprover, dbo.MSP_EpmResource_UserView.RBS,
dbo.MSP_TimesheetPeriod.PeriodUID
FROM dbo.MSP_TimesheetResource INNER JOIN
dbo.MSP_Timesheet INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID
= dbo.MSP_TimesheetPeriod.PeriodUID INNER JOIN
dbo.MSP_TimesheetPeriodStatus ON
dbo.MSP_TimesheetPeriod.PeriodStatusID =
dbo.MSP_TimesheetPeriodStatus.PeriodStatusID ON
dbo.MSP_TimesheetResource.ResourceNameUID =
dbo.MSP_Timesheet.OwnerResourceNameUID INNER JOIN
dbo.MSP_TimesheetStatus ON
dbo.MSP_Timesheet.TimesheetStatusID =
dbo.MSP_TimesheetStatus.TimesheetStatusID INNER JOIN
dbo.MSP_TimesheetProject INNER JOIN
dbo.MSP_TimesheetLine ON
dbo.MSP_TimesheetProject.ProjectNameUID =
dbo.MSP_TimesheetLine.ProjectNameUID ON
dbo.MSP_Timesheet.TimesheetUID =
dbo.MSP_TimesheetLine.TimesheetUID INNER JOIN
dbo.MSP_EpmResource_UserView ON
dbo.MSP_TimesheetResource.ResourceUID =
dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN
dbo.MSP_TimesheetTask ON
dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID LEFT
OUTER JOIN
dbo.MSP_EpmResource_UserView AS
MSP_EpmResource_UserView_1 ON
dbo.MSP_EpmResource_UserView.ResourceTimesheetManagerUID
= MSP_EpmResource_UserView_1.ResourceUID LEFT OUTER JOIN
dbo.MSP_TimesheetActual ON
dbo.MSP_TimesheetLine.TimesheetLineUID =
dbo.MSP_TimesheetActual.TimesheetLineUID
WHERE (dbo.MSP_TimesheetActual.ActualWorkBillable IS NOT NULL)

2 & 3) Regular project task timesheet line items do not have separate
statuses. There is only one status for the entire timesheet. The exception
would be if you are using administrative line items. Those would have
separate line item approvals and therefore separate statuses.

Hope this helps,

Jonathan

"Konstantinos Christodoulakis"

 

[Lars Hammarberg]

When retreiving taskuid (and projectuid) from the msp_timesheettask and
msp_timesheetproject tables, remember that the tables are recursive - that
is, in order to find the correct uid corresponding to the uid's in the
other (non-timesheet) project/task tables, you'll have to traverse the
msp_timesheettask table recursively by way of tasknameuid-->parentnameuid
all the way up to the entry in which both the tasknameuid and the
parenttasknameuid are one and the same. That entry will give you the correct
taskuid which will correspond to a taskuid in - say - the MSP_EpmTask table.
All other taskuids (for the rows in which the tasknameuid and
parenttasknameuid are not the same) are totally fictuous - i.e.: there's no
corresponding guid in any other table anywhere in the universe

Bear in mind, though, that the timesheet tables are the only tables in the
reporting database in which you could find the same assignmentuid used for
more than one resourceuid or taskuid.
If you experiment with re-assigning an new resource to an existing task
after the first resource have created a timesheetline entry for the original
assignment, you'll see what I mean... the original assignmentuid (which
mapped to the assignmentuid used in the non-timesheet tables) is being
re-used for another combination of taskuid/resourceuid. In other words -
there's nothing 'unique' in this assignmentuid.

In other words - you can't with 100% accuracy map a timesheet entry to the
very task and/or resource it once was created for.

--

/Lars Hammarberg
www.camako.se
Microsoft Gold Certified Partner