Tying Timesheet Tasks back to EPMTask Table

[Shawn Everingham]

I am attempting to correlate Timesheet Tasks to EPM Task Table.

We have a couple of Enterprise Custom fields that we track at the task
level. We pull our timesheet actuals for accounting purposes and need a way
to tie those actuals back to the Enterprise Custom Field for that task.

What we have found is that we cannot join the TimesheetTask_OlapView table
back to EPMTask table with the TaskNameUID, or the TaskUID, or the
ParentTaskNameUID. What gives? is there a cross reference look up table?

Where do the GUID's come from in the TimesheetTask table for TaskUIDs?

Thanks in advance!!

 

[Sander]

Hi Shawn,

I did something with this some time ago. Used the assignmentUID to get to
other tabels. From Assingment TaskUID you can get to Task table.
This is what I had:
SELECT ts.description, ta.ActualWorkBillable,
assact.AssignmentActualWork,
assact.AssignmentActualCost, assact.AssignmentUID,
assact.TimeByDay, assact.ProjectUID, assact.TaskUID
FROM MSP_EpmAssignmentByDay_UserView AS assact
INNER JOIN MSP_TimesheetLine AS tl ON assact.AssignmentUID =
tl.AssignmentUID
INNER JOIN MSP_TimesheetActual AS ta ON tl.TimesheetLineUID =
ta.TimesheetLineUID AND ta.TimeByDay = assact.TimeByDay
INNER JOIN MSP_timesheet AS t ON TL.timesheetUID = t.TimesheetUID
INNER JOIN MSP_timesheetStatus as ts ON t.timesheetstatusID =
ts.timesheetstatusID

hope this helps a little.
Sander

 

[Shawn Everingham]

Sander, thanks. I have been playing around with the assignment hook. I will
give this a more thorough shake down today using your query. I have found
that I have to do left joins as timesheet tasks don't always match up to
tasks...

Stay tuned! Thanks!
Shawn

 

[Shawn Everingham]

Sander, many thanks. That worked like a charm!!!! I"M IN BUSINESS NOW!!!!
SWEET!!!!