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