Server - Worklog reports for Epics

This article is for Tempo for Cloud, Server and Data Center

QuestionHow can I pull a worklog report for Epics?
Answer

A report on spent time for an epic can be tricky and at the moment there is no approach to achieve that easily. The infrastructure of epics can be complicated within Jira as issues can be linked in many nested ways (epic links, issue links and subtasks). 
In this blogpost I will point out some options to create worklog reports on epic level both with the help of Tempo, Eazy BI and EpicTime. Each option listed below have a different approach with some pros and cons. You might need to find the right fit for your business needs.

  1. The first approach can be achieved by using the "Tempo worklog distribution" dashboard gadget that is shipped with Tempo Timesheets. When you have selected the gadget make sure to select a Jira filter that includes your epics and select "Epics" as the grouping option.

    This will draw a graph showing all time spent for an epic. This report includes all worklogs on issues that are linked to an epic AND its subtasks. The report can be filtered for only one time period at once and cannot span a customised date range.
  2. The second option is to use the Tempo report engine itself. By creating a customized report from the startup report page you will be able to filter the report according to your needs. From the filter menu select the epic that you want to report on.

    Filtering on epics allows to select a customized date range. However the report only pulls worklogs from those issues that are directly linked to the epic otherwise by an epic link or an issue link.
  3. Another way to use the Tempo report engine is to filter on a Jira issue filter. That will force us to create a Jira filter for each single epic or each group of epics that you want to report from. Creating a Jira filter on an epic and its related issues is not possible from native Jira. So you will need to have some third party apps installed to create a Jira filter that will pull all linked issues and its subtask into consideration. The first app doing this job is AM utils (free) and the second one is Scriptrunner (paid). Both apps deliver additional functionality the Jira issue search. The syntax for the apps is slight different:
    AM Utils: "Epic Link" = WDP-1 OR issuekey in  (issuesSubtasks("\"Epic Link\" = WDP-1"))
    Scriptrunner: ("Epic Link"=WDP-1) OR issueFunction in subtasksOf("\"Epic Link\"=WDP-1")
    In the Custom report setting now use the created Jira filter.

    You might notice that the report pulls in also the worklogs of subtasks for the issues that are linked to the epic we want to report on.
    But be aware that the additional JQL functions delivered by AM utils or Scriptrunner do only reflect issues that are directly linked to the epic (by using epic link or issue link).
  4. In order to make sure you do not miss anything and have a fully customised report you can always use the data analysing tool from Easy BI.
    For the last use case we use a SQL query in order to get all linked Jira issues to an epic and its subtasks. This is the only method that we can use to get e.g. issues linked to epic through a third level link. Imagine the scenario: We have an epic and some stories linked to the epic. The Stories itself are linked to other Jira issues using the "issue link" functionality and those issues again have subtasks! You see the infrastructure can get quiet complicated and you need to be careful by building up those constructs as you might miss some "root" issues. 
    For the last scenario we can use some customised SQL queries to pull the information into the report that we need to. Below you will see a simple query that pulls all linked issues from an epic and all its related worklogs. You might need to repeat these SQL queries by combining them with a UNION statement depending on the nested level of links that you use. All links within Jira (no matter if it is an epic link, an issue link or a subtask) are kept in the database table "issuelink".
Epic query
Select a.epic_key,a.epic_id,a.issue_key,a.issue_id
,w.AUTHOR,w.STARTDATE,round(w.timeworked/3600,2) as timeworked
from
(
SELECT concat(p.pkey,'-',i.issuenum) as epic_key,concat(pl.pkey,'-',il.issuenum) as issue_key,il.ID as issue_id,i.ID as epic_id,l1.DESTINATION FROM jiraissue as i 
inner join project as p on p.iD = i.PROJECT
left join issuelink as l1 on l1.SOURCE = i.ID
inner join jiraissue as il on il.ID = l1.DESTINATION
inner join project as pl on pl.iD = il.PROJECT
where i.issuetype = 5
) as a
left join worklog as w on w.issueid = a.issue_id

Once the data import and your report setup have been successful you could implement the report into Easy BI dashboard gadget.

5. EpicTime is an add-on that will install multiple Jira custom fields that can be very useful for reporting on time spent and progress per epic as it accumulates all logged time for linked issues AND its subtasks. These Jira custom fields can easily be added to your issue overview screen.

This view can exported to an csv file for further processing in an calculation application.

Be aware that the total spent time displayed in that view are totals and do not take into account the worklog date (means that the JQL parameter "worklogdate" has no affect).

As the epic fields are injected as Jira custom fields you could enable those fields to be displayed in the excel export data dump and from the worklog servlet