Reporting with Project Online is a bit of a disaster.
Edit – I’ve learned a bit more since publishing this. See the newer post here:
With an on premise installation of Project Server, we have access to the reporting database (well, it’s all one database at this point, so perhaps I should call it something else…). The reporting tables are beautiful. They are well organized and clearly named.
We don’t have access to the reporting database in Project Online. Instead we have OData (which is also available to on premise installations). OData is certainly great for developers. Pass in a URL, get data back. Nice. With OData, you can specify fields, filters, etc. (As a side note, if you need to work with OData, go get a copy of LinqPad (www.linqpad.net). It’s a fantastic GUI tool to help you build out the URLs..)
So, how do you actually use an OData feed from Project Online? SSRS is out, as it doesn’t support OData feeds (Perhaps it can through an xml hack, but you can’t host your reports on project online. If you try hosting them elsewhere, you’ll quickly run into authentication issues as SSRS doesn’t support authenticating with Office 365 sites.) The main option that we have is Excel. And, at first glance Excel seems to work just fine. You can quite easily retrieve a list of projects and display them in Excel. You can even use slicers to filter the data. Nice.
But how about a slightly more complicated scenario. How about a report that lists overdue tasks along with data about the assignment for all active projects for a given project manager? With the reporting database, this is a relatively simple query, but here’s the process for doing it with OData:
Add data connections for each of the following:
- Wait for each data connection to bring in ALL of the data for the specified entity. (If you have a small number of projects, you’ll hopefully be able to avoid any out of memory errors)
- Build out your pivot table
- Publish to the reports folder in Project Online
- Explain to users that the data will be out of date when they view it, and show them how to refresh the data connections (how long this takes will depend on the number of projects you have. For me, this process took 3-5 minutes.)
This is why most of the demos of this feature use the Projects entity. Again, when you try to build a report that uses Assignments, it downloads ALL rows from the assignments table into Excel. Now, you can certainly customize the URL of the oData connection to specify a filter. But that filter cannot be dynamic. So, you can specify start and end dates, but those dates will be hardcoded (and there’s no “today” keyword we can use). Another example: you can easily set up the oData connection for Projects to only return “active” projects. But how do we specify that the tasks and assignments should only be for those active projects? We don’t have access to “join” capabilities. So again, the solution is to bring ALL data local (to Excel), and perform filtering there.
A word about data refresh: You can configure data connections in Excel to refresh on an interval, but that applies to while the Excel file is open in Excel, not while it’s sitting in a SharePoint library. So, if users want current data, they will have to refresh the connections. This is a not-great but workable solution if you’re listing projects (which load relatively quickly), but this is just silly if the user wants to view a report that contains info for tasks/assignment, and has to wait multiple minutes each time they want to view the report.
A couple more details:
- Power View fully supports setting up joins based on the GUID fields that exist in the OData feeds.
- Power Pivot does not support setting up joins based on the GUID fields. (I have no explanation for this, but this seems to be the case based on repeated testing).
- If you go the OData route, realize that the OData feeds will only send the first x records, along with a link for the OData url to get the next batch.