Previously, I wrote a bit of a rant about reporting with Project Online. I still think it’s a bit of a disaster, but I’ve learned a bit more since writing that post, and can now produce somewhat functional reports without Excel crashing. It still takes quite a bit longer than with SSRS, but now it is possible.
This post will go through the process of building an Excel PivotTable that uses data from a PowerPivot source, that is getting data via an OData feed from Project Online.
PowerPivot is not capable of authenticating to Office 365. Perhaps that will change in the future, but that is the reality today. The workaround is to configure fiddler to submit the authentication token for Excel. So, the broad steps are:
a. Download and install fiddler
b. See this post for instructions on how to configure custom rules in fiddler to enable authentication with Office 365
c. With fiddler running, navigate to the Project Online site in a browser (now fiddler has the authentication token)
d. Open the PowerPivot window, and proceed.
2. Get Data
In theory, we should be able to connect to several different OData feeds, and then build a pivot table with them. I could not get this to work. It would start to work, but Excel would repeatedly crash, and I would constantly get out of memory errors. But, the following works:
a. In the PowerPivot window, select “get external data” –> “from Data Service” –>From OData Feed.
b. Paste in the URL for the OData Feed, such as https://somesite.sharepoint.com/sites/pwa/_api/projectdata/Tasks
c. See the special note below for the Projects feed. DO NOT just use projectdata/Projects
d. Finish the little wizard for the feed, and repeat for any other OData feeds you need from projectdata.
e. Switch to the diagram view, and configure relationships for the feeds, as appropriate
f. Identify which feed is the lowest level you need for the given report. For, example, if you’re reporting on Projects, tasks, and assignments, the “lowest level” feed is assignments. (The feed that is the “many” part of a one to many relationship).
g. Switch to the tab for the “lowest level” feed, as mentioned above. Scroll to the far right, and add calculated fields for each and every field you need to display on the report that is not in this tab already. The goal is to be able to build the pivot table with only using fields from a single tab in PowerPivot. For example, perhaps you need a report that has data from the Assignments feed, but you also need the “ProjectType” field from the projects tab. In the PowerPivot window, switch to the Assignments tab, and add a field with the following formula: =RELATED(Projects[ProjectType]). Repeat this for every field needed on the report.
h. Add a pivot table, using only fields from the one PowerPivot tab.
Note: As mentioned above, the Projects feed requires special handling. When I tried to use the Projects feed, it consistently gave errors, until I added a select statement to specify the list of fields and avoided ParentProjectId. I simply could not get the Projects feed to work if that field was included, but it works fine if it’s not there. For example:
I assume there’s an issue with a data type. If you know how to resolve this issue, please leave a comment!
So, the above system works for me. I still daydream of using SSRS while building these reports, but it does work. If you know of better ways of building reports with Project Online, again, please leave a comment.