Mar 30
Capturing Task Details using Timesheet Comments has Many Limitations

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 meet their requirements.  For this client, the timesheet comments worked 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, the project manager's comment or blank comment, over writes the existing comments.   For my client, the solution worked well because they were not statusing, however most of my clients do statusing, so I decided to dig deeper into this issue.

As I dug deeper into the database to understand timesheet comments, I discovered that the server configuration was also playing a big part in how and when timesheet comments are over written. The following are few scenario statusing in which timesheet comments are not overridden:

  • SME is enable and the resource timesheet manager and resource are the same and the project managers does not approve time for statusing.
  • Task Manager is enabled and resource is auto-approved
  • Task Manager is disable and resource is approved by timesheet manager
  • Sometimes Fix Approval Routing is enabled to prevent timesheet line approval

I have tested many combination of configurations keep finding that most configuration will over write the timesheet comments after an approval. You may find my scenarios list above may not be correct and my response is, "somewhere in the project server configuration is has affected the comments".

Deep Dive into Timesheet Comments

Determine to find a good solution for timesheets comment, I decided to take a deep time into the data flow of timesheet line comments in the project server database.   My thinking at the time was the comments are saved somewhere in the published database or a transaction log file had a history of the comments.  My theory didn't pan out as I 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 updated and saved, then 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.

TimesheetComment1.png 

After saving the timesheet, we query the PUB.TIMESHEET_LINES and find the following results. Each time a timesheet is save, the comments can be found in TS_LINE_COMMENT field from the PUB.MSP_TIMESHEET_LINES table.

TimesheetComment2.png 

At some point in time user submits 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 forward to the timesheet manager.  Several things happen at this point.

First of all, 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 seen with different project server configurations, that the save operations saves records in both tables at times.   While watching both the PUB.TIMESHEET_LINES and DBO.MSP_TIMESHEETLINE tables and performing an approval, I found that the approval often over writes the timesheet comment with notes like [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.

Conclusion about using Timesheet Comments

Capturing additional details about work using timesheet comments has many limitations.  Most project server configuration can cause timesheet comments to be over written. If you are only using the Timesheet feature of project server then this featur may work for you.  Timesheet comments are saved and generating detail work reports using commentes may work fine.  However, if statusing is part of your governance or if the project server settings is set to SME (Single entry mode) then approvals is going to overwrite your comments and the timesheet comments will be lost.  

There is one hope that can make this work for all scenarios.  Project server provides server side event handlers that can be used for capture your timesheet comments. The [Timesheet Submitted] event handler can be used to capture the timesheet comments and then write them into a separate table before other approvals override them.   A little time and money is needed to be invested in creating this solution because it requires custom coding and connecting the custom code to the event handler.  Seems complicated but there are some great coding examples found on MSDN.

Send me your comments if you have found a better solution to manage timesheet comments.

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools