I've been working with the Microsoft Cloud Escalation Team for almost four months to resolve an issue we've been having with the Timesheets functionality in Project Online since we started using the product almost 3 years ago. I don't seem to be making any progress with Microsoft directly, so thought I'd see if anyone else has come across this problem. There are two key problems with the timesheets feature that in turn, cause labour costs in MS Project to be inaccurate.
1. GUID ISSUE: During the period of Jan 2015 (could have started earlier, this is when we started using the product) through to Sep 2016, the timesheets tables were producing unidentifiable GUIDs in the ProjectId, TaskId, and AssignmentId fields. Essentially whenever a new timesheet line/row was inserted into one of the timesheet tables, instead of the system retrieving the corresponding GUID from the project/task/assignment tables, it would just insert a random GUID. We use the OData feed to access our data and these unidentifiable GUIDs meant we couldn't join and compare the actuals data in the assignments tables and timesheets tables to ensure data is copied successfully to the assignments tables (and therefore reflected accurately in the corresponding MS Project file).
Microsoft rectified this issue with a patch in Sept 2016 so it no longer occurs but we want to be able to check our historical data is correct and accurate. Has anyone else had this problem or been able to overcome/address it?
2. MISMATCH ISSUE: This leads to problem number 2; because of our lack of trust in the data we now monitor things much closer in order to maintain management's trust in the reported information. What we are observing is that timesheets actuals aren't consistently published/copied across to the assignments tables / MS Project files.
We follow a strict process with timesheets in an effort to minimise the occurence:
- Each person submits their timesheet on a Friday afternoon / Monday morning
- Once all timesheets are submitted, timesheet approvers approve all entries via the Approvals page at approximately the same time and publish these entries via the Status Updates page.
- I then check the data via a Power BI file querying the OData (Project Data) feed to check that the values in ActualWorkBillable (TimesheetLineActualDataSet) have copied across to AssignmentActualWork (AssignmentTimephasedDataSet) successfully. In most cases this happens, but there are instances where the values don't copy across.
Looking at this problem in detail, I've determined the problem occurs during the following actions and Microsoft have verified the system is designed to do the following:
1. TimesheetOwner (as per TimesheetLines table) presses Submit on the Timesheet screen - the TimesheetStatus (TimesheetLines) value changes from "In Progress" (or new timesheet line entry is added to the table if it doesn't already exist) to "Approved" and the TimesheetLineStatus (TimesheetLines) value changes to "Pending Approval".
2. The ReportStatusManager (as per Tasks table) presses Accept on the Approvals screen - the TimesheetLineStatus (TimesheetLines) changes from "Pending Approval" to "Approved".
3. The ReportStatusManager (as per Tasks table) presses Publish on the Status Updates History screen - the TimesheetApproverResourceName (TimesheetLines) field is populated with the ReportStatusManager (Tasks) name, and the values in ActualWorkBillable (TimesheetLineActualDataSet) are copied across to AssignmentActualWork (AssignmentTimephasedDataSet)
We are seeing two types of mismatches; the first appears to occur when the Accept button is pressed on the Approvals page. For some reason, the TimesheetLineStatus value isn't successfully updating to "Approved" (see point 2 above). The second type of mismatch is where the value doesn't successfully copy from ActualWorkBillable to the AssignmentActualWork field (see point 3 above).
Now that the GUID issue (described in point 1) is fixed, I'm able to successfully join the two tables (only for data post Sep 2016) TimesheetLineActualDataSet and AssignmentTimephasedDataSet and my report highlights the two different types of mismatches. This does though require careful use of the product and a solid amount of time each week ensuring the product behaves as expected. When these mismatches occur, we must recall the timesheet the entry is from, re-enter the values for the task, re-submit and re-approve/publish the timesheet - this effectively runs the entry through the whole process again which most of the time ends up with a successful outcome.
Given issue no. 2 wasn't fully understood/monitored when we started using the product, there are a large number of mismatches in our data prior to Oct 2016 - the problem with this; being we can't easily identify them so we can fix them, because of the GUID issue described in issue no. 1.
The person I've been communicating with from the Microsoft Cloud Escalation Team since May 2017 doesn't show any signs of understanding these problems in any depth and our emails have become a never-ending circle of clarifying what the problem is. Given Project Online is a cloud product that is deployed to all users out-of-the-box, I figured there must be others out there that are aware of this problem so have decided to take to the internet for help... any thoughts/suggestions/solutions are welcomed.
1. GUID ISSUE: During the period of Jan 2015 (could have started earlier, this is when we started using the product) through to Sep 2016, the timesheets tables were producing unidentifiable GUIDs in the ProjectId, TaskId, and AssignmentId fields. Essentially whenever a new timesheet line/row was inserted into one of the timesheet tables, instead of the system retrieving the corresponding GUID from the project/task/assignment tables, it would just insert a random GUID. We use the OData feed to access our data and these unidentifiable GUIDs meant we couldn't join and compare the actuals data in the assignments tables and timesheets tables to ensure data is copied successfully to the assignments tables (and therefore reflected accurately in the corresponding MS Project file).
Microsoft rectified this issue with a patch in Sept 2016 so it no longer occurs but we want to be able to check our historical data is correct and accurate. Has anyone else had this problem or been able to overcome/address it?
2. MISMATCH ISSUE: This leads to problem number 2; because of our lack of trust in the data we now monitor things much closer in order to maintain management's trust in the reported information. What we are observing is that timesheets actuals aren't consistently published/copied across to the assignments tables / MS Project files.
We follow a strict process with timesheets in an effort to minimise the occurence:
- Each person submits their timesheet on a Friday afternoon / Monday morning
- Once all timesheets are submitted, timesheet approvers approve all entries via the Approvals page at approximately the same time and publish these entries via the Status Updates page.
- I then check the data via a Power BI file querying the OData (Project Data) feed to check that the values in ActualWorkBillable (TimesheetLineActualDataSet) have copied across to AssignmentActualWork (AssignmentTimephasedDataSet) successfully. In most cases this happens, but there are instances where the values don't copy across.
Looking at this problem in detail, I've determined the problem occurs during the following actions and Microsoft have verified the system is designed to do the following:
1. TimesheetOwner (as per TimesheetLines table) presses Submit on the Timesheet screen - the TimesheetStatus (TimesheetLines) value changes from "In Progress" (or new timesheet line entry is added to the table if it doesn't already exist) to "Approved" and the TimesheetLineStatus (TimesheetLines) value changes to "Pending Approval".
2. The ReportStatusManager (as per Tasks table) presses Accept on the Approvals screen - the TimesheetLineStatus (TimesheetLines) changes from "Pending Approval" to "Approved".
3. The ReportStatusManager (as per Tasks table) presses Publish on the Status Updates History screen - the TimesheetApproverResourceName (TimesheetLines) field is populated with the ReportStatusManager (Tasks) name, and the values in ActualWorkBillable (TimesheetLineActualDataSet) are copied across to AssignmentActualWork (AssignmentTimephasedDataSet)
We are seeing two types of mismatches; the first appears to occur when the Accept button is pressed on the Approvals page. For some reason, the TimesheetLineStatus value isn't successfully updating to "Approved" (see point 2 above). The second type of mismatch is where the value doesn't successfully copy from ActualWorkBillable to the AssignmentActualWork field (see point 3 above).
Now that the GUID issue (described in point 1) is fixed, I'm able to successfully join the two tables (only for data post Sep 2016) TimesheetLineActualDataSet and AssignmentTimephasedDataSet and my report highlights the two different types of mismatches. This does though require careful use of the product and a solid amount of time each week ensuring the product behaves as expected. When these mismatches occur, we must recall the timesheet the entry is from, re-enter the values for the task, re-submit and re-approve/publish the timesheet - this effectively runs the entry through the whole process again which most of the time ends up with a successful outcome.
Given issue no. 2 wasn't fully understood/monitored when we started using the product, there are a large number of mismatches in our data prior to Oct 2016 - the problem with this; being we can't easily identify them so we can fix them, because of the GUID issue described in issue no. 1.
The person I've been communicating with from the Microsoft Cloud Escalation Team since May 2017 doesn't show any signs of understanding these problems in any depth and our emails have become a never-ending circle of clarifying what the problem is. Given Project Online is a cloud product that is deployed to all users out-of-the-box, I figured there must be others out there that are aware of this problem so have decided to take to the internet for help... any thoughts/suggestions/solutions are welcomed.