Recently, I had a client who was interested in using the timesheet comments for tracking additional details about work done for their clients. The client was using timesheets for billing and wanted their consultants to provide additional details about the work done using comments. In their particular case timesheet comments met their requirements, and at the time I thought it was great solution. However, after playing with the solution, I discovered that many factors can cause the comments to be overridden.
It turns out that when a project manager or a timesheet manager approves time, the manager is prompted to send a comment back to the resource. It is at this point that the project manager’s comment or blank comment overwrites the existing comments. For my client, the solution worked well because they were not using the status field. However, most of my clients do, so I decided to dig deeper into this issue.
I discovered that the server configuration was also playing a big part in how and when timesheet comments were overwritten. The following are a few scenarios where statusing doesn’t overwrite timesheet comments:
- Single entry mode is enabled, the resource timesheet manager and resource are the same person and the project manager doesn’t approve time for statusing;
- Task Manager is enabled and resource is auto-approved;
- Task Manager is disabled and resource is approved by the timesheet manager; and
- Sometimes “Fix Approval Routing” is enabled to prevent timesheet line approval.
(If you have more to add to this list, I hope you’ll comment after reading the rest of the article!)
Deep Dive into Timesheet Comments
Determined to find a good solution for timesheets comment, I took a look into the data flow of timesheet line comments in the Project Server database. My thinking at the time was that the comments are saved somewhere in the published database or that a transaction log file had a history of the comments. My theory didn’t pan out as I had hoped.
When a timesheet is created; the published database table, “MSP_TIMESHEET_LINES,” is updated with a record for each line in the timesheet. Each time a timesheet was updated and saved, the timesheet line data is replaced with the saved data from the form into the MSP_TIMESHEET_LINES. Comments are also saved and updated each time a save occurs.
In the example below a comment has been added to each line in the timesheet.
After saving the timesheet, we queried the PUB.TIMESHEET_LINES and found that each time a timesheet was saved, the comments could be tracked down in the TS_LINE_COMMENT field from the PUB.MSP_TIMESHEET_LINES table.
At some point users submit their time by clicking Send and “Turn in Final Timesheet”. The timesheet ribbon provides an option to include a comment and then the timesheet is forwarded to the timesheet manager. Several things happen at this point.
First, the comments in the PUB.TIMESHEETS_LINES are the same as before. Second, we find new records inserted in the table, DBO.MSP_TIMESHEETLINE. I have also noticed that with different Project Server configurations, Save sometimes saves records in both tables. While watching both the PUB.TIMESHEET_LINES and DBO.MSP_TIMESHEETLINE tables and performing an approval, I found that the approval often overwrites the timesheet comment with notes such as “[mwharton: 3/30/2015]” which is the name of the resource and timestamp of the project manager or timesheet manager. Once that happens the timesheet comment is useless if the purpose of the comment was to record details about work done that week.
One Hope on Timesheet Comments
Capturing additional details about work using timesheet comments has many limitations. Most Project Server configuration can cause timesheet comments to be overwritten. If you’re only using the timesheet feature of Project Server, then this feature may work for you. Timesheet comments will be saved and generating detail work reports with comments may work fine. However, if statusing is part of your governance or if Project Server is set to single entry mode, then approvals will overwrite and lose your comments.
There is one hope that can make this work for all scenarios. Project Server provides server-side event handlers that can be used to capture your timesheet comments. Specifically, the [Timesheet Submitted]” event handler can capture the timesheet comments and write them into a separate table before other approvals override them. A little time is needed to be invested in creating this solution because it requires custom coding and connecting the custom code to the event handler. While it may sound complicated, there are some great coding examples found on MSDN.
Please post your comments if you’ve found a better solution for managing timesheet comments.
A version of this article originally appeared on Michael Wharton’s blog, MyProjectExpert.
Image courtesy of Marjory Collins, via Wikimedia Commons
