Recently, we were working on a nice set of Project Online timesheet reports for a client. The fields we needed were in these Odata tables:
- Tasks
- TimesheetLines
- TimesheetLineActualDataSet
We knew we were going to need to create some calculated fields, so we decided to use PowerPivot. We needed to create relationships between these 3 tables, so we started with the most granular tables. We created these relationships:
- TimesheetLineActualDataSet relates to TimesheetLines using the TimesheetLineId field
- TimesheetLines relates to Tasks using the TaskId field
We started to create the pivot table for our report, and noticed none of the information we needed from the Tasks table was populating. We needed those fields for our report. After a bit of troubleshooting, we found the ID fields had inconsistent formatting that was causing them to not relate. Two of the fields had curly brackets ({}) around the ID. One table did not. When Project Online looks at those fields, it knows to ignore the curly brackets. Excel does not.
We had some cleaning up to do. We needed to change the TaskId field in the TimesheetLines table to show the TaskId without curly brackets. That would make it look like the TaskId field in the Tasks table. While we could make one long intricate formula, we decided to break it into three:
First, using best practices, instead of counting the number of characters in an ID field, we allow Excel to count. That way, should the numbering standard change, it will not affect us:
Next, we remove the curly bracket from the left side of the field:
Then, we removed the curly bracket from the right side:
Now, we have a TaskId field that will relate to the Tasks table, and we are able to create our data model, and then our report, successfully!
If you’d like to find out about classes we offer in Reporting from Project Online or Project Server, contact us!
