Background Information
The default structure of the Project Server 2007 reporting database attributes timesheet data to the Timehseet Creator rather than Timesheet Owner. This causes actual work to be distributed correctly in all cases where the the Timesheet Owner and Timesheet Creator are one and the same, but this does not correctly support the distribution of actual work to the Timesheet Owner in the OLAP cube data for surrogate timesheets.
Resolution
The following solution was provided in the Microsoft Project Server news group by frequent poster Pawel. Please note that that altering the structure of the reporting database views is not supported by Microsoft, although extending the database is supported. Please take all necessary steps to recover to your starting state before attempting this solution. Make a backup of the original MSP_TimesheetActual_OlapView definition before using the following SQL to alter the structure:
Query to modify the ProjectServer_Reporting DB view (MSP_TimesheetActual_OlapView ) to provide the Timesheet Owner
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