This is part 9 of the “Getting started with Project Online” series and the final part. This should have been posted months ago but I didn’t realise I missed it, sorry for the delay! In this post we will look at the reporting options for Project Online, specifically Project ODATA and Excel / Excel Web App. The focus will be on the ODATA queries rather than the presentation layer. The post will also touch on other reporting options for Project Online. In the last post we looked at the different places in PWA where data can be viewed and edited. If you missed the last post, see the link below:
Firstly a bit of background for Project ODATA. The Project Online / Project Server ODATA feed was introduced by Microsoft to act as an access point to the Reporting schema in the Project Web App database. For those of you who are familiar with Project Server (On-prem) most of the reports would have read data directly from the Reporting tables / views in the Project Server database using T-SQL. When Project Online was introduced as a cloud based solution, direct database access was not possible for many reasons but security / access being the main two. The answer was to build an API that accessed this data, that is where the ODATA feeds come in. ODATA is an open data protocol that provides HTTP/REST access to the Project Online reporting database schema. The Project Online ODATA feed is accessed via the PWA URL, append /_api/ProjectData to the end of the URL. For example http://Server/PWAName/_api/ProjectData – this is known as the service root URI (Uniform Resource Identifier). Project ODATA is available for Project Server 2013 (On-Prem) but you typically wouldn’t use ODATA on-prem for reports as you have the SQL server access. Also Excel reports that use ODATA on-prem will not refresh in Excel Services, they will only refresh the data in Excel. The ODATA feed is permission controlled, to access this API the user will need “Access Project Server Reporting Service” – this should be granted via a Project Server security group / SharePoint security group depending on what permission mode your PWA instance is using.
When building new reports always start in Internet Explorer (or a browser of your choice 
) to fully test that the ODATA URLs work before taking these to Excel. Now we will move to Internet Explorer to see the data. Starting at the root /_api/ProjectData you will see all of the possible endpoints available:
If the user sees the following:
Disable the “Turn on feed reading view” in Internet Explorer.
To see all of the properties (fields etc.) available for each endpoint, use the metadata option. Append /$metadata to the URL /_api/ProjectData/$metadata:
Using these two URLs will help you build the URLs you need to meet the report requirements.
When querying any data source it is best practice to only return the data you need, filter out the data that is not required. This will make the report more responsive for the end user. The same applies with ODATA. Don’t just add the all of the ODATA endpoints (tables) to Excel and filter in Excel as this will not be efficient. Using this approach, Excel will download all of the data to the client then filter after, you want to filter at the source before the data is in Excel. The key options to use for optimising the ODATA queries are $select and $filter query options. The select query option will be used to select the properties (fields) that you want to use, an example can be seen below:
/_api/Projectdata/Projects?$select=ProjectName,ProjectId,ProjectCost,ProjectWork
To see what this does, see below:
With a select query option:
Without a select query option:
As you can see, using the select query option less information is returned, you only get the information you want rather than all the details that you probably don’t need. Depending on how much data you have in the PWA instance you will notice how much quicker IE returns the data when using the select query option compared to returning all properties. The next query option to look at is the $filter option. This will be used to filter the data returned. Before we look at the filter query option, there is something else to show for filtering. Some of the ODATA endpoints accept parameters, using the Projects() feed as an example. When loading the Projects() data you will see example URLs to show you how to access data for that particular project, see the line highlighted below:
So using /_api/Projectdata/Projects(guid’f68e1341-50b0-e311-942e-00155d1521a1′) only data for that particular project will be returned. You would replace the GUID for the correct Project GUID in your PWA instance:
That can then be combined with the $select query option:
/_api/Projectdata/Projects(guid’f68e1341-50b0-e311-942e-00155d1521a1′)?$select=ProjectName,ProjectId,ProjectCost,ProjectWork
To filter ODATA queries, the filter query option is used. A common filter would be:
/_api/Projectdata/Projects()?$filter=ProjectType ne 7
This would filter out the timesheet project row. Another examples would be:
/_api/Projectdata/Projects()?$filter=ProjectType ne 7 and ProjectCost gt 15000
This would filter out the timesheet row project but also projects where the cost was less than £15000:
For demo purposes so that you could see the projects and select was also added:
/_api/Projectdata/Projects()?$filter=ProjectType ne 7 and ProjectCost gt 15000&$select=ProjectName,ProjectId,ProjectCost,ProjectWork
There are many functions and operators to create the filter logic, see the filter sections on the URLs below for examples:
http://www.odata.org/documentation/odata-version-3-0/url-conventions & http://www.odata.org/documentation/odata-version-2-0/uri-conventions
There are also other query options available to use such as $orderby and $top, you will find details on those in the links above also. Some Project specific examples are below:
/_api/Projectdata/Projects()?$orderby=ProjectWork
/_api/Projectdata/Projects()?$top=5&$orderby=ProjectCost desc
Project ODATA also has navigation properties, for example, you might want to get all risks associated with a particular project:
/_api/Projectdata/Projects(guid’78732475-eaf5-e311-be98-4c809328175b’)/Risks
That returns all of the risk properties (fields), to only select the properties you need use the select option:
/_api/Projectdata/Projects(guid’78732475-eaf5-e311-be98-4c809328175b’)/Risks?$select=ProjectId,ProjectName,RiskId,Title,Category,AssignedToResource
That should be enough information to get you started on building efficient ODATA queries, for more details see:
http://www.odata.org/documentation/odata-version-2-0/uri-conventions/
http://www.odata.org/documentation/odata-version-3-0/url-conventions
http://msdn.microsoft.com/en-gb/library/ff478141.aspx
http://msdn.microsoft.com/en-us/library/office/jj163048(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/jj163529(v=office.15).aspx
Once you are happy that the ODATA queries are efficient as possible and returning the correct data in IE, these can be added to Excel so that you can generate your data models, relationships and reports. I wont cover that part in this post as it has been covered before here:
Another example is here:
****Update 2014/09/14****
See the Reporting Pack that is available now:
******************************
Remember to get Project Online ODATA Excel based reports to refresh the data in Excel Web App you will need to enable the feature detailed here:
As well as using Excel for the reports there are other options, for example you could use SSIS to export the data to a SQL database then use SSRS:
Or you could create reports in SharePoint/PWA pages using JavaScript, some examples here:
That brings us to an end of the getting started series, there will be a summary post / quick reference guide following this.
I hope this has proved beneficial for those that are just getting started with Project Online
Modern Work Management - Project and the Power Platform blog 
Excellent!! Covered all possible ways of Reporting in Project Online.
Thank you very much.