OData Reporting in PWA

Repost of Mike McLean’s Reporting Post.  Mike was the Program Manager with the Microsoft Project team at time of publishing.

Today we’ll discuss some of the changes to server reporting in the next version of Project Web App (PWA), including an introduction to the new OData service.

Historically, users access data in PWA by going directly against the database or via cubes available on-premises. With the launch of Project Online this release, we also have users storing data in Office 365. To provide access to this data, we’ve built an OData service that can be used to retrieve data stored in your instance of PWA. This OData service is available for both online and on-premise deployments. Excel 2013 now has native support for OData feeds and can authenticate to Office 365 to retrieve data from Project Online. More information on OData can be found at http://www.odata.org/.

The OData service is accessed via a URL and can be found by appending /_api/ProjectData to the location of PWA.

For example, if my Project Online URL is:

https://contoso.sharepoint.com/sites/pwa

My OData service can be found at:

https://contoso.sharepoint.com/sites/pwa/_api/ProjectData

Since the OData feed is a web service, you can navigate to it using your browser. This will help verify you have access to the OData feed. In SharePoint Permission mode, Portfolio Viewers, Portfolio Managers, and Administrators have access to the OData feed. In Project Permission mode, access to the OData feed can be explicitly granted to users.

When accessing the URL, a page will display listing all entities available in the Project OData feed. Entities are high level items such as Projects, Tasks, Risks, Issues, etc. These entities will be the items we select in Excel 2013 when building our reports.

Note: You may need to turn off “Feed reading view” through Internet Options for the entities to display!

Here is the full list of entities:

AssignmentBaselines

PortfolioAnalysisProjects

RiskTaskAssociations

AssignmentBaselineTimephasedDataSet

PrioritizationDriverRelations

TaskBaselines

Assignments

PrioritizationDrivers

TaskBaselineTimephasedDataSet

AssignmentTimephasedDataSet

Prioritizations

Tasks

BusinessDriverDepartments

ProjectBaselines

TaskTimephasedDataSet

BusinessDrivers

Projects

TimeSet

CostConstraintScenarios

ProjectWorkflowStageDataSet

TimesheetClasses

CostScenarioProjects

ResourceConstraintScenarios

TimesheetLineActualDataSet

Deliverables

Resources

TimesheetLines

Issues

ResourceScenarioProjects

TimesheetPeriods

IssueTaskAssociations

ResourceTimephasedDataSet

Timesheets

PortfolioAnalyses

Risks

Each entity has a set of properties associated with it. For example, every Project will have an ID, Start Date, Finish Date, etc. By appending /$metadata to the end of the OData url, you can review the full list of properties available for each entity. This is the easiest way to review exactly what type of data can be consumed using the Project OData feed.

In Project 2013, there are a few sample reports included which are already connected to the OData service. In subsequent blog posts, we’ll go into specifics around OData syntax. For this post, we’ll walk through how to find these reports and refresh them to display your data.

On the PWA homepage, click “Reports”:

This will navigate you to the Business Intelligence Center for the PWA. Click “Reports”:

You’ll see a list of folders for each language available. Click the desired language:

Here you’ll see three Excel workbooks which have connections to the OData feed (for on-premises deployments you’ll also see the SQL based reports). “Project Overview” and “Resource Overview” both have connections to multiple entities, demonstrate using relationships in Excel 2013, and include charts and graphs. “Project Overview Dashboard” is very similar, but uses the new Power View technology available in Excel 2013.

Click “Project Overview” and select “Edit” to open the report in Excel 2013:

Excel may require you to authenticate. Click “Organization or School”. Then enter your onmicrosoft.com User ID and password and click “Sign In”:

Once Excel has opened the file, click any yellow bars to enable Editing and enable Content. The report will be empty because it is a template that can pull your data. On the Data tab, click “Refresh All”:

You will see Excel 2013 retrieving data:

Once the data has been retrieved, the report will populate:

The report can now be saved back to PWA by selecting File > Save As > Current Folder:

Quick reload of PWA shows the newly refreshed report saved with a new name:

Refresh the Power View workbook in exactly the same way, just make sure you click the tab “Sheet1” before clicking Data > Refresh All. The data will be retrieved into the Excel workbook model, which is then consumed by Power View:

Since the OData service enables REST queries, OData can be used to retrieve data in other scenarios in addition to Excel 2013. Client applications, SharePoint apps, even mobile apps can take advantage of OData. These apps can be built using the .NET framework, JavaScript, and other languages or environments.

Here are some other useful OData links.

Office.com topic:

http://office.microsoft.com/en-us/project-server-help/use-excel-2013-to-create-a-new-project-web-app-report-HA102923779.aspx

MSDN article:

http://msdn.microsoft.com/en-us/library/jj163015(office.15).aspx

---

PPM Works is a full service Microsoft Project and Portfolio Management consultancy, focused on Microsoft Project Server and Microsoft SharePoint platforms.  We believe in partnering with our clients to exceed expectations and build long lasting rewarding partnerships. We are continuously building a knowledge base of multiple tips, tricks and solutions to make your use of Microsoft Project Pro as quick and easy as possible. Contact us for more information.

Stay updated with the latest of Project Online/Server 2013 with us: Subscribe. Check our future events!

Subscribe to our webcast email list for updates on our upcoming webinars! 

Visit our FAQ Page for access to our tool box.

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]