What Project Server 2010 and 2013 make possible in terms of timesheeting, "freezing" timesheets and expenses. RRS feed

  • Question

  • We have recently gone live on a Timesheeting system based on Project Server 2007.

    I now want to know how the system could be improved using 2010 or 2013.

    But first you would need to know our requirements and what we did in 2007 ...

    We needed the GUI of My Timesheets and the Admin tasks but approvals based on what project/task it was. Ie PM approval not line management approval.

    We managed it by entering timesheets in My Timesheets, importing to My Tasks and then regularly picking up published Task Updates in the Table EPMAssignmentByDay and capturing them in an added table in the Reporting database. We had a few key requirements which required some customisation:

    1. Need to capture the timestamp of when approved. to nearest hour. plus approver ID (ie status manager) and the cost centre at that time of the resource (they can move sometimes)
    2. Need to be able to freeze approved records - so that after that point they can't be changed. And financial reports can run and know the data won't change.

    2 - is a headache for us. There is no mechanism to stop a user from trying to update frozen records. We currently have to just ignore the updates

    We also wanted expenses - but that wasn't possible in 2007.

    The new "Single Entry" mode looks like it will make life easier for the users.


    1. Is there any concept of timestamping the approvals?
    2. Is there any concept of freezing the approved data?
    3. The new Timesheet user-defined fields. Could these be used to add an expense amount and comment?

    I now know my way around the 2007 Reporting Database (at least parts of it) - but haven't found documentation to tell me what changed at the level need to answer the questions above.

    Thank you



    Wednesday, February 12, 2014 12:36 PM

All replies

  • Hi Mike,

    Here are a few keys for your questions (indeed the SEM might improve your users experience):

    1. Any timesheet operation is logged into a timehseet_audit table, thus all is timestamped and tracked so it can be reported and audited easily afterward,
    2. A approved timesheet can always be recalled by the resource, there is no way to prevent a user to recall his timesheet, evenif it has been approved. This is by design, to allow user adjusting mistakes. Anyway the approver can simply reject any recalled and submitted again timesheet,
    3. The need should be seen more in detail, but you might create a custom field for assignments and add it to the timesheet view, allowing users to enter a custom cost by assignment. Concerning the comments, by design, the user can enter a comment per assignment per period in his timesheet.

    Hope this helps.

    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, February 12, 2014 2:17 PM
  • Guillaume

    Thank you.

    1. I didn't know about the audit table. I can find something like that in the Published database on 2007 - and I have been told we are not allowed to use data there. "Not supported by Microsoft". Is that right
    2. This is bad news for me. I understand that you need to leave the timesheet open for some time to allow corrections. But to feed financial systems, at some stage you have to say this will not change. You might pay people/companies based on the hours. I'd be very interested to hear how other companies address this.
    3. We did look at a custom assignment-level field for adding expenses. But what if you have expenses in multiple periods. And you want to know what expense went when (we are looking to pay people based on these). In database terms you can add fields at the level of the EPMAssignment table in 2007. But I am looking for something at the level of EPMAssignmentByDay. I heard something in a 2010 broadcast which made me think you could add custom fields at that level. But I am looking for more detail.



    Wednesday, February 12, 2014 3:41 PM
  • 1- Here is an interesting article about MSP_TIMESHEET_ACTUAL_AUDIT table:

    Don't know if it is supported or not, but it is definitively a good tool.

    2 - Concerning this point, I'd advice to simply close the timesheet period before feeding financial system, so no modification can be done afterwards.

    Hope this helps.

    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, February 12, 2014 3:48 PM
  • Thanks again Guillaume.

    That 2010 timesheet audit report seems to show that 2010 tables are not so different from 2007.

    Our problem with closing the period is that we have over 500 resources booking time. We can't get them to enter time all on the same week. closing periods (for all resources) is too crude

    In our previous system and in the one we built on Project 2007, we take all the time that has been approved and mark it as frozen. The previous system then stopped you changing the data.

    I have heard of some code which freezes individual timesheets in Project Server. We may have to look at that.



    Thursday, February 13, 2014 9:01 AM
  • One of my colleagues pointed me to the MSFT paper "Managing timesheets in Microsoft Office Project Server 2007"

    It tells me that there is a field called "Processed" in the timesheet tables in 2007.

    It also says "This block is designed to indicate when a timesheet has been transferred to a third-party system.  Customers may also use this field to block the timesheet from being recalled." - which feels like MSFT are trying to give the hooks I need. This could enable me to close off changes - at least at timesheet level if not timesheet line level.

    But which field is it in which database table?

    I also notice that there is a tantalising comment for one value of the enumeration TimesheetEnum.Status in EPM 2013: PendingSubmit: Value=5. Pending submission; used when one or more timesheet lines are pending approval after a timesheet is submitted and project manager coordination is required.

    This sounds very like it would support what we want: each of the lines in a timesheet week to be approved by the different approvers and then lock the timesheet.  (Even better would be to lock a row after PM has approved and a weekly processing day reached)

    Can anyone point me to this level of detail of what is in project Server 2013. or is this in 2010 as well.



    Monday, February 17, 2014 5:03 PM
  • This might be my last reply to my own post.

    I found an interesting article on how to close a timesheet properly: tells you about the property TS_IS_PROCESSED (which is also in the Publish database as a field in MSP_TIMESHEET.

    And similarly

     So nice to know I am not cracking up and imagining a requirement and thinking the standard Microsoft answer (closing periods) is too crude and just doesn't cut it. And there is a technical solution - though as usual the data you want to access (ok write to) is in the published database. PSI is a bit of toil compared to a good update query.



    Thursday, February 20, 2014 1:26 PM
  • You are a life saver
    Thursday, June 18, 2015 8:42 PM
  • In 2013 as far as I tested and I know, Once period is closed. user cannot delete or recall a timesheet.
    Thursday, June 18, 2015 8:48 PM