Building Excel reports from Project Server 2013 OData Services

This is quick walkthrough to showcase how you could build powerful reports from Project Server data online, in a jiffy.

I am using the Project Online Subscription from Office 365. However, this should work with your on-premise Project Server 2013 setup as well. So no worries…Interestingly this technique can be extended to any other data source…let’s say getting all your bug reports from Team Foundation Server to Excel, getting the Northwind Products table from an OData source online etc.

Without much adieu let me start… just 3 Steps

Step 1: Copy the OData Connection file URL from Project Online, under Data Connections library in the ProjectBICenter. You can find it in the following URL:



Not able to get here?

Navigate to your “pwa” home page (http://servername/sites/pwa) and click on “Reports” in the left navigation. It will take you to the ProjectBICenter site with the URL as (/ProjectBICenter/Pages/Default.aspx). Then click on “Reports” link on the left navigation in the ProjectBICenter site. You should reach the above place.

Step 2: Open new Excel Workbook and select “Existing Connections” from “Data” ribbonClick “Browse for More” button at the bottom left corner paste the above copied URL in the “Filenamesection and press Open.


Step 3: Select “OK” to view the data as a table


Final Outcome: Entire Project Online data is available to you in excel for data crunching. Also, data is constantly in sync with online.


To synchronize data with Project Online: Select “Refresh All” under the “Data” ribbon


Well you got through the hard part now…rest all i probably would leave to a good excel data cruncher to build charts, pivots, power views, KPIs and other dashboards out of this rich data.

If you see any missing columns, that’s probably coz they are not pulled as a part of your OData URL. If that’s the case, go back to Step 1 and then check the OData URL…add the column names you want (use your SharePoint skills to get the exact column name). For further tweaking the OData URL and its query options, you can visit and learn more…

In the upcoming posts, i shall show you how to display this on your Project Site in a webpart.

Feel free to post your comments, if you need any assistance.

Hope this was useful!!

  1. I know this if off topic but I’m looking into starting my own weblog and was wondering what all is needed to get set up?

    I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet smart so I’m not 100% sure. Any tips or
    advice would be greatly appreciated. Thanks


  2. the full report

    Building Excel reports from Project Server 2013 OData Services | The Relentless FrontEnd – Aackose Lal’s Blog


  3. I constantly spent my half an hour to read this web site’s articles or reviews daily along
    with a mug of coffee.


  4. I leave a leave a response each time I like a article on a website or if
    I have something to contribute to the discussion. Usually it’s
    a result of the fire displayed in the post I read.
    And on this article Building Excel reports from Project Server 2013 OData Services | The
    Relentless FrontEnd – Aackose Lal’s Blog. I was actually moved enough to drop a thought 🙂 I do have a
    few questions for you if you tend not to mind. Could it be just me or does it appear like a few
    of these responses come across as if they are left by brain dead visitors?
    😛 And, if you are writing on additional online
    sites, I would like to follow you. Could you make a list the complete urls of all your community
    sites like your linkedin profile, Facebook page or twitter feed?


  5. I every time spent my half an hour to read this blog’s articles everyday along with a cup
    of coffee.


  6. Oh my goodness! Incredible article dude! Thanks, However I
    am experiencing problems with your RSS. I don’t know why I
    can’t subscribe to it. Is there anyone else having
    the same RSS issues? Anybody who knows the answer will you kindly respond?


  7. […] This post is in continuation to my previous post on Building Excel reports from Project Server 2013 OData Services. […]


Feel free to leave a reply here...

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

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

Facebook photo

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

Connecting to %s

%d bloggers like this: