Microsoft Project Online Timesheet Reporting using Power BI Desktop

by trebgatte Member on ‎11-11-2015 01:52 PM

Visualizing Project Timesheet Information

Project Online supports collaborative work management across your organization. It provides the ability to manage multiple project schedules and allows project team members to enter the time spent on assigned tasks via their timesheet. The timesheet information can then be used for many purposes.

 

Many Project Management Offices (PMOs) have dynamic information needs that require data interactivity to uncover the sought out answer. Power BI Desktop is the perfect tool to enable the data exploration process. In this article, we’ll focus on the PMO need to audit timesheet data, exploring who has submitted time and what types of time were submitted. Below is an example of an overall timesheet status dashboard, created in Power BI.

 

PowerBI Timesheet Dashboard.png

 

Success takes the whole village

Getting everyone to submit their timesheet in a timely manner is key to getting full value from time tracking. Thus, auditing timesheet submissions is an integral part of a successful time tracking.

 

The village faces challenges

Many organizations face three time tracking challenges.

 

First, it’s difficult to get the entire organization to fill out their timesheet in a timely manner. Sometimes, you aren’t in the office on Friday so the timesheet doesn’t get submitted. Some people will simply forget to submit their time. Others may be more passive aggressive in that they view time tracking as a tax with little individual benefit.

 

Second, Project has no included functionality for easily auditing timesheet submission. If you want to use time tracking, you have to build your own business intelligence views to meet your analytic needs.

 

Lastly, reporting tools of years past focused on solving a particular business problem. They were not optimized for interactivity to meet immediate and specific data needs. Below is an example of a dashboard for investigating where timesheets have gone missing.

 

PowerBI Timesheet Investigate Dashboard.png

 

This article will take you through the process of defining your business need, show you how to leverage a social design approach to design the solution and take you through the steps to address the need in Power BI.

 

Designing for Business Intelligence

Business intelligence answers the questions that support specific organizational conversations. Experience in BI Design indicates that mapping the social interactions first, will increase the chances that the designed visualizations will meet the business need.

 

Using Tumble Road’s Conversation-Centric Design (CCD) approach for designing Business Intelligence solutions, the technical design details are derived iteratively. First, we model the targeted social interactions. Three key aspects are defined in this step: the audience, the conversations and the key questions. Once these aspects are defined, you can use these to define the data scenarios, individual data elements, common user terminology, and supporting processes.

 

Requirements and Design

For this exercise, I’m limiting the design to one primary audience, the Project Management Office, and three key questions for the PMO.

 

Requirements User Scenario

The previous week’s timesheet results for projects and administrative time are reviewed in the Monday morning PMO team meeting. Claire, the director, leads the meeting, where she reviews the results with Stephanie and Ian. The data is reviewed by open timesheet period. The outcome of the meeting is to follow up with the individual team managers to complete the process. Claire also uses the data to generate PMO reports to the company vice presidents. Therefore, she wants to have complete data as much as possible to generate the reports.

 

Key Organizational characteristics

  • All timesheet eligible resources turn in a weekly timesheet even when they are not currently assigned to a project as the organization uses Administrative Time Categories to track time away.
  • The PMO is only concerned with total time charged to a project and only the standard time category is used.
  • The weekly timesheet periods run from Sunday to Saturday each week.
  • Timesheet periods are closed after 60 days

Jason and Sophie need the timesheet data for their own uses with Finance and HR but are passive observers to the review process.

 

Conversation

The business intelligence visualization is needed to support the PMO weekly review of timesheet data at 9AM on Mondays.

 

Audience

The PMO consists of five people, one director and four analysts. Claire, Stephanie and Ian are direct consumers of the data and are the primary audience of this exercise. Jason and Sophie are indirect consumers of the data. Claire, Stephanie and Ian, therefore, will be best choices for assessing whether the visualization meets their needs. Stephanie and Ian can also verify the data.

 

Key Questions

From the scenario above, we can derive the following key questions. NOTE: There are typically more than three but I find three is a great starting point.

 

  • Have all timesheet eligible resources submitted their timesheet?
  • Have all submitted timesheets been approved?
  • Which teams have outstanding timesheets?

 

Technical Design and Implementation

Now you seen the business need and requirements. To see how this is translated into a technical design and the final implementation of this reporting, read the full guide.

Comments
by CheenuSing New Contributor
on ‎04-24-2016 11:18 PM

I am having a table which has year and stage number as columns.  I want to build a slicer on year only for those stage number having values > 0, how to achieve this in Power BI.

by jklinger Visitor
on ‎08-31-2016 05:16 PM

I attempted to enter the code on the tumbleroad page.  I cannot get past 1 error on the timesheet periods query.  Can someone give me some suggestions on how to fix this.

bi error.PNG

 

 

by onlinetimesheet Visitor
on ‎10-27-2017 12:44 AM

This is very informative blog, Thank you so much for  sharing positive tought.

online timesheet Visualizing Project Timesheet Information

by softwareTr01 Occasional Visitor
on ‎01-23-2018 01:21 AM

This is really a wonderful blog on Online Time Tracking software and also on Track My Time technique. Please keep on posting these topics so that we will keep ourselves updated.

by softwareTr01 Occasional Visitor
on ‎02-23-2018 01:24 AM

I am very thankful to you for the wonderful article. Please keep posting these topics. I am very glad to read. For more info visit:- Livetecs LLC