Timesheet cube negative values Project Server 2007

[marie18]

Hi everyone,

We are using Project Server 2007 SP2. When I generate a report from the
Timesheet cube (in Excel or Data Analysis) to get the working billable and
non-billable hours per period, per task, per resource I get negative values
in the report. So far we found out the reason for the negative values is the
correction by the timesheet manager. However the corrected hours in the
timesheet are different than the hours generated in the report.

Has anyone had any issues with this? As we are struggling with this for a
couple of days now I will very much appreciate any help.

Thanks in advance,

Maria

 

[Barbara - Austria]

Hi Maria,
I haven’t seen that issue so far, but our customers are nearly not using
adjustments following a different process. We ourselves are using very
limited that, but I have never had that issue.

First let me explain shortly what happens in Project Server:
Creating or modifying a timesheet will start queue job types ‘Reporting
(Timesheet …)’. They are populating your changes to Reporting DB and you can
find this data in views Timesheet*_OlapView. Went these jobs through without
errors?
Any modification will create additional records with adjustment IDs as I
explained in the other forum. The newest record will always have an
AdjustmentUID= 00000000-0000-0000-0000-000000000000
Building a cube will take these data to re-create your Analysis Datebase.
Was your build job successful?

Unfortunately I don’t know how cube building is working in detail: Does it
take the record with = 00000000-0000-0000-0000-000000000000 or is it
building a sum of all hours of that timesheetline for a certain date?

I am not sure what data you want to access when writing ‘Excel or Data
Analysis’. However, I think first of all you should check your data in
Reporting DB.

To find the record negative hours with AdjustmentUID =
00000000-0000-0000-0000-000000000000 you can use this query:
SELECT dbo.MSP_TimesheetActual_OlapView.TimeByDay,
dbo.MSP_TimesheetResource_OlapView.ResourceName,
dbo.MSP_TimesheetProject_OlapView.ProjectName,
dbo.MSP_TimesheetTask_OlapView.TaskName,
dbo.MSP_TimesheetPeriod_OlapView.PeriodName,
dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable AS
MostRecentActualWorkBillable,
dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID,
dbo.MSP_TimesheetActual_OlapView.AdjustmentUID FROM
dbo.MSP_TimesheetResource_OlapView INNER JOIN dbo.MSP_Timesheet_OlapView ON
dbo.MSP_TimesheetResource_OlapView.ResourceNameUID =
dbo.MSP_Timesheet_OlapView.OwnerResourceNameUID INNER JOIN
dbo.MSP_TimesheetLine_OlapView ON dbo.MSP_Timesheet_OlapView.TimesheetUID =
dbo.MSP_TimesheetLine_OlapView.TimesheetUID INNER JOIN
dbo.MSP_TimesheetActual_OlapView ON
dbo.MSP_TimesheetLine_OlapView.TimesheetLineUID =
dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetPeriod_OlapView ON dbo.MSP_Timesheet_OlapView.PeriodUID =
dbo.MSP_TimesheetPeriod_OlapView.PeriodUID INNER JOIN
dbo.MSP_TimesheetProject_OlapView ON
dbo.MSP_TimesheetLine_OlapView.ProjectNameUID =
dbo.MSP_TimesheetProject_OlapView.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetTask_OlapView ON dbo.MSP_TimesheetLine_OlapView.TaskNameUID
= dbo.MSP_TimesheetTask_OlapView.TaskNameUID WHERE
(dbo.MSP_TimesheetActual_OlapView.AdjustmentUID =
'00000000-0000-0000-0000-000000000000') AND
(dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable < 0)
Are there any records listed?

With the next one you can check if any sum of actuals is negative:
SELECT dbo.MSP_TimesheetActual_OlapView.TimeByDay,
dbo.MSP_TimesheetResource_OlapView.ResourceName,
dbo.MSP_TimesheetProject_OlapView.ProjectName,
dbo.MSP_TimesheetTask_OlapView.TaskName,
dbo.MSP_TimesheetPeriod_OlapView.PeriodName,
SUM(dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable) AS
SumOfACtualWorkBillable, dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID
FROM dbo.MSP_TimesheetResource_OlapView INNER JOIN
dbo.MSP_Timesheet_OlapView ON
dbo.MSP_TimesheetResource_OlapView.ResourceNameUID =
dbo.MSP_Timesheet_OlapView.OwnerResourceNameUID INNER JOIN
dbo.MSP_TimesheetLine_OlapView ON dbo.MSP_Timesheet_OlapView.TimesheetUID =
dbo.MSP_TimesheetLine_OlapView.TimesheetUID INNER JOIN
dbo.MSP_TimesheetActual_OlapView ON
dbo.MSP_TimesheetLine_OlapView.TimesheetLineUID =
dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetPeriod_OlapView ON dbo.MSP_Timesheet_OlapView.PeriodUID =
dbo.MSP_TimesheetPeriod_OlapView.PeriodUID INNER JOIN
dbo.MSP_TimesheetProject_OlapView ON
dbo.MSP_TimesheetLine_OlapView.ProjectNameUID =
dbo.MSP_TimesheetProject_OlapView.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetTask_OlapView ON dbo.MSP_TimesheetLine_OlapView.TaskNameUID
= dbo.MSP_TimesheetTask_OlapView.TaskNameUID GROUP BY
dbo.MSP_TimesheetActual_OlapView.TimeByDay,
dbo.MSP_TimesheetResource_OlapView.ResourceName,
dbo.MSP_TimesheetProject_OlapView.ProjectName,
dbo.MSP_TimesheetTask_OlapView.TaskName,
dbo.MSP_TimesheetPeriod_OlapView.PeriodName,
dbo.MSP_TimesheetActual_OlapView.TimesheetLineUID HAVING
(SUM(dbo.MSP_TimesheetActual_OlapView.ActualWorkBillable) < 0)
Are there any records listed?

If you found negative records with any of these statements, you could force
a rebuild of Reporting DB. Do that outside of normal working hours, it will
slow down your system. Do an Administrative Backup of ‘Enterprise Custom
Fields’ and a restore of them if job is finished.

If you haven’t any negative records, I suggest to create a new cube (Server
Settings – Build Settings – new name in ‘Analysis Datebase to be created’)
and see if data is correct in that one.

Let us know what you found, to help to resolve!
Regards
Barbara

 

[marie18]

Hi Barbara,

thanks for looking into this. I ran both queries and they returned 0 rows.
However it appeared that the negative values appear for the line where
AdjustmentUID <> '00000000-0000-0000-0000-000000000000'. Actually the
manager who edited the timesheet of a user gets assigned the negative values
of the hours. For example if I put 8 billable hours per day and my manager
corrects then to 4 billable and 4 non-billable, the result is

Maria 8 billable, 0 nonbillable
Manager -8 billable, 0 nonbillable
Manager 4 billable, 4 nonbillabe

What happens is that the hours are assigned to the manager who edited and
then approved the report...This is way we get incorrect results in the
report..

Is there a way to avoid this?

Thank you,

Maria

 

[Barbara - Austria]

Hi Maria,

first of all I need to apologize. My assumption to be the record with
AdjustmentUID=000.. being the most recent one was wrong. Sorry! I mixed up
how many hours I used for which step.

I have not being successful to reproduce your issue on my system. Are you
able to reproduce or are we talking about something what happened within a
certain timeframe?

Regarding your example: are you sure that you are using
MSP_Timesheet_OlapView.OwnerResourceNameUID and not
MSP_TimesheetActual_OlapView.LastChangedResourceNameUID? You call it a
report and have not answered my question how you are accessing your data
(Analysis View, Analysis Database, Reporting DB).

What is your patch level? I am working on SP2 + CU December. If you have a
lower level, I suggest updating as soon as possible. There are some updates
for timesheets. None of them is exactly describing your problem, but some of
them deal with mixing up resource and timesheet manager or adjustments.

This time more questions than suggestions. Let me know, perhaps I get an
idea with some more information.
Regards
Barbara

 

[marie18]

Hi Barbara,

the issue is repeating. It did not happen just once. For creating a report
we weither use Excel or directly browsing the cubes in Analysis Services 2008.

I am not sure what you mean by that:" Regarding your example: are you sure
that you are using MSP_Timesheet_OlapView.OwnerResourceNameUID and not
MSP_TimesheetActual_OlapView.LastChangedResourceNameUID?" beause I actually
simply browse the cube.

We are using SP2, I tried finding a link on Microsoft download for download
of the December CU update but did not find any. If you could please let me
know where can I find it

Thanks,

Maria

 

[Barbara - Austria]

Hi Maria,

ok, thanks for your information. Directly browsing the cube shows correct
values in my case.

You can find CU December with following links, but CU February is already
available. (I have not applied so far):
http://support.microsoft.com/kb/977027,
http://support.microsoft.com/kb/977026,
http://support.microsoft.com/kb/977022,
http://support.microsoft.com/kb/977028,
http://support.microsoft.com/kb/977266. I cross my fingers that it will
help!

I am awful sorry, I ran out of ideas now! Good luck!
Regards
Barbara

Ps.: Since I was not sure if you are using Reporting DB directly, I
mentioned the 2 *ResourceNameUIDs. Sorry for confusion.

 

[Gary L. Chefetz]

Marie:

There are a couple issues with the cube and timesheets. One of them,
regarding Surrogate timesheets, is explained on my site:

http://www.projectserverexperts.com...heet Creator not Timesheet Owner in Cube.aspx

You may be seeing yet another definition problem. See if the latest CU
doesn't cover this, as Barbara suggested. If not, you might want to consider
querying the RDB directly for this type of data.