I found a workaround for my problem and I will post it here, as - admittedly
a hacky one - may help some of people reading this newsgroup. And perhaps
someone will improve it even farther.
Sine I already spent too much time on analysis of that topic I decided to go
thorugh a relatively safe way and instead of modifying the cube definitions,
extending them etc. I found that the MSP_Project_Timesheet cube that keeps
all the most important parameters of my projects (including all enterprise
custom fields attached to the tasks), extracts the Work, Actual Work (and all
other work parameters) from a database _view_ MSP_EmpAssignmentByDay_OlapView
(in the ProjectServer_Reporting database).
Since (as I already wrote before) in case of my organization it is not
important to have the data from real Actual Work (as they come from (optional
in our case) task approvals, NOT from Timesheets, I decided to modify that
view definition and draw the data not from the EmpAssignmentByDay but from
the timesheets.
In this way I kept the "interface" which is the metadata of the view but
modified it's content (so to speak) but substituting different source data.
The cubes however would not see any difference (and this is the _very_
beauty).
I edited the vew definition using the SQL Server Management Studio and
(having made a backup copy of the original) I entered the code in the end of
this post.
Please note, that the original code was brutally mutilated and stripped from
all the parameters I do not need at the moment (as baselines, costs etc.), so
this is not a solution for everyone. But it gives an idea of how to do taht
properly (I hope).
The end result is, that now my reports work great, as (as I wrote in one of
my initial posts) Project Server Definitions of the cubes provide all
possible intesections etc, of the Work/Actual Work (which through the SQL
below I subsitituted with Actual Work Billable from the timesheets) vs. all
task enterprise custom fields.
Which could not be said about original Actual Work Billable ws. these
fields...
And so I am very happy with my new reports. I hope that will be of some help
or source of inspiration for someone else
The new (awfully mutilated) code for modified
MSP_EmpAssignmentByDay_OlapView follows. Have fun.
Pawel
--- Code ---
SELECT tl.AssignmentUID AS AssignmentUID,
ta.TimeByDay AS TimeByDay,
tp.ProjectUID AS ProjectUID,
tt.TaskUID AS TaskUID,
0 AS AssignmentCost,
0 AS AssignmentOvertimeCost,
0 AS AssignmentActualCost,
0 AS AssignmentActualOvertimeCost,
ISNULL(ta.PlannedWork, 0) AS AssignmentWork,
0 AS AssignmentOvertimeWork,
ISNULL(ta.ActualWorkBillable, 0) AS AssignmentActualWork,
0 AS AssignmentActualOvertimeWork,
0 AS AssignmentMaterialWork,
0 AS AssignmentMaterialActualWork,
0 AS AssignmentBudgetCost,
0 AS AssignmentBudgetWork,
0 AS AssignmentBudgetMaterialWork,
0 AS AssignmentResourcePlanWork,
0 AS AssignmentRegularCost,
0 AS AssignmentRemainingCost,
0 AS AssignmentRemainingOvertimeCost,
0 AS AssignmentActualRegularCost,
0 AS AssignmentRemainingRegularCost,
0 AS AssignmentRegularWork,
0 AS AssignmentRemainingWork,
0 AS AssignmentRemainingOvertimeWork,
0 AS AssignmentActualRegularWork,
0 AS AssignmentRemainingRegularWork,
ISNULL(dbo.MSP_EpmAssignment.ResourceUID,
'B067CE64-9588-4B18-BF54-3491F76419C9') AS ResourceUID,
ISNULL(dbo.MSP_EpmAssignment.ResourceOwnerUID,
'B067CE64-9588-4B18-BF54-3491F76419C9') AS ResourceOwnerUID,
0 AS AssignmentBaseline0Cost, 0 AS AssignmentBaseline0Work,
0 AS AssignmentBaseline0MaterialWork, 0 AS
AssignmentBaseline0BudgetCost, 0 AS AssignmentBaseline0BudgetWork,
0 AS AssignmentBaseline0BudgetMaterialWork, 0 AS
AssignmentBaseline1Cost, 0 AS AssignmentBaseline1Work,
0 AS AssignmentBaseline1MaterialWork, 0 AS
AssignmentBaseline1BudgetCost, 0 AS AssignmentBaseline1BudgetWork,
0 AS AssignmentBaseline1BudgetMaterialWork, 0 AS
AssignmentBaseline2Cost, 0 AS AssignmentBaseline2Work,
0 AS AssignmentBaseline2MaterialWork, 0 AS
AssignmentBaseline2BudgetCost, 0 AS AssignmentBaseline2BudgetWork,
0 AS AssignmentBaseline2BudgetMaterialWork, 0 AS
AssignmentBaseline3Cost, 0 AS AssignmentBaseline3Work,
0 AS AssignmentBaseline3MaterialWork, 0 AS
AssignmentBaseline3BudgetCost, 0 AS AssignmentBaseline3BudgetWork,
0 AS AssignmentBaseline3BudgetMaterialWork, 0 AS
AssignmentBaseline4Cost, 0 AS AssignmentBaseline4Work,
0 AS AssignmentBaseline4MaterialWork, 0 AS
AssignmentBaseline4BudgetCost, 0 AS AssignmentBaseline4BudgetWork,
0 AS AssignmentBaseline4BudgetMaterialWork, 0 AS
AssignmentBaseline5Cost, 0 AS AssignmentBaseline5Work,
0 AS AssignmentBaseline5MaterialWork, 0 AS
AssignmentBaseline5BudgetCost, 0 AS AssignmentBaseline5BudgetWork,
0 AS AssignmentBaseline5BudgetMaterialWork, 0 AS
AssignmentBaseline6Cost, 0 AS AssignmentBaseline6Work,
0 AS AssignmentBaseline6MaterialWork, 0 AS
AssignmentBaseline6BudgetCost, 0 AS AssignmentBaseline6BudgetWork,
0 AS AssignmentBaseline6BudgetMaterialWork, 0 AS
AssignmentBaseline7Cost, 0 AS AssignmentBaseline7Work,
0 AS AssignmentBaseline7MaterialWork, 0 AS
AssignmentBaseline7BudgetCost, 0 AS AssignmentBaseline7BudgetWork,
0 AS AssignmentBaseline7BudgetMaterialWork, 0 AS
AssignmentBaseline8Cost, 0 AS AssignmentBaseline8Work,
0 AS AssignmentBaseline8MaterialWork, 0 AS
AssignmentBaseline8BudgetCost, 0 AS AssignmentBaseline8BudgetWork,
0 AS AssignmentBaseline8BudgetMaterialWork, 0 AS
AssignmentBaseline9Cost, 0 AS AssignmentBaseline9Work,
0 AS AssignmentBaseline9MaterialWork, 0 AS
AssignmentBaseline9BudgetCost, 0 AS AssignmentBaseline9BudgetWork,
0 AS AssignmentBaseline9BudgetMaterialWork, 0 AS
AssignmentBaseline10Cost, 0 AS AssignmentBaseline10Work,
0 AS AssignmentBaseline10MaterialWork, 0 AS
AssignmentBaseline10BudgetCost, 0 AS AssignmentBaseline10BudgetWork,
0 AS AssignmentBaseline10BudgetMaterialWork
FROM
dbo.MSP_TimesheetActual AS ta FULL OUTER JOIN
dbo.MSP_TimesheetLine AS tl ON ta.TimesheetLineUID =
tl.TimesheetLineUID FULL OUTER JOIN
dbo.MSP_TimesheetTask AS tt ON tl.TaskNameUID = tt.TaskNameUID
FULL OUTER JOIN
dbo.MSP_TimesheetProject tp ON tl.ProjectNameUID =
dbo.MSP_TimesheetProject.ProjectNameUID FULL OUTER JOIN
dbo.MSP_EpmAssignment ON
tl.AssignmentUID=dbo.MSP_EpmAssignment.AssignmentUID
:
[CUT]
