Project - Timesheet Custom Fields in Reporting Database MSPS 2007

Asked By Gary Zag

04-Feb-10 07:06 PM

I added custom enterprise fields to a timesheet view.  Where is the data in
these fields stored in the reporting database?

04-Feb-10 07:30 PM

Custom Fields do not "belong" to a timesheet as far as I am aware, they live
with Task and Resources. If you created and displayed a Task Custom field,
look in the Views in the reporting db for MSP_EpmTask_UserView

All views ending with userview have relevant custom fields automatically
appended to them.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com





__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com

05-Feb-10 03:25 AM

a in

We have done a custom report (EPM 2007) that shows timesheet data and
custom fields values entered at project, resource and task level.
We created a SQL stored procedure, that creates a CSV file, with
joints between timesheets lines and projects, task and resource
tables, through the assignments table (on reporting DB).
Just a warning on an issue we are currently fixing; in some cases, the
sync with publish and reporting can fail; perhaps it is better to
gather data from publish DB...

Regards
Jeff

05-Feb-10 02:54 AM

You need to get this fields in the way Rod describes. But be careful, there
was (is?) a bug in timesheet tables regarding TaskUID.

MSP_TimesheetTask_OlapView.TaskUID is wrong in some cases so you need to
link MSP_EpmTask_UserView with AssignmentUID via MSP_EpmAssignment_UserView.
I can remember that there was a correcting in some update regarding timesheet
tables but I am not aware if that one was solved. Nevertheless it is still
the same for timesheets created before that.

You can verify if your data is affected by that with following statement:
SELECT dbo.MSP_TimesheetTask_OlapView.TaskUID AS TASKUID_Timesheet,
dbo.MSP_EpmTask_UserView.TaskUID AS TASKUIDviaAssignmentUID FROM
dbo.MSP_TimesheetLine_OlapView INNER JOIN dbo.MSP_TimesheetTask_OlapView ON
dbo.MSP_TimesheetLine_OlapView.TaskNameUID =
dbo.MSP_TimesheetTask_OlapView.TaskNameUID INNER JOIN
dbo.MSP_EpmAssignment_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON
dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID ON
dbo.MSP_TimesheetLine_OlapView.AssignmentUID =
dbo.MSP_EpmAssignment_UserView.AssignmentUID AND
dbo.MSP_TimesheetTask_OlapView.TaskUID <> dbo.MSP_EpmTask_UserView.TaskUID

08-Feb-10 07:45 AM

Not sure if this is exactly what you are after but have a look in the
reporting database at the following:

dbo.MSP_EpmAssignment_UserView

Resource entity custom fields are suffixed with '_R' and task entity custom
fields are suffixed with '_T'

This information can be linked to timesheet data to provide a link to
assignment custom fields.

Regards,

Piet Remen
http://www.projectserver.com.au