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” ribbonClick “Browse for More” button at the bottom left corner paste the above copied URL in the “Filename” section 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 OData.org 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!!
The Relentless FrontEnd - Aackose Lal's Blog 
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
the full report
Building Excel reports from Project Server 2013 OData Services | The Relentless FrontEnd – Aackose Lal’s Blog
I constantly spent my half an hour to read this web site’s articles or reviews daily along
with a mug of coffee.
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?
I every time spent my half an hour to read this blog’s articles everyday along with a cup
of coffee.
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?
Thanks!!
Reblogged this on Sutoprise Avenue, A SutoCom Source.
[…] This post is in continuation to my previous post on Building Excel reports from Project Server 2013 OData Services. […]