Reporting with Project Online, attempt #2

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.

1. Authenticate

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:

https://site.sharepoint.com/sites/pwa/_api/projectdata/Projects?$select=ProjectId,ProjectActualWork,ProjectDescription,ProjectName,etc,etc2&$orderby=somefield,somefield2

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.

Advertisements

8 thoughts on “Reporting with Project Online, attempt #2

    1. Mike G Post author

      Check PWA permissions, there is a permission for accessing the data feeds. Specifically, go to PWA Settings –> manage users –> click on the user account, and ensure that the user has been granted the “Access Project Server Reporting Service” permission (in global permissions).

      Reply
      1. Justin

        Mike this was working prior to me switching from http to https w/user certificates. The users had the permission prior. I modified the trusted data connections, and location to all https. Do I need to refresh the permissions? I’m also in sharepoint permission mode if that plays any affect.

      2. Mike G Post author

        Sorry, I’m not familiar with your configuration. If you switched from http to https, then it doesn’t sound like you’re using Project Online. And, user certs? I’m not familiar with the requirements for getting user certs operational in SharePoint. I know that SSRS doesn’t support it, and Excel Services might not either.

  1. Sylvain Gross (@SylvainGrossNeo)

    Hi Martin,

    You should consider using PowerQuery to connect to your OData datasource in Project Online.
    I found an article from the Office Blog here: http://blogs.office.com/2014/03/24/creating-burndown-charts-for-project-using-power-pivot-and-power-query/
    The steps are:
    – After downloading and installing Power Query, go to the POWER QUERY Tab in excel
    – Add a Other Data Source, and select OData
    – Type your URL to the list you want: Resources, Projects, etc…
    – A form asks you for Authentification: choose Enterprise, and put your Live ID associated with your account
    – Power Query displays all the data in those table: from there you can arrange them as you want
    – Go to the Power Pivot to define the datamodel: the Power Query query is available as Connection, select it
    – From now, you can work with your data stored in Project Online directly from Power Pivot, without using Fiddler or somewhat…

    Hope it should work for you ! Pls give me your feedback on twitter @SylvainGrossNeo
    Good luck and thanks for your intersting blog !

    Sylvain

    Reply
  2. Joubin Simoni

    Hi Mike,

    Great and informative post! I am doing a Project Online deployment and basically did the same exact items you specified above to implement reports for this project. A couple of questions I had for you –

    1) Do you know of any way to improve the performance of the reports? I found that in a report where I need Assignment, Task, Project and AssignmentBaseline data, performance is incredibly slow as when you first try to refresh the report, it takes over a minute just for the refresh! and I am talking a small result set of < 1000 records. I am embarrassed to introduce these reports to the customer due to such terribly slow performance but not sure how to improve it!

    2) I have not found a good way (other than using the Excel Pivot table tools) to filter the data (i.e. introduce the equivalent of a WHERE clause for the reports). Of course, with Excel pivottable filtering, there's always the problem of when new records come in (for instance, you filter on Cycle field including only 1 and 2 then sometime later, the "3" record is in the dataset but not shown in the report because of the existing filter). Putting a filter in the powerpivot data model does not seem to stick….any ideas or suggestions on how to approach this? Many thanks!

    Reply
    1. Mike G Post author

      1) We have very limited options re performance. You could:
      a. Use Power BI, which has a scheduled refresh capability. (though, the most frequent refresh you can schedule is daily)
      b. Use SSIS to get the data from Project Online to your own data warehouse, and use an onprem instance of ssrs to do reporting. Have fun selling that one to the customer, but if you need more info, it’s here:http://msdn.microsoft.com/en-us/library/office/dn720853%28v=office.15%29.aspx (Project Hosts offers an app where they build and host the data warehouse for you: http://blog.projecthosts.com/2014/02/03/ssrs-reports-for-project-online-showcased-in-microsoft-keynote-at-project-conference-2014-projconf-projectonline/)
      c. Instead of Excel or SSRS, you can write javascript that queries and presents data from the odata feeds. Pro: fast response times for the user. Con: increases “report” development time by 10x – 50x.
      d. In excel, you can modify the odata url to select specific fields. So, if you only select the fields you need, you’ll get somewhat better performance.

      But otherwise, the poor performance is by design, so there’s not much we can do.

      2. Slicers are really nice, and users like them (just search for “excel slicers”). They only have a few cons:
      a. filtering is still done in the client (excel). So, this doesn’t help performance.
      b. If the field you’re filtering on has more than a few values, then slicers are a little clunky to work with
      c. Slicers don’t handle other data-types well, such as dates

      Good luck!

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s