I had all sorts of trouble trying to get the Project Web App standard reporting (ie Excel sheets supplied OOTB that use an Odata connection to extract data from PWA) working. I tried a lot of things including enabling the Project Web App Permission for Excel Web App Refresh site collection feature on the PWA site. So I opted to use Power BI. Here’s how to do this:
- Download Power BI Desktop from the Windows Store
- Follow these instructions to connect to your PWA site
I wanted to create a timesheet report that I can send to clients where we do time and materials work. This is what I did.
Load the following tables (and select these fields) from your PWA site:
- TimesheetLineActualDataSet (ActualWorkBillable, Comment, Resource Name, TimeByDay)
- TimesheetLines (ProjectName, TaskName, TimesheetLineComment)
- Timesheets (Comment, PreiodName)
We generate a timesheet report for our clients by displaying the above in a table, and then export the visualisation to a csv.
Note that Power BI automatically applies Referential Integrity so you don’t need to set this up!
Date columns are composite, in that they are broken up into year, month, day and quarter. We leave quarter out. To sort by date, select year, month and day columns in the table together by clicking and holding shift.