All eazyBI for Jira eazyBI for Confluence Private eazyBI

Sprint and Epic gantt charts
eazyBI for Jira

On this page:

Issue Epic gantt chart

This example will show how to create Gantt charts with Jira data.

The main attribute of any Gantt chart is Start and End date. You would like to use some measure representing date for your Gant chart; any first two date columns will be drawn in the Gantt. You could use date properties. For example, Issues, Sprints, and Fix versions have date properties you can use on the Gantt chart as the Start date and End date.

This report uses the Issue dimension Epic hierarchy representing open epics and issues in open epics. The measures from issues within the Epic are summed up on the Epic level, e.g., the Hours spent show all hours logged on issues within the Epic, Story Points created shows total planned story Points in issues in epic.

For the Issue Epic Gantt chart, the Issue created date and Epic End date measures are used as the Start date and End date. Issue created date represents a created date of the epic itself. Measure Epic End date uses a custom formula to calculate the latest due date in issues in epic:

Cache(
CASE WHEN
  -- this case will work for Epic Link dimesion and individual epic selection
  [Epic Link].CurrentHierarchyMember.Level.Name = "Epic"
  AND
  [Epic Link].CurrentHierarchyMember.Name <> "(no epic)"
THEN
  CASE WHEN
    not IsEmpty([Epic Link].CurrentHierarchyMember.Get("Resolved at"))
  THEN
  -- for resolved issues grab resolution date as report end date
    [Epic Link].CurrentHierarchyMember.GetDate("Resolved at")
  ELSE
  -- get the last due date from issues in epic:
    TimestampToDate(Max(
      Descendants([Issue.Epic].[Epic].GetMemberByKey([Epic Link].CurrentHierarchyMember.Key),
      DateToTimestamp([Measures].[Issue due date]))))
  END
WHEN
 -- if Issue hierarchy on epic level: 
  [Issue].CurrentHierarchyMember.Level.Name = "Epic" 
THEN
   CASE WHEN
    not IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at"))
  THEN
  -- for resolved issues grab resolution date as report end date
    [Issue].CurrentHierarchyMember.GetDate("Resolved at")
  ELSE
 -- access all children of the epic and get the latest due date
  TimestampToDate(Max(
    Descendants([Issue].CurrentHierarchyMember),
    DateToTimestamp([Issue].CurrentHierarchyMember.GetDate('Due date'))))
  END
ELSE
  -- for issues show due date
  [Issue].CurrentHierarchyMember.GetDate('Due date')
END
)


The report uses the calculated measure Issue epic link status as a filter. This measure represents the status of an epic for the epic itself and any sub-level issues.

CASE 
  WHEN 
    [Measures].[Issue type] = 'Epic'
  THEN
    [Status].[Status].GetMemberNameByKey(
      [Issue].CurrentHierarchyMember.get('Status ID'))
  WHEN 
    [Issue type].[Issue type].getMemberByKey(
      [Issue].CurrentHierarchyMember.get('Issue type ID'))
    .getboolean('Subtask')
  THEN 
    [Status].[Status].GetMemberNameByKey(
      [Issue].[Issue].GetMemberByKey([Issue].CurrentHierarchyMember.get('Parent issue key')).GetLinkedMember("Epic Link").Get('Status ID')
    )
  ELSE 
    [Status].[Status].getMemberNameByKey(
      [Issue].[Issue].GetmemberByKey([Issue].CurrentHierarchyMember.get('Epic Link')).get('Status ID')
    )
END

Progress of a task can be visualized with colors:

  • blue - upcoming tasks and completed tasks without completion ratio;
  • green - completed tasks with completion ratio (%);
  • red - overdue tasks with completion ratio (%);
  • grey - a group of tasks.

To get the progress of issues, there is a custom measure Issue resolved % defined with formula

CASE WHEN
  [Measures].[Issues created] > 0
THEN
  CoalesceEmpty([Measures].[Issues resolved],0)
  /
  [Measures].[Issues created]
END

Define the formatting of the measure to Integer percentage %, the % part of the line will be colored accordingly.

Sprint gantt chart

A similar report can be created to show Sprint's progress. You can design below visible Gantt chart if you are using Jira Agile Sprint and Story Points custom fields.

This chart uses Sprint custom dimension on rows and Sprint Start and End date measures on columns.

The following formula is used to calculate Story points completed % for each sprint.

CASE WHEN 
  [Measures].[Story Points created] > 0 
THEN
	([Measures].[Story Points created] 
	- [Measures].[Story Points due])
  / [Measures].[Story Points created]
END