Forum Discussion
Reporting on Project Online (PWA) Timesheets
- Jul 10, 2018
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)
That really depends on what you're looking to see... Status of timesheet submission, actual hours, both?
For starters, you'll likely use oData connections and Power Query in Excel or PowerBI to make the reports.
Here's the formatting information for the odata version used by Project online: http://www.odata.org/documentation/odata-version-2-0/uri-conventions/
Here's the function reference for working with Power Query:
https://msdn.microsoft.com/query-bi/m/power-query-m-function-reference
Here's where you find the tables your data are stored in
https://sitenamehere.sharepoint.com/sites/pwa/_api/ProjectData/
Add $metadata to the end of that string to see all the attributes and data types
https://sitenamehere.sharepoint.com/sites/pwa/_api/ProjectData/$metadata
Use the odata reference above to tack on to those URLs to filter some information and select certain things. For example:
https://sitenamehere.sharepoint.com/sites/pwa/_api/ProjectData/TimesheetLines?$filter=ActualWorkBillable gt 0 & $select=ProjectId,ProjectName,TaskName,TimesheetOwnerId,TimesheetOwner,ActualWorkBillable,TimesheetPeriodId,TimesheetStatus
You can plug that straight into IE or your browser of choice to see some data return. Best, though, would be to go to the data tab in Excel, New query, From other sources, From Odata feed. Then build / merge / format other queries to your heart's content.
You can find samples in the TechNet gallery. Here's one I worked up since Project Online doesn't have a "Not yet started" stats for timesheets, but that's one of the most important for us!
https://gallery.technet.microsoft.com/Timesheet-Status-Report-37b95da2