Surrogate Timesheets and Timesheet Modifications

[Toastman Tom]

We just started using EPM 2007 for tmesheet tracking within our group - I set
up tasks and task assignments along with reporting structure so we can more
accurately track and report on software cap each month.

Timesheets are completed by each employee and then forward based on
reporting structure to their manager for approval. Everything went great with
the following exceptions - am hoping to get some insight/advice.

1) Surrogate timesheets - 2 people were out on PTO so the manager created
surrorate timesheets for each individual. Instead of showing up under the
correct employee, the hours entered ended up being added to the managers
total hours for the month.

2) Editing timesheets - same issue, when the manager edits a submitted
timesheet the hours ended up in their total hours not the employees. We
worked around this by rejecting the timesheet and having the employee make
the corrections but one would think that both corrections and surrogate
timesheets would impact the intended employees information, not the manger
making the change.

Thanks for any input.

 

[Jonathan Sofer [MVP]]

Please clarify your reporting structure. Do you use Analysis Services or
Reporting Services for example? Where are you seeing the hours being
reported against the wrong user?

Thanks,

Jonathan

 

[Toastman Tom]

Results are showing up in a Data Anbalysis view I created which is based on
data in the default EPM Timesheet cube.

Fields in this initial version are:
Resource List, Task List (level 2 and level 3 tasks), Actual Work Billable
and then Time (financial period) as the final parameter.

I just tried it again this morning - created a test timesheet period,
created a surrogate timesheet for an employee who reports to me. All looks
good in the UI - employee name shows on the timesheet screen, I show as the
approver etc...

I entered hours against a task, hit save and submit etc... When I rebuild
the cube the entered hours show up under my name with nothing for the "real"
employee that they were entered on behalf of.

 

[Jonathan Sofer [MVP]]

Yes, it sounds like an issue with the cube design and surrogate timesheets.
Are you completely current on service packs and upgrades?

From my experience with writing SQL reports against the timesheet data, I
have seen that surrogate timesheets add a layer of complexity in the way
that the data is stored. My guess is that the microsoft team that was in
charge of designing and building the timesheet cube, didn't account for
that.

Jonathan Sofer

 

[Gary Chefetz]

Another frequent visitor to these NGs, Pawel, posted a solution last year:

modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providing the
name/ID of the person who last changed the report, it gives the owner of the
timesheet (what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
SELECT
ta.TimesheetLineUID as TimesheetLineUID,
ta.TimeByDay as TimeByDay,
t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
ta.CreatedDate as CreatedDate,
ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
ta.AdjustmentUID as AdjustmentUID,
ta.ActualWorkBillable as ActualWorkBillable,
ta.ActualWorkNonBillable as ActualWorkNonBillable,
ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
ta.PlannedWork as PlannedWork,
ta.Comment as Comment
FROM
MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID

 

[Toastman Tom]

Thanks. Will give this a shot and let you know the results. Thanks again.

Another frequent visitor to these NGs, Pawel, posted a solution last year:

modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providing the
name/ID of the person who last changed the report, it gives the owner of the
timesheet (what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
SELECT
ta.TimesheetLineUID as TimesheetLineUID,
ta.TimeByDay as TimeByDay,
t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
ta.CreatedDate as CreatedDate,
ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
ta.AdjustmentUID as AdjustmentUID,
ta.ActualWorkBillable as ActualWorkBillable,
ta.ActualWorkNonBillable as ActualWorkNonBillable,
ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
ta.PlannedWork as PlannedWork,
ta.Comment as Comment
FROM
MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID

Results are showing up in a Data Anbalysis view I created which is based
on
data in the default EPM Timesheet cube.

Fields in this initial version are:
Resource List, Task List (level 2 and level 3 tasks), Actual Work Billable
and then Time (financial period) as the final parameter.

I just tried it again this morning - created a test timesheet period,
created a surrogate timesheet for an employee who reports to me. All looks
good in the UI - employee name shows on the timesheet screen, I show as
the
approver etc...

I entered hours against a task, hit save and submit etc... When I rebuild
the cube the entered hours show up under my name with nothing for the
"real"
employee that they were entered on behalf of.

 

[Toastman Tom]

Worked perfectly. Thanks.

Another frequent visitor to these NGs, Pawel, posted a solution last year:

modify one of the ProjectServer_Reporting DB views
(MSP_TimesheetActual_OlapView ) in such a way, that instead of providing the
name/ID of the person who last changed the report, it gives the owner of the
timesheet (what could be expected).

The SQL code that works for us is pasted below, but please use it on your
own responsibility (and don't forget to make a backup of the actual
MSP_TimesheetActual_OlapView definition).

USE [ProjectServer_Reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MSP_TimesheetActual_OlapView]
AS
SELECT
ta.TimesheetLineUID as TimesheetLineUID,
ta.TimeByDay as TimeByDay,
t.OwnerResourceNameUID as LastChangedResourceNameUID, --This is important
ta.CreatedDate as CreatedDate,
ta.TimeByDay_DayOfWeek as TimeByDay_DayOfWeek,
ta.TimeByDay_DayOfMonth as TimeByDay_DayOfMonth,
ta.AdjustmentUID as AdjustmentUID,
ta.ActualWorkBillable as ActualWorkBillable,
ta.ActualWorkNonBillable as ActualWorkNonBillable,
ta.ActualOvertimeWorkBillable as ActualOvertimeWorkBillable,
ta.ActualOvertimeWorkNonBillable as ActualOvertimeWorkNonBillable,
ta.PlannedWork as PlannedWork,
ta.Comment as Comment
FROM
MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID = tl.TimesheetLineUID
FULL OUTER JOIN
dbo.MSP_Timesheet AS t ON tl.TimesheetUID = t.TimesheetUID

Results are showing up in a Data Anbalysis view I created which is based
on
data in the default EPM Timesheet cube.

Fields in this initial version are:
Resource List, Task List (level 2 and level 3 tasks), Actual Work Billable
and then Time (financial period) as the final parameter.

I just tried it again this morning - created a test timesheet period,
created a surrogate timesheet for an employee who reports to me. All looks
good in the UI - employee name shows on the timesheet screen, I show as
the
approver etc...

I entered hours against a task, hit save and submit etc... When I rebuild
the cube the entered hours show up under my name with nothing for the
"real"
employee that they were entered on behalf of.