Forum Discussion

Copper Contributor

May 09, 2022

Solved

Pulling Time Sheet Data

We used to run Project Server on premise which had a built in sample report for pulling time sheet data. We are now setting up Project Online and noticed these sample reports are no longer avai...

Online

Project

Reporting

Paul_Mather
May 10, 2022
Hello csmathers2 ,

You will need to use the Organization account option. From a blank Excel file, try these steps for Project Online:

Data tab > Get Data > From other sources > From OData feed

In the OData feed pop up, keep Basic selected and enter the PWA URL like below:

{pwaUrl}/_api/ProjectData such as: https://tenant1.sharepoint.com/sites/PWAsite/_api/ProjectData

Ensure Organization account is use and sign in then click Connect

Then select the tables you want to build the reports you need

You could also look at Power BI and start with the default template: https://github.com/OfficeDev/Project-Power-BI-Templates/tree/master/Project%20Online

Many thanks

Paul

csmathers2

Copper Contributor

May 09, 2022

I should also mention we still have pivot tables that we used to pull data from the old server. I was hoping we might be able to redirect these to the new data source but I'm unsure of precisely how to accomplish this.

Paul_Mather

MVP

May 09, 2022

Hello csmathers2 ,

What was the data source for the Excel files when connected to Project Server, depending on the version of Project Server, they could have been using either of these:

OData - _api/ProjectData
OLAP Cube
SQL

For Project Online, the Timesheet reports will need to use the OData Reporting API as the other data sources are not available. So if your existing reports are using either the OLAP Cube or direct SQL queries, these will need to be recreated for Project Online.

Paul

csmathers2
Copper Contributor
May 10, 2022
Thank youPaul_Mather. At the moment I'm having trouble connecting to the data through Excel.
I've tried the various methods of connecting, except for the Web API option. I'm not sure what is needed in the "Key" field, if that's what you're suggesting.
Any additional insights would be much appreciated.
- Paul_Mather
  MVP
  May 10, 2022
  Hello csmathers2 ,
  
  You will need to use the Organization account option. From a blank Excel file, try these steps for Project Online:
  
  Data tab > Get Data > From other sources > From OData feed
  
  In the OData feed pop up, keep Basic selected and enter the PWA URL like below:
  
  {pwaUrl}/_api/ProjectData such as: https://tenant1.sharepoint.com/sites/PWAsite/_api/ProjectData
  
  Ensure Organization account is use and sign in then click Connect
  
  Then select the tables you want to build the reports you need
  
  You could also look at Power BI and start with the default template: https://github.com/OfficeDev/Project-Power-BI-Templates/tree/master/Project%20Online
  
  Many thanks
  
  Paul
  - csmathers2
    Copper Contributor
    May 10, 2022
    Thanks again Paul. I was missing the _api/ProjectData piece when attempting to connect. This did the trick!