Blog

May 2, 2017

How to fill in 100 timesheets in 2 minutes

This post is about how to fill in the timesheets in 2 minutes instead of 32 and how to implement automated work time reporting through importing data to Microsoft Project Server from such tracking systems as TFS, Redmine, and Jira.

The article will be useful for project managers, heads of development companies, and software developers interested in integration of different project management systems.

Issue — mess in timesheets filling

For 99% of the software companies recording of work time is as important as air to calculate costs. That is the reason why in many companies employees are compelled to fill in the timesheets. Quite often it means that they do it manually – fill big tables with the tasks and spent working hours.

99% of software developers hate this procedure. What an excellent Friday evening — 30 minutes of sitting and copy/pasting all the week tasks from the tracking system into the timesheet. And, as we know, when people do stupid and hated work, the result is usually not good. This is what happened to our company, Friday timesheets were far from perfection. How did it look like?

First, the timesheets did not reflect all production tasks solved by the developers. Developer could just write that he spent all 40 hours for a particular project. Or by mistake he could mix the tasks and projects.

To fight with this mess our great colleague from PMO (whose photo, undoubtedly, would decorate this article, if we published it) gently, but persistently pressured 100 people every Monday. Then she verified 1200 strings of all timesheets according to a long checklist. And only by Tuesday PMs had a full picture.

Result: it took 3 working days to get the report for the previous week.

How it was. Timesheet collecting procedure.

More than three days

Solution — automation of timesheet filling

The solution took shape gradually. Our ideas were the following: if developer submits all the work in the issue tracker anyway, then why not to take data on tasks and working hours directly from there? It would allow obtaining information quickly for the analysis of labor costs, personnel document flow and other management tasks. Then was the easy part – finding a beautiful technology solution on integration of our trackers with the system in which we record working hours (Microsoft Project Server, find more about it).

How it is now. Procedure of timesheets filling

Up to 100 items in 2 minutes! Total Time data collection — 2 hours and 10 minutes.

What we got?

In MS Project Server we’ve added a magic button which allows making time-sheet in 2 minutes. On Friday you log in Project Server, and it automatically builds timesheet by week data from the tracker. It is only left to add data on compensatory holidays, vacation, and sick leave if there were any. And voila — you press the button, confirm that everything is correct, and the timesheet is ready.

 

It is necessary to mention that magic works only for those who carefully followed technology of maintaining projects in the tracker during the week: submitted tasks with planned and actual time spent for their performance. These good guys receive a plus to Friday-karma and automatic filling of the timesheet.

Automatically filled timesheet

Bonuses of magic button

1. We saved 200 working hours

If earlier an employee spent 30-40 minutes to fill timesheets in, now it is just 2 minutes. And if to count it for 100 people per month, so we save about 200 working hours.

And not to forget about 32 more working hours spent on checking the correctness of the timesheets. Now it is only 4 hours a month. Time saving is easily explained: now in timesheets we have real tasks imported from trackers, not created manually.

2. We get all actual analytics weekly

Now at 7:00PM on Friday 91% of all timesheets are completely filled, and managers have the analytics of labor costs by projects at the end of the week. To compare, before by this time we had less than a half.

3. We unified management approaches for all projects

Now in all tracking systems all projects, clients and other data are designated universally. There are figures for the planned and actual time for task performance everywhere needed. And it reduces our risks and increases project control.

4. We simplified control of project management

Before, to understand how the project was managed, it was necessary to check trackers manually. Now particular project management standards can be controlled automatically at the moment of timesheet creation. For example, if the tasks in Redmine do not have statuses and time estimates, they will not be imported into timesheets. To the contrary, if the PMO see correctly completed timesheet – it means that its author filled all required fields in the tracking system.

In fact, we got the whole business process re-engineered. We equipped our working process as it is convenient to us with our complex projects.

5. We get analytics by any parameters every Friday

Initially, managers received needed analytics only by next Tuesday-Wednesday, now 90% of information from timesheets is collected at the end of the current week, on Friday evening. On the basis of it we build OLAP cubes.

How many bugs were fixed during the week? How many hours were spent for each of the projects? Where there were bottlenecks and why? It is possible to analyze data more quickly now. But it is not a limit. Next time we will tell about how it is possible to obtain summary data on all projects from trackers real time by the means of the advanced OLAP cube version.

And now it is time for detail, flesh, and pieces of code.

Automation of business processes step-by-step

Step 1. We standardized project management in different tracking system

The issue was that in our company for management of projects which are different in technology stuff, we use three different trackers (systems for project management). It is required from the point of technologies and project specifics

Standards of project management in different trackers varied a little. For example, for different projects different workflows could be accepted. Or the complex project for the same customer could be named in mobile department (in Redmine), say, «The mobile app for the cool client of N», and in web department (in TFS) — «The internal website of the company N».

So we had to polish this development zoo for integration by checking that in each system identical projects were given identical names, tasks estimates were specified everywhere and the general methodology of management was applied.

At the technology level it demanded implementing some new features for trackers. For example, in TFS there is no column to enter a number of hours you spent for a task on certain date.

Step 2. We created a page which imports records in MS Project Server

Let us tell you how to create such a page. First, a short plan of what needs to be done:

  1. Add button to MS Project.
  2. Authorize in issue tracking system and read reported hours from there.
  3. Display the received data to user for checking.
  4. Put the data to MS Project.
2.1. Button

Let’s look at how one can expand the MS Project Server interface. As you all know, MS Project Server works over MS SharePoint Server, so it is possible to use the features of the last to add various elements to MS Project interface. In particular, it was necessary for us that user started import directly from the timesheet page. For this purpose it is needed to add a special button to timesheet Ribbon and to bind it with the corresponding action. To deploy it in MS SharePoint, you should create Feature containing a Custom Action element.

<CustomAction Id="EBT.MSP.TimesheetSync.ImportFromRedmine.CustomAction"
          Location="CommandUI.Ribbon"
          Title="Import data from RedMine" >
          …
</CustomAction>

Inside this CA we identify the corresponding extension.

<CommandUIDefinition Location="Ribbon.ContextualTabs.Timesheet.Home.Sheet.Controls._children"></CommandUIDefinition>

Well, it is a well known thing. The only issue that you may come across is how to identify Location mentioned above. We want the button to be here.

There is no much information on it on the Internet, so the main source of information is pwaribbon file. This file is quite big, but clear. After we found the needed Location everything goes easy:
We add button to it.

<Button Id="EBT.MSP.TimesheetSync.Import.ANCHOR.RedmineButton"
                          LabelText="Import from Redmine"
                          Image16by16="/_layouts/15/EBT.MSP.TimesheetSync/img/rm_small.png"                          
                          Command="EBT.MSP.TimesheetSync.Import.ANCHOR.RedmineButton.Command"
                          TemplateAlias="o1" />

And we determine the corresponding command.

       <CommandUIHandler
            Command="EBT.MSP.TimesheetSync.Import.ANCHOR.JiraButton.Command"
            CommandAction="javascript:function ProjectCenterExtension() {
              var _grid;          // Display surface (a view) of the JS Grid.
              var _satellite;     // Control wrapper for the JS Grid.
              var props = window.timesheetComponent.get_TimesheetSatellite().get_impl()._pageProperties;
              window.location = '_layouts/15/EBT.MSP.TimesheetSync/ImportTimesheetData.aspx?ts=' + props.tsUid + '&amp;prd=' + props.prdUid + '&amp;datasource=jira_ebt';              
            }; 
            ProjectCenterExtension();"
        />

As you can see from the example code, the command just redirects user to the corresponding page of ApplicationPage type. It is written by us. Actually, it is inside this one page where all “dirty” things happen.

In particular:

  1. Authorization in tracking system and data reading.
  2. Displaying data on the page to let user see added timesheet
  3. Recording information in MS Project.
2.2. Data reading

Here two issues occurred:

  1. How to authorize in trackers to get data.
  2. How to do the mapping between MS Project user and tracking system user.
     

There are two general approaches for this. Spoiler alert: we had to use both of them for different trackers.

  • Register a super user to get all data from a tracker. And then try to match user names in MS Project Server and trackers.
  • Make sign on from MS Project Server to trackers. Here we can get a bonus — no matching needed, you can just get user records from the common user account. However, there are also some issues connected to it, which we tell about later..

Solution for Redmine and TFS

In our case these trackers «live» in our infrastructure; that is why we decided to use the first approach.

It is easy when speaking about Redmine — Redmine has Log Work feature which works as we need. So an employee can open the task and log how much hours he spent on it on certain date.
Then it goes simple — you make a view in Redmine DB (our Redmine uses MySQL), and everything is ready! The task is solved, and we can get the data.

It works different for TFS. It doesn’t have the features like in Redmine. But there are particular Add-Ins which may help to get them. For example, tfs-timetracker — interesting thing, even though expensive.

But it is possible to use orgaizational approach. How? In TFS there is a field Completed Work where developers have to write down total number of spent hours for a task. In templates of Scrum projects such field is hidden, but it can be displayed. Furthermore, relying upon the changes of Work Item it is possible to monitor changes of this field and to understand when hours were added, when, by whom, and how much time was spent. Of course, it takes great effort to work with a tracker from a developer. He cannot report time backdating; nevertheless, if to do everything in time and accurately, such approach works well. Moreover, it helps to maintain discipline among developers.

JIRA

In our case JIRA «is hosted» in a third-party environment. Therefore, we have neither super user, nor access to its DB. But JIRA has remarkable REST API which provides necessary things.

To get the data, it is necessary to authorize under the OAuth protocol. After import from JIRA starts, user is redirected to the JIRA authorization page, authorizes there (if user has not authorized yet), then user goes back to the import page.

(Unfortunately JIRA supports only OAUTH 1 which is a little outdated at the moment, but still working). Having received OAUTH token we:

  1. Get author’s name by the method. api/v2/myself Thus, no name matching is needed; we just take user who logged in JIRA.
  2. Then we look for all the tasks which user logged in a particular period.api/v2/myself/search?jql=worklogDate>={start_date} and worklogDate<{end_date} and timespent>0 and worklogAuthor={author}&fields=summary,project,parent,timeoriginalestimate,{pswa}&startAt=0

At first sight, that’s it, but we need more. MS Project supports working in delegate mode when user does something on behalf of someone, and our managers actively use this mode. Here, we come across certain collisions, because if in MS Project person of A represents person of B (usually a manager represents a subordinate, an executive — an employee), in JIRA it is different — user represents himself. And by similar inquiry user receives the tasks assigned to him (Person A) which will be registered in timesheet to Person B. Moreover, there is no other method (or we have not found it yet) to receive all tasks of all projects where specified user logged all working hours. And person A may be limited in permissions in JIRA on having access to needed tasks. However, the last issue can be solved organizationally.

To go through all tasks, it is required to get all projects by the method:

api/v2/project

Then we have to go through all projects and get all tasks.

api/v2/search?jql=project={projectKey}&fields=summary,project,parent,timeoriginalestimate,{pswa}&startAt=0

 

After that we get logged hours for each task and select the hours for particular period and user.

api/v2/issue/{id}/worklog


In this case the problem of matching names happens again. Besides, such process takes a lot of time.We couldn’t figure out a better approach; however, if there were a better solution, we would be glad to hear about it.

2.3. Data displaying

There is nothing to say — good old ASP.Net code which renders received data.

2.4. Recording to Timesheet

So, we passed to special page, authorized, received necessary records, it is left only to record them in MS Project. Actually, MS Project has several API types, but as we work inside it, we used the same one that is used by MS Project pages. This API looks quite unusual.

For example, to get resource, it is necessary to call the method.

var dataset = resourceSvc.ReadResource(userId);

And it is quite clear, but then we have to spend much time trying to understand where there is a needed table, string, and column in this DataSet. It is not enough documentation on this question and, frankly speaking, we did not find any example of adding Timesheet Lines to Timeshees working with this API on the Internet. But, after seeking in «giblets» of MS Project Server by means of ILSPY, we eventually wrote the following code:

private void ReportTime(Guid timeSheetId, List<TaskData> tasks, Guid projectId)
        {
            var timesheetDs = _timeSheetSvc.ReadTimesheet(timeSheetId);

            TimesheetDataSet.LinesRow[] rows = (TimesheetDataSet.LinesRow[])timesheetDs.Lines.Select();

            bool needUpdate = false;

            foreach (var task in tasks)
            {
                if (!task.Estimation.HasValue && !task.IgnoreEstimation) continue;

                Guid taskId = new Guid(task.Id);
                Guid assnId = new Guid(task.AssnId);
                bool isRowExist = false;
                foreach (var row in rows)
                {
                    if (row.ASSN_UID != assnId) continue;
                    isRowExist = true;
                    AddActual(timesheetDs, task, row);                    
                }

                if (!isRowExist)
                {
                    TimesheetDataSet.LinesRow line = timesheetDs.Lines.NewLinesRow();
                    line.TS_UID = timeSheetId;
                    line.TASK_UID = taskId;
                    line.PROJ_UID = projectId;
                    line.ASSN_UID = assnId;
                    line.TS_LINE_CACHED_ASSIGN_NAME = task.Name;
                    line.TS_LINE_UID = Guid.NewGuid();
                    line.TS_LINE_CLASS_UID = PSLibrary.TimesheetConst.const_StandardLineClassGuid;
                    line.TS_LINE_STATUS = (byte)LineStatus.Pending;
                    line.TS_LINE_VALIDATION_TYPE = (byte)ValidationType.Verified;                    
                    timesheetDs.Lines.AddLinesRow(line);

                    _timeSheetSvc.PrepareTimesheetLine(timeSheetId, ref timesheetDs, new Guid[] { line.TS_LINE_UID });
                    AddActual(timesheetDs, task, line);                    
                }
                needUpdate = true;
            }

            if (needUpdate)
            {
                Guid jobUid = Guid.NewGuid();
                _timeSheetSvc.QueueUpdateTimesheet(jobUid, timeSheetId, timesheetDs.GetChanges() as TimesheetDataSet);

                TaskUtils.WaitForQueue(_queueSvc, jobUid);
            }           
        }

This code, actually, also records necessary data in MS SharePoint. To be honest, most problems from the whole story occurred with MS Project, and we could write a separate post devoted to this. 

Step 3. We polished technology on focus group

We introduced "the magic button" separately for each tracker, every time covering ~ 30% of employees and testing it on small group of 3-5 beta users quietly before final start. Then we burnished it and pushed to production. So this helped us to avoid Friday evening with pile of questions and indignation from 30 people at once.

Step 4. We got the feedback

What Eastbanc Technologies employees think of this «magic button» for timesheet filling:

Yuri Bulkin, Java-Architect

Awesome!!! When I first clicked the button and right there saw completely filled timesheet — I just didn’t believe my eyes. Many times they said that it would work like this. And I understood how it was to look. But when I received the timesheet which is automatically filled with twenty tasks, it was so cool that I realized all convenience of this thing.

 

 

Alexandra Ocheretinskaya, Head of PMO

We realized that the weekly activity of filling in timesheets is an unhealthy and nonintellectual activity. We urged our developers to enter manually information which already exists, just in another place and form. And we can save people from this unnecessary work. If people anyway already work on tasks which are recorded in the trackers, then why not to import this information for the accounting? That is what we did.

 

 

Maxim Podusov, Software Developer

It is much faster now, 30 minutes faster... Before it was 32 minutes, and now jut 2 minutes.

 

 

 

Vasiliy Lebedev, Senior Software Developer

Cool! Less duplicate work, more detailed tasks. Who knows, maybe I will start filling timesheets in time. :)

 

 

 

Irina Manannikova, Project Manager

As for me personally, I got rid of routine manual process of weekly timesheet verification. High speed of timesheet filling is a nice bonus. And the main goal was to receive actual, transparent and clear picture on projects in trackers. The objective is achieved, at the end of the week we have almost 100% up-to-date information by each project for the previous week.

 

 
Step 5. Profit!

We enjoy life and use the free time effectively.

 

Have questions left? We are ready to answer all of them!

Ask a question

Ask a question

Awesome!

Thank you. We'll answer soon