Make It Workflow — Part 7: Generating Time Reports

Posted on by Mariya Davydova

In our previous post, we took a look at the Time Tracking feature and showed you how to use workflows to make sure that your colleagues add work items properly (that is, avoid cheating). In this post, we describe how you can use workflows to report and analyze time tracking data.

blog_8@2x

Time Report

As usual, YouTrack offers an out-of-the-box solution — the Time Report. This report shows the total amount of time spent working on issues in one or more projects. It displays the type of work done and the original estimation. Time spent can be grouped by the type of work performed or by the users who performed the tasks.

The time report helps answer questions like:

  • How much work was recorded last week and who performed this work?
  • How was the time spent allocated between different projects?
  • Are the issues that are assigned to an employee usually overestimated or underestimated?

However, the main problem here is that you need to analyze this data manually. Every time you want to monitor this activity, you need to access the report, recalculate it, then analyze the data. If you need to do any historical analysis, you have to export the data and crunch the numbers in an external tool.

Workflows as a Reporting Tool

As always, workflows are here to help! While workflows don’t have direct access to calculated report data, they have access to the raw data in the form of issues and work items. With on-schedule rules, you can report whatever you want on a regular basis and send it to someone, whether a team lead or an employee, by email.

We start with a core module that we can use to analyze issues. This script finds all of the work items for a given assignee in a given project that were recorded within a specific time frame. As we reference this module in all of our workflow rules, we save this as a separate custom script called work-items:

var search = require('@jetbrains/youtrack-scripting-api/search');
var dates = require('@jetbrains/youtrack-scripting-api/date-time');

function formatter(timestamp) {
  return dates.format(timestamp, 'yyyy-MM-dd');
}

/**
 * @param {User} [author] work items author
 * @param {Project} [project] project to get issue from
 * @param {Number} [from] starting date in ms from the epoch start
 * @param {Number} [to] ending date in ms from the epoch start
 * @return {[WorkItem]} list of work items matching the parameters
 */
var fetchWorkItems = function(author, project, from, to) {
  // Generate a search string to find issues,
  // where at least one work item was added by {{EJS1}} between {{EJS2}} and {{EJS3}}:
  var searchQuery = 'work author: ' + author.login + ' ';
  searchQuery += 'work date: ' + formatter(from) + ' .. ' + formatter(to);
  
  // Now we can traverse over these issues in a {{EJS4}}
  // and choose the work items we need:
  var items = [];
  var issues = search.search(project, searchQuery);
  issues.forEach(function(issue) {
    issue.workItems.forEach(function(item) {
      if (item.author.login === author.login &&
          item.date >= from && item.date <= to) {
        items.push(item);
      }
    })
  });
  
  // Return the array:
  return items;
};

exports.fetchWorkItems = fetchWorkItems;

Now, let’s help our team lead answer the following question: how much work did each developer log last week? We can extract the set of values from the Assignee field as the list of developers, get work items for each one of them, and calculate the difference between the time logged and required work duration (say, 40 hours for each developer).

As you may have seen in other workflows that run on a schedule, this rule uses an “anchor issue”. The anchor issue lets you pull the project that the issue belongs to into the context and iterate over other issues in the project. It also makes sure the rule runs exactly once per scheduled execution.

For an anchor issue, just create an issue with a description like “Please don’t ever delete this issue!” and set it to a resolved state. You can then reference its ID in the search property of your on-schedule rule. This makes the rule to run exactly once per each time scheduled. We’ll use this technique in the following rules.

var entities = require('@jetbrains/youtrack-scripting-api/entities');

var wi = require('./work-items');

var DAY_IN_MS = 24 * 60 * 60 * 1000;
var HOURS_TO_WORK_A_WEEK = 40;

exports.rule = entities.Issue.onSchedule({
  title: 'Send report to the project lead every Monday',
  cron: '0 0 10 ? * MON',
  search: '#WI-1', // // TODO: replace with the ID of an anchor issue
  action: function(ctx) {
    var project = ctx.issue.project;
    
    // Calculate start and end of the last week:
    var from = new Date();
    from.setHours(0, 0, 0, 0); // the start of this day
    from = from.getTime() - 7 * DAY_IN_MS; // the start of last Monday
    var to = from + 7 * DAY_IN_MS - 1; // the end of last Sunday
    
    // Get a list of assignees from the Assignee field in the project,
    // get a list of work items for each of them, and calculate sum of durations
    // for the work items reported by each assignee:
    var durations = {};
    var assignees = ctx.Assignee.values;
    assignees.forEach(function(assignee) {
      var items = wi.fetchWorkItems(assignee, project, from, to);
      var duration = 0; // duration in minutes
      items.forEach(function(item) {
        duration += item.duration;
      });
      durations[assignee.login] = duration / 60;
    });
    
    // Create email content:
    var subject = '[YouTrack, Report] Report of work done last week';
    var body = 'Here is the report for last week: \n\n';
    assignees.forEach(function(assignee) {
      var duration = durations[assignee.login];
      var text = assignee.fullName + ' worked for ' + duration + ' hour(s)';
      if (duration > HOURS_TO_WORK_A_WEEK) {
        text += ' (overtime for ' + (duration - HOURS_TO_WORK_A_WEEK) + 
          ' hour(s)).\n';
      } else if (duration < HOURS_TO_WORK_A_WEEK) {
        text += ' (downtime for ' + ( HOURS_TO_WORK_A_WEEK - duration) + 
          ' hour(s)).\n';
      } else {
        text += '.\n';
      }
      body += text;
    });
    body += '\nSincerely yours, YouTrack\n';
    
    // Send email to the project lead:
    project.leader.notify(subject, body);
  },
  requirements: {
    Assignee: {
      type: entities.User.fieldType
    }
  }
});

The cool thing about this rule is that it is highly customizable. Here are just a few of the possible directions you can push this functionality:

  • Instead of using the set of values for the Assignee field, generate the list of developers based on membership in one or more groups.
  • Pull data from multiple projects and calculate the amount of time spent for each, grouping time spent by project or by developer.
  • Map the required work duration per developer instead of using a common constant.

As the second example, let’s send a reminder to the developers when the amount of work logged is less than the required work duration at the end of the week:

var entities = require('@jetbrains/youtrack-scripting-api/entities');

var wi = require('./work-items');

var DAY_IN_MS = 24 * 60 * 60 * 1000;
var HOURS_TO_WORK_A_WEEK = 40;

exports.rule = entities.Issue.onSchedule({
  title: 'Remind developers on Friday if they have not logged enough work',
  cron: '0 0 16 ? * FRI',
  search: '#WI-1', // // TODO: replace with ID of an anchor issue
  action: function(ctx) {
    var project = ctx.issue.project;
    
    // Calculate start and end of this week:
    var to = new Date(); // current moment
    var from = new Date(to - 4 * DAY_IN_MS); // Monday 16:00
    from.setHours(0, 0, 0, 0);
    from = from.getTime(); // the start of last Monday
    
    // Get a list of assignees from the Assignee field in the project,
    // get a list of work items for each of them, and calculate sum of durations
    // for the work items reported by each assignee:
    var durations = {};
    var assignees = ctx.Assignee.values;
    assignees.forEach(function(assignee) {
      var items = wi.fetchWorkItems(assignee, project, from, to);
      var duration = 0; // duration in minutes
      items.forEach(function(item) {
        duration += item.duration;
      });
      durations[assignee.login] = duration / 60;
    });
    
    // Send emails in case of work is not yet done:
    assignees.forEach(function(assignee) {
      var duration = durations[assignee.login];
      if (duration < HOURS_TO_WORK_A_WEEK) {
        var subject = '[YouTrack, Reminder] Work done this week';
        var body = 'Hey ' + assignee.fullName + ',\n\n';
        body += 
          'Looks like you have forgot to log some work: you have worked on ' +
          project.name + ' for ' + duration + ' hour(s) instead of ' +
          HOURS_TO_WORK_A_WEEK + ' required for you.\n';
        body += '\nSincerely yours, YouTrack\n';
        assignee.notify(subject, body);
      }
    });
  },
  requirements: {
    Assignee: {
      type: entities.User.fieldType
    }
  }
});

The same ideas for extending the previous script apply here as well, and many more. With the ability to access work items, you can calculate not only billable hours but also other numeric characteristics, like the collective velocity of your team and the relative performance of each developer.

In our next article, we show you how you can use workflows to empower your helpdesk. While we prepare the post for you, mine inspiration from our other workflow-related resources:

Subscribe

Subscribe for updates