Forum Discussion
Reporting on Project Online (PWA) Timesheets
Hi Andy,
The easiest way is to import only the tables that you need and create a PowerPivot model in Excel.
Best practices for querying the OData feeds an be found here:
https://docs.microsoft.com/en-us/previous-versions/office/project-odata/dn776317(v%3doffice.15)
Relevant tables could be:
- Resources - resource metadata
- Projects - project metadata
- Assignments - link between project and assignment
- TimesheetLineActualDataSet - Actual Data from timesheets
- Timesheets - Link to resource and timesheet status
- TimesheetLines - Link to resource and project and timesheet line status
The complete reference can be found here: https://docs.microsoft.com/en-us/previous-versions/office/project-odata/jj163529(v%3doffice.15)
Ian and Trutz, many thanks for your speedy replies - much appreciated.
I've not needed to explore PowerQuery so far in my career, but this will be the project that forces me to learn it I think. I'd found most of those tables and had been going through the process of filtering out unwanted columns and filtering the data for my specific projects within the query. My next steps are to complete this process, then start linking these tables together to get the simple data output that I want.
I guess I should look for a simple tutorial in PowerQuery to learn a few basics, but I can't help just getting in there and playing around.
Your responses have definitely confirmed that I am taking the right approach, so thank you for that. I really just need to learn how to use these modern data tools in Excel!
The links provided will I'm sure be helpful too, thank you!
Wish me luck...
Andy