Project Server 2013 – Reporting Story

Posted By 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

http://pwmather.wordpress.com/2013/03/01/projectserver-powerview-report-in-excel-2013-ps2010-ps2013-office2013/

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

 

 


Blog Posted In Blog Posted In: Project Server, Reporting
Blog Posted In Comments Off on Project Server 2013 – Reporting Story