Display Project Server 2013 Excel Reports in your SharePoint Site


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

Now that you have your Excel Report and started being innovative in building jazzy reports and dashboard, you might want to display it on your homepage so that you could get the snapshot of everything when you login online.

The only requirement for this is to ensure that you have “Excel Web App” installed and the required features enabled.

Step 1: Upload your excel reports to a Document library and open it. This will open up the Excel report in a WOPIFrame page, with the URL as:
https://servername/sites/pwa_name/ProjectBICenter/_layouts/15/WopiFrame.aspx?sourcedoc=/sites/pwa_name/ProjectBICenter/Sample%20Reports/Project%20Overview%20Dashboard.xlsx&action=default

Office Subscription Screenshot Excel Web App

WopiFrame

 

 

Step 2: Copy the above URL to your clipboard.

Step 3: Edit your Project Site, add a Page Viewer webpart and set the type as “WebPage” and “URL” as the one copied from step 1.

Outcome: The outcome will be something like this

ExcelReports

 

Hope this helps!!

 

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