JQL ORDER BY - how to sort by epic and related stories/tasks?

[Matt Thornton]

I am attempting to create a Filter that provides a list view of multiple issues in a given project and sprint. Within that list, I would like to order the issues by Epic, then associated stories to that epic, and associated tasks to those stories, and then the next Epic (with relevant child issues).

The desired output would look something like this:

• Epic 1
  • Story 1.1
  • Story 1.2
    • Task 1.2.1
• Epic 2
  • Story 2.1
• Epic 3
  • Story 3.1
    • Task 3.1.1
  • Story 3.2.
• etc.

My JQL already has project = XX and sprint = YY, but I have not yet been able to find a way to sort as desired. "Epic Name" field is blank on the child issues to the epic, and "Epic Link" field doesn't exist on the epic itself, so various permutations of ordering by those two fields haven't worked. In searching other posts, I have found ways to do this with a specific epic, or an epic in a range, but I don't want to have to manually define the epic range every time I run the query.

I would greatly appreciate any input!

-Matt

[Nic Brough -Adaptavist- · Accepted solution]

This can't be done natively, because of the way Epics are related to Stories.

Exactly as you say: " "Epic Name" field is blank on the child issues to the epic, and "Epic Link" field doesn't exist on the epic itself"

Epic name goes on the Epic only, but can be displayed on stories that belong to it in some places.  Epic link only goes on the stories, and actually holds a reference to the Epic, but displays the linked Epic's name on screen.

This is 

• Something we have to work around - what we're looking for is to group by two different fields that mean the same thing, and JQL ain't that clever
• A right royal PITN for searching and sorting
• Changing a bit soon (but not in a way that solves it, sadly)

My usual fix for this is to create a pair of scripted fields (there's another similar problem with issues and sub-tasks, I usually find people who have one of these problems turn out to have both)

I create a field for "base issue" for all issues and sub-tasks.  The scripts populate it with the current issue id on base level issues and Epics, but on sub-tasks, it populates it with the sub-task parent.

I create a field for "Epic issue" for all types of issue, and do much the same - for Epics, use the Epic's issue ID, for issues, use issue.epic-parent, and for sub-tasks, use issue.parent.epic-parent

[Matt Thornton]

Thanks Nic, appreciate the advice. My inclination was that if I couldn't do it in native JQL I would have to do a custom scripted field to create some unique identifier that was easily sortable. I like the "base issue" and "epic issue" approach - I will give this a try! 

[Trudy Claspill]

Hello @Matt Thornton 

That type of sorting is not possible with native JQL functionality for the ORDER BY clause.

There may be third party apps for reporting that would give you that sort of output.

Or, if the Epics and Stories are in just one project you can use the Roadmap feature to display the issues in that order. That does not account for including linked Task issue types, unless those Tasks are actually Subtasks.

Are you open to third party apps for this?

[Matt Thornton]

Thanks Trudy, appreciate the input. The Roadmap view is popular and a way I have been able to visualize this for some users - the filter would just be nice because I can use the excel plugin to populate a report and distribute to users who prefer things in an excel.

I'll look around at third party solutions as well.

Thank you!