Find Timesheet approver data from SQL

[Raja]

Hi

I am creating a report from the database. I need the table info, where
the approver for all timesheets are stored. Can anyone help?

Thanks,
Rajakanna

 

[Jonathan Sofer]

I believe it is in the reporting database under
dbo.MSP_Timesheet.OwnerResourceNameUID which needs to be joined then to the
dbo.MSP_TimesheetResource.ResourceNameUID which is then joined to
dbo.MSP_Resource.ResourceUID

You can download the schema definition for the reporting database among
other things here on the "Project 2007 SDK: Software Development Kit":
http://www.microsoft.com/downloads/...f9-7028-4b30-99a2-18cb1eed1abe&displaylang=en

 

[Raja]

Hi

I am using MS-Project Server 2003 and SQL Server as database. I
searched for the tables you mentioned below and could not find any of
them. Could you tell me that if you are referring to the same version
of MSPS. Your help will be most appretiated.

Regards,
Rajakanna

 

[Jonathan Sofer]

Sorry, you mentioned timesheets and approvers and so I automatically assumed
you meant MSPS 2007. The reason I assumed this is because MSPS 2003 doesn't
really have a concept of timesheets but more a concept of task updates. In
MSPS 2007 there are both timesheets and task updates which can be integrated
or separated out. It is a good idea for future postings to specify the
version you are working with when asking a question.

As far as your question:

In 2003 on the front end server you will find the schema files for the msp,
web and view tables. It is most likely located on the server under the
location:
C:\Program Files\Microsoft Office Project Server 2003\HELP\1033\PJSVRDB.HTM
C:\Program Files\Microsoft Office Project Server 2003\HELP\1033\PJDB.HTM

I think you can use the following table to find the approval information:

"MSP_WEB_WORK_APPROVAL"
This table stores all timesheet approvals for Project Server uses, including
who approved the timesheet entry, the assignment, start and finish dates,
and approval status.

Column Name Data Type Description
WAPPROVAL_ID integer The unique ID for the approved work entry.
WASSN_ID integer Refers to a valid ID in the MSP_WEB_ASSIGNMENTS table.
WRES_ID integer Refers to a valid ID in the MSP_WEB_RESOURCES table.
WPRD_START_DATE datetime The start date for the approved time period.
WPRD_FINISH_DATE datetime The finish date for the approved time period.
WRES_ID_APPROVER integer The ID for the resource who approved or rejected
the work entry; refers to a valid ID in the MSP_WEB_RESOURCES table.
WAPPROVAL_STATUS tinyint Indicates whether the work entry was approved or
rejected.0 Pending 1 Approved 2 Rejected
WAPPROVAL_NOTIFICATION_SENT tinyint Indicates whether a notification has
been sent regarding the work entry.

Hope this helps,

Jonathan Sofer