Microsoft Project Online – Manipulating OData using $select and $filter

By Darin Brazile

Note – By Request, this was also made into a downloadable White Paper and can be found here:  http://dld.bz/d755H

Microsoft’s cloud based Project and Portfolio Management offering, Project Online, has brought a traditionally enterprise level tool to the masses.  Small to medium size companies can now afford to move their portfolio management from excel to a bona fide system.  The previous barrier to entry, purchasing hardware, has been removed by Microsoft hosting the data.  Since the data is no longer onsite, the Open Data Protocol (OData) was chosen as the method for exposing the data for consumption.

Before continuing with this post, please read how to create an OData connection to your Project Online instance:  Create OData Connection

Today we will focus on editing the data string.  I will use two simple methods that are easy to replicate.  I have chosen the projects table and created a sample report that displays a project list with durations.  Think of this as one pane of a dashboard report.  To speed data transfer, I will limit the data pull to only the data displayed.  I will then clean up my data, by filtering out unwanted projects.

 

 

 

Connection String

1. Open the report or Excel file to modify.
2. Click on the Data
3. Select Connections.
4. In pop-up window, select desired connection (in the example, I only have one to select from).
5. Click Properties.

 

 

 

 

6. Click on the Definitions tab.

 

Edit Odata String – $select

I want to target only the data that I intend to display.  I will use the $select query option to do so.

 

Experience tip:  Copy the connection string into OneNote, Word or Excel to make the modifications as a means of saving the original and safe keeping of the one that works.

 

The syntax of my $select statement looks like this:

 

 

The resulting data set:

 

 

Edit Odata String – $filter

Two things stand out to me, the order and formatting of the data and the data included. We will deal with the data that is included first.  I do not want any test projects nor any administrative projects.  I add a $filter statement to my query:

 

 

 

The resulting data set:

 

 

Notice that two projects were removed, ZZ Test Delete Site and Timesheet Administrative Work Items.

Report Finalization

I want to make my mini-report or dashboard tile more readable so I perform some formatting work.  My resulting report:

 

For more information about Odata, visit the following links:

http://www.odata.org/documentation/odata-version-3-0/url-conventions/

http://www.odata.org/documentation/odata-version-3-0/odata-version-3-0-core-protocol

Comments