Calculate Hours Spent for Project Version

This post will show how to calculate hours spent for a project version using PDI JIRA plugin. Of course there are plugins which give such reports in Jira, but PDI Jira plugin for Kettle can give more flexibility and provide this information into your own data store for analysis. You can download the latest PDI JIRA version here.

Transformations

First create IssueHours.ktr transformation whith following steps in it. This transformation is intended to extract hours spent on a single issue.

Step worklog should be configured to extract the issue information for an issue passed as a parameter to the transformation.

issue.key will be provided from the job running this transformation, so it must be configured as a transformation parameter. See how to set transformation parameters here. Configure the output fields to be extracted from JIRA service responses. This should be easy to do as there are preconfigured JSON Path expressions provided by the plugin and these expressions are context dependent, i.e. you'll get fields available in the response for the issue request. Of course you are not limited with the patterns and you can specify any other fields.

Following steps should sort records by author field and perform sum on timeSpent for each author. The summary will be time spent by authors for the issue. This See how to use sort step here and group step here .

There is text file set to collect output results. The reason temporary file is used for the output is it can accumulate the records in several transformation executions. This is not possible if you try to collect the resulting records in a transformation output because you'll lose the information on next issue calculation.

Transformation which searches  all issues for a project version need to be set up. It will provide information about the issues found to the hours calculating transformation.

 

Key for the issues found will be stored in the transformation output (step send - see how to use here), so the job can use them to execute next calculation step. Version tasks step is a PDI JIRA plugin step which extracts the issues from specified version.

Again providing project key and version label is left to be done from outside using parameters. When executed transformation should provide keys for the tasks found in the version to the next transformation which appears to be issue hours calculation step.

Finally a transformation which to summarize all results should be set up. This transformation will read all data collected for issues from the temporary text file. Actually this file will contain time spent for each task in the project version. Then summarize the hours for all tasks and calculate the time in hours, as the JIRA gives them in seconds. You're free to output the result to any store - table, file, etc. In this case result is simply traced in the job output.

The Job

The job will execute calculation steps in sequence. First the helper file for collecting time spent should be cleared and make sure no records left from previous executions. This file is used by issues transformation and because it accumulates the results it need to be clear.

Next step should perform the search all issues for the project version and execute issue calculation step for each issue found, so this step should be set to execute for each input row (see how). Summary step will take the contents of the temporary file where times spent for all issues found will be collected.