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:

/sites/pwa/ProjectBICenter/Data%20Connections/Forms/AllItems.aspx?RootFolder=%2Fsites%2Fpwa%2FProjectBICenter%2FData%20Connections%2FEnglish%20%28United%20States%29&FolderCTID=0x012000E9798414DD31E84AB759BED87428492E&View=%7B17A6948A-A122-4B2E-9398-FC156E6BE875%7D&InitialTabId=Ribbon%2ERead&VisibilityContext=WSSTabPersistence

Image

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.

Image

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

Image

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

Image

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

Image

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!!

8 thoughts on “Building Excel reports from Project Server 2013 OData Services

  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. Pingback: the full report
  3. 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?

  4. 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!!

Feel free to leave a reply here...

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