Project Server Help Blog

This issue comes up from time to time in the Project Server news group. 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 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.

The following solution was provided in the Microsoft Project Server news group by frequent poster Pawel. It is also posted in our FAQ Pages. 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