SOLVED
Home

Reporting on Project Online (PWA) Timesheets

%3CLINGO-SUB%20id%3D%22lingo-sub-213662%22%20slang%3D%22en-US%22%3EReporting%20on%20Project%20Online%20(PWA)%20Timesheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-213662%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20simplest%20way%20to%20pull%20Timesheets%20reporting%20out%20of%20Project%20Online%20(PWA)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20organisation%20is%20using%20the%20Timesheets%20functionality%20in%20Project%20Online%20to%20record%20the%20time%20resources%20spend%20against%20projects%20to%26nbsp%3Benable%20project%20profitability%20reporting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20build%20a%20set%20of%20reports%20in%20Excel%20that%20can%20be%20automatically%20refreshed%20and%20combined%20with%20other%20data%20sets.%20My%20level%20of%20Excel%20knowledge%20is%20pretty%20good%2C%20and%20I%20can%20see%20an%20oData%20connection%20to%20PWA%2C%20but%20the%20mass%20of%20tables%20that%20exist%20in%26nbsp%3BPWA%20are%20pretty%20daunting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20or%20advice%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAndy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-213662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOnline%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EProject%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-214296%22%20slang%3D%22en-US%22%3ERe%3A%20Reporting%20on%20Project%20Online%20(PWA)%20Timesheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-214296%22%20slang%3D%22en-US%22%3E%3CP%3EIan%20and%20Trutz%2C%20many%20thanks%20for%20your%20speedy%20replies%20-%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20not%20needed%20to%20explore%20PowerQuery%20so%20far%20in%20my%20career%2C%20but%20this%20will%20be%20the%20project%20that%20forces%20me%20to%20learn%20it%20I%20think.%20I'd%20found%20most%20of%20those%20tables%20and%20had%20been%20going%20through%20the%20process%20of%20filtering%20out%20unwanted%20columns%20and%20filtering%20the%20data%20for%20my%20specific%20projects%20within%20the%20query.%20My%20next%20steps%20are%20to%20complete%20this%20process%2C%20then%20start%20linking%20these%20tables%20together%20to%20get%20the%20simple%20data%20output%20that%20I%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20I%20should%20look%20for%20a%20simple%20tutorial%20in%20PowerQuery%20to%20learn%20a%20few%20basics%2C%20but%20I%20can't%20help%20just%20getting%20in%20there%20and%20playing%20around.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20responses%20have%20definitely%20confirmed%20that%20I%20am%20taking%20the%20right%20approach%2C%20so%20thank%20you%20for%20that.%20I%20really%20just%20need%20to%20learn%20how%20to%20use%20these%20modern%20data%20tools%20in%20Excel!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20links%26nbsp%3Bprovided%20will%20I'm%20sure%20be%20helpful%20too%2C%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20me%20luck...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAndy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-213737%22%20slang%3D%22en-US%22%3ERe%3A%20Reporting%20on%20Project%20Online%20(PWA)%20Timesheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-213737%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20really%20depends%20on%20what%20you're%20looking%20to%20see...%20Status%20of%20timesheet%20submission%2C%20actual%20hours%2C%20both%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20starters%2C%20you'll%20likely%20use%20oData%20connections%20and%20Power%20Query%20in%20Excel%20or%20PowerBI%20to%20make%20the%20reports.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formatting%20information%20for%20the%20odata%20version%20used%20by%20Project%20online%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.odata.org%2Fdocumentation%2Fodata-version-2-0%2Furi-conventions%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.odata.org%2Fdocumentation%2Fodata-version-2-0%2Furi-conventions%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20function%20reference%20for%20working%20with%20Power%20Query%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fquery-bi%2Fm%2Fpower-query-m-function-reference%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmsdn.microsoft.com%2Fquery-bi%2Fm%2Fpower-query-m-function-reference%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20where%20you%20find%20the%20tables%20%3CSTRONG%3Eyour%3C%2FSTRONG%3E%20data%20are%20stored%20in%3C%2FP%3E%3CP%3Ehttps%3A%2F%2F%3CSTRONG%3Esitenamehere%3C%2FSTRONG%3E.sharepoint.com%2Fsites%2Fpwa%2F_api%2FProjectData%2F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20%24metadata%20to%20the%20end%20of%20that%20string%20to%20see%20all%20the%20attributes%20and%20data%20types%3C%2FP%3E%3CP%3Ehttps%3A%2F%2F%3CSTRONG%3Esitenamehere%3C%2FSTRONG%3E.sharepoint.com%2Fsites%2Fpwa%2F_api%2FProjectData%2F%24metadata%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20odata%20reference%20above%20to%20tack%20on%20to%20those%20URLs%20to%20filter%20some%20information%20and%20select%20certain%20things.%20For%20example%3A%3C%2FP%3E%3CP%3Ehttps%3A%2F%2F%3CSTRONG%3Esitenamehere%3C%2FSTRONG%3E.sharepoint.com%2Fsites%2Fpwa%2F_api%2FProjectData%2FTimesheetLines%3F%24filter%3DActualWorkBillable%20gt%200%20%26amp%3B%20%24select%3DProjectId%2CProjectName%2CTaskName%2CTimesheetOwnerId%2CTimesheetOwner%2CActualWorkBillable%2CTimesheetPeriodId%2CTimesheetStatus%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20plug%20that%20straight%20into%20IE%20or%20your%20browser%20of%20choice%20to%20see%20some%20data%20return.%20Best%2C%20though%2C%20would%20be%20to%20go%20to%20the%20data%20tab%20in%20Excel%2C%20New%20query%2C%20From%20other%20sources%2C%20From%20Odata%20feed.%20Then%20build%20%2F%20merge%20%2F%20format%20other%20queries%20to%20your%20heart's%20content.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20find%20samples%20in%20the%20TechNet%20gallery.%20Here's%20one%20I%20worked%20up%20since%20Project%20Online%20doesn't%20have%20a%20%22Not%20yet%20started%22%20stats%20for%20timesheets%2C%20but%20that's%20one%20of%20the%20most%20important%20for%20us!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgallery.technet.microsoft.com%2FTimesheet-Status-Report-37b95da2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgallery.technet.microsoft.com%2FTimesheet-Status-Report-37b95da2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-213736%22%20slang%3D%22en-US%22%3ERe%3A%20Reporting%20on%20Project%20Online%20(PWA)%20Timesheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-213736%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Andy%2C%3C%2FP%3E%3CP%3EThe%20easiest%20way%20is%20to%20import%20only%20the%20tables%20that%20you%20need%20and%20create%20a%20PowerPivot%20model%20in%20Excel.%3C%2FP%3E%3CP%3EBest%20practices%20for%20querying%20the%20OData%20feeds%20an%20be%20found%20here%3A%3C%2FP%3E%3CP%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fdn776317(v%253doffice.15)%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fdn776317(v%253doffice.15)%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fdn776317(v%253doffice.15)%3C%2FA%3E%3C%2FP%3E%3CP%3ERelevant%20tables%20could%20be%3A%3C%2FP%3E%3CUL%3E%3CLI%3EResources%20-%20resource%20metadata%3C%2FLI%3E%3CLI%3EProjects%20-%20project%20metadata%3C%2FLI%3E%3CLI%3EAssignments%20-%20link%20between%20project%20and%20assignment%3C%2FLI%3E%3CLI%3E%3CSPAN%3ETimesheetLineActualDataSet%3C%2FSPAN%3E%20-%20Actual%20Data%20from%20timesheets%3C%2FLI%3E%3CLI%3E%3CSPAN%3ETimesheets%20-%20Link%20to%20resource%20and%20timesheet%20status%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3ETimesheetLines%20-%20Link%20to%20resource%20and%20project%20and%20timesheet%26nbsp%3Bline%20status%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20complete%20reference%20can%20be%20found%20here%3A%26nbsp%3B%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fjj163529(v%253doffice.15)%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fjj163529(v%253doffice.15)%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fjj163529(v%253doffice.15)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Andy Dennis
New Contributor

Hi all,

 

What is the simplest way to pull Timesheets reporting out of Project Online (PWA)?

 

My organisation is using the Timesheets functionality in Project Online to record the time resources spend against projects to enable project profitability reporting.

 

I want to be able to build a set of reports in Excel that can be automatically refreshed and combined with other data sets. My level of Excel knowledge is pretty good, and I can see an oData connection to PWA, but the mass of tables that exist in PWA are pretty daunting.

 

Any help or advice appreciated.

 

Thanks,

Andy

3 Replies
Solution

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

 

 

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

Related Conversations