Untitled

In a recent MPUG Web-n-Learn, I demonstrated building a quick Timesheet Status Report using OData in Project Online. 
The report itself is not too complicated to build, but it helps show another example as to how OData could be used for reporting.  This could also very easily be replicated for Project On-Premises as well.
I will assume that you are using Project Online, and have Timesheet data populated.
  • Start by opening up Excel 2013, and navigating to Data >> From Other Sources >> From OData Data Feed
image
  • On the Data Connection Wizard pop-up, type in the link of your project online tenant, in this format, and click Next.
SNAGHTMLb0fabf1
This will bring up a list of all the data tables that are available to you for reporting. Select Timesheets from that, Click Finish to , complete the data connection wizard .
SNAGHTMLb1280fb
You will be prompted to select one of the options to pull the data in. For now, select Table.
SNAGHTMLb14574d
This will bring in all the data from your Project Online Tenant, as it exists in the Timesheets DataSet. This might take a few minutes, depending on the amount of data you have, which is why it is important to learn how to filter the data via the query BEFORE you consume it. (a post for some other day).
image
Now select all the data, and click on Insert >> Pivot Table, and select to place the data in a new worksheet.
image
Once the Pivot Table is created, set the fields as shown. We will be reporting by “Week Ending”, so I chose the period end date. Alternatively, you could use the Start Date as well.
image
Once, all the fields are in place, you will see something like this.
image
The value TimesheetStatusID represents the status of Timesheets, in number format. Here is the legend for the status ID.
image
Knowing this, we could now do some conditional formatting, based on these values. Click on Conditional Formatting button on the ribbon, and select Manage Rules.
image
Setup your rule, as below. Obviously, my conditions do not cover every possibility (like Pending Submit etc.,) , but I leave that up to your excel skills to come up with the conditional formatting rules.
image
This will turn the table to something as below.
image
Now, clean it up and Jazz it up a little, and publish to Project Online, to get a report like this:
image