Posted by: EPM Partners on April 17, 2013
Introduction
Tracking project and/or portfolio progress, and forecasting future performance are the cornerstones of all project management methodologies. While the reporting requirement itself is common and generic in nature, every organisation has specific PM practices which demand unique reporting that will result in the requirement for custom-developed Project Server reports. To meet this need, Microsoft Project Server has always provided reporting tools with rich capabilities to both create and distribute traditional or analytical reports, and to empower users to generate their own ad-hoc reports based on unplanned requirements.
Project Server 2013 has introduced some new reporting mechanisms. In addition, reporting possibilities of the cloud version of Project Server (Project Online) are not exactly same as its on-premises counterpart. It is essential therefore to analyse, plan and architect reports according to the deployment option selected.
This article summarises the key reporting capabilities of Project Server 2013 for both an on-premises deployment and for Project Online.
Data Access
Querying the reporting database
Accessing the reporting data directly by querying the reporting database is an available option for an on-premises deployment. This capability is not available in Project Online. The four databases in Project Server 2010 (draft, publishing, archiving & reporting) have been merged into a single database in Project Server 2013. However, reporting table names have not been changed and therefore, reports written for the Project Server 2010 schema are compatible with Project Server 2013.
oData
The oData service exposes Project Server reporting data via a web service interface. The query is passed via a URL and results are returned as an XML document which can be consumed by Excel 2013, as well as other client-side applications.
The oData query syntax supports REST and LINQ formats which covers selecting, filtering, sorting, joining, etc. But for complex SQL queries (with nested queries and many joins), the effort to write and test the queries will be significantly higher. Below is an example of selecting all projects in a PWA instance with only the column ProjectName in REST syntax:
http://[ServerName]/[PWA Instance Name]/_api/ProjectData/Projects?$Select=ProjectName
See the following link to learn more about querying OData feeds for Project Server 2013:
http://msdn.microsoft.com/en-us/library/office/jj163048.aspx
OLAP Databases
OLAP databases are primarily used to analytical reporting. These are available for on-premises deployments only and do not contain any significant enhancement from previous Project Server versions.
Data Visualization
SQL Server Reporting Services (SSRS)
SSRS is a tool of choice of for developing traditional and user interface-rich reports. SSRS does not natively support oData feeds. Though it is possible to consume oData feeds using XML data sources, claims-based authentication model of Office 365 makes it even more difficult to consume hosted Project Server data. Hence this tool will mainly be used for on-premises deployments.
Many EPM solution providers have developed significant numbers of SSRS-based reports for previous versions. Porting such reports for the hosted version may require considerable effort.
Excel 2013
Excel 2013 has native support for oData consumption and the Project Server 2013 hosted version comes with pre-installed oData-based data connections which are utilised by Excel-based reports. Using the oData (or direct SQL data via SQL Query) capabilities of Excel 2013, tabular, graphical and pivot table-based reports can be created and published on SharePoint using Excel Services. For on-premises solutions, Excel pivot tables can also connect to OLAP cubes.
Excel 2013 with Power View
Power View is an interactive, data visualisation and exploration add-in for Excel 2013. This tool enables power users to create information-rich and interactive dashboards based on data models created in Power Pivot (Power Pivot can in turn be a consumer of oData feeds.)
Power View combines the interactivity of analytical reports like slicing, dicing, drilling up/down with rich user interface elements like Dashboards, Maps, Charts, Graphs, Tables and Images. Power View is the key reporting differentiator from previous Project Server offerings as it empowers day-to-day users to create attractive and useful dashboards. See the following links to learn more about Power View capabilities.
http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx
Other reporting tools
Any reporting tool capable of connecting to SQL Server (or to OLAP cubes) directly can be used for on-premises deployment. Examples include Performance Point, Dundas Charts, Fusion Charts, etc.
There are very few specialised tools available which consume Project Server oData feeds natively. However, the open nature of Web Services allow developers to write their own custom applications to consume data and create custom reporting. Developers can also use CSOM (client side object model) to access data which is not available via oData feeds (oData only provides data which is available in Project Server reporting database tables and views.)
Project 2013 Client Side Reporting.
The Project 2013 client comes with many new reports. Creating dashboard-based reports from single project data is intuitive and easy. This is going to be a handy feature for project managers who want to report project progress on an ad-hoc or periodic basis. These reports are good for printing, presentations and sharing via email, but unfortunately cannot be published over PWA.
Summary
Following table summarises various available technologies for Project Server 2013 reporting for both on-premises and hosted versions.
| Technology | On Premise | Online | Comments |
| Data Query | |||
| Directly Quering SQL Server Reporting Database | Yes | No | |
| oData | Yes | Yes | |
| OLAP Database | Yes | No | Same capabilities as PS 2010 |
| Data Visualization | |||
| SQL Server Reporting Services | Yes | Yes | Online version requires more work |
| Excel 2013 – Pivot Tables & Charts | Yes | Yes | |
| Excel 2013 – Power View | Yes | Yes | |
| Other Tools | Many | Few |
Comments Off on Project Server 2013 – Reporting Story
