What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: Step By Step to create a Timesheet Status Report with OData in Project Online
Select a size
Step By Step to create a Timesheet Status Report with OData in Project Online
This article will walk you through a step-by-step procedure on 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.
Creating the Data Connection
Start by opening up Excel 2013, and navigating to Data >> From Other Sources >> From OData Data Feed
On the Data Connection Wizard pop-up, type in the link of your project online tenant, in this format, and click Next.
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 .
You will be prompted to select one of the options to pull the data in. For now, select Table.
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 (not covered in this article).
Creating the Pivot Table
Now select all the data, and click on Insert >> Pivot Table, and select to place the data in a new worksheet.
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.
Once, all the fields are in place, you will see something like this.
The value TimesheetStatusID in the ‘value’ section of the Pivot Table, represents the status of Timesheets, in number format. Here is the legend for the status ID.
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.
Setup your rule, as below. Obviously, my conditions do not cover every possibility (like Pending Submit etc.,), but you could use Excel Conditional Formatting Skills to cover those scenarios.
This will turn the table to something as below.
Now, for the cells that do not have any data; in this context, it means that the Timesheet has not been created by the resource. It would be nice to display some text in that empty space.
To do that, right click on the pivot table, and select Pivot Table options.
In the box, which says For empty cells, show: type “Not Created”. Also, give a meaningful name to the Pivot Table, as shown.
Setting Browser View Options
Now, before we publish it to Project Online, we will set some Bowser View Options. To do this,
Navigate to File >> Brower View Options
Set your Options as shown below, and click OK.
And finally, publish to Project Online, by clicking File >> Save and selecting your Project Online BI Center.
Navigate back to BI Center and you will see your report like this.