Connecting Excel to Project Server OData

This Blog will help you to get data from Project Server really fast using Excel to create pivot tables, charts or just slice and dice your data and use any other features that Excel offers.

Prerequisites

• Project server 2013
• Office Excel 2010 or 2013 (in our Blog we are using 2013)
•  Permission to access to Project Server OData Feed

*If you don’t have access to the Project Server OData feed please contact your SharePoint Admin

Let’s Begin

First of all I want to talk a bit about OData, so what is OData?

“The Open Data Protocol (OData) enables the creation and consumption of REST APIs, which allow resources, identified using URLs and defined in a data model, to be published and edited by Web clients using simple HTTP messages.” Source: www.odata.org

In Project Server 2013 Microsoft made ProjectData (WCF Data Service, also known as an OData service) available to us so we can use it to  make both online and on-premises queries of reporting data from a Project Web App instance, and we will be using that today in our example. (Source)

To have a better understanding of the Project Server OData service please visit http://msdn.microsoft.com/en-us/library/office/jj163015(v=office.15).aspx

Alright now we have a better understanding of the Project Server OData service lets connect our Excel and get some data.

1- Open Excel and click on the DATA tap on the Top

2- Click on the From Other Sources button and choose From OData data Feed

3- You will get the Data Connection Wizard, go ahead and put the Odata URL , in out example we will get the Project List as you will see in the screen shot below

4- You will also have the option to use the sign in that you are using for your account or other log in credentials in my example will be using my windows credential, click next

5- Select the table that have the data you want, in our example it will only be one which is the Projects table

6- Brows and save your connection and then click Finish

7- We will get the Import data window with all the options of how to display our data, I am going to choose the Table option and click OK

And here is our final table

Thanks

Interested in how EPMA can help your schedules? Contact us today at 1.888.444.EPMA or enroll for one of our training classes http://www.epmainc.com/epma-institute/course-listing

Please feel free to leave comments below or check out our other blogs on Microsoft Project, Project Server, SharePoint and Project Management Methodology.

Follow us at @EPMAinc, linkedin, or facebook