JQL to show epics that are active, including their related issues (tasks, stories, etc)

[Adam Callaghan]

Hi there,

I'm fairly new to JIRA and JQL though I have a bit of a background in SQL and some coding, but I haven't been able to figure out how to achieve the below in the standard JQL filtering, but I'm hoping someone smarter than me can point me in the right direction.

The short version is that I'm trying to create a filter that will let me view our Roadmap view in the following way (because it's very busy with future planned work):

• All Epics that are in flight or due to start in the next two months
• Include all issues related to those Epics so that we can see them onscreen and continue to add more

The first part was fairly straight forward (with help from our product owner) and resulted in the following query:

project = INFRA and issueType = Epic AND status != done AND (duedate <= now() OR duedate <= 60d)

This reduces the view down to the Epics we're interested in, however is also eliminates the related issues.

What I've been trying to do is use the above Filter (it's now a global filter) to drive another query to give me all the issues who's parent is in the Filter, but alas, no dice!

If this is not possible without additional addons then that's fine, but if it is, I'd love to crack it!

Thanks in advance

Adam

[Mikael Sandberg · Accepted solution]

Hi @Adam Callaghan,

Welcome to Atlassian Community!

You need an app in order to get the result you are looking for. There are multiple apps in the Marketplace that extends JQL like JQL Tricks and Enhanced Search, both of which I have used before.

[Adam Callaghan]

Hi Mikael,

Thanks for such rapid feedback on this :)

I'll reach out to our product owner and find out if we have access to those apps, though I'm fairly sure we don't.

If we do, I'll reach out again for some assistance if I'm unable to pull the queries together.

Thanks again

Adam

[Adam Callaghan]

Hi Mikael,

Thanks again for your previous response.

As we've continued to utilise JIRA, we have continued to bump into the filtering limitations and we've just gotten agreement to add one of these apps into our subscription.

It looks like the JQL Tricks application gets a better score and more widely deployed than the Enhanced Search, however I'd be interested in your feedback if you wouldn't mind?

As you've indicated, I think both look to be capable of supporting what we need, but it one more functional than the other?

Cheers

Adam

[Tim Perrault]

Hi @Adam Callaghan 

I wonder if something like the below JQL would work for your case:

project = INFRA AND issuetype = Epic AND status != done AND (duedate <= now() OR duedate <= 60d) OR "Epic Link" is not EMPTY ORDER BY "Epic Link" ASC

[Adam Callaghan]

Hi Tim,

I've given that a try and it's pulled what looks like everything in from every project.

But I've bracketed the query into the below and that's filtering the view appropriately, but I'll dig through the results and confirm it's correct though the initial look is good! :D

project = INFRA AND (issuetype = Epic AND status != done AND (duedate <= now() OR duedate <= 60d) OR "Epic Link" is not EMPTY) ORDER BY "Epic Link" ASC

[Adam Callaghan]

So I've been working through the results and that query is not quite doing the trick.

The results show the Epics with the appropriate date ranges, but it's also pulling back any Epic with a sub-task linked regardless of the date range, which will be the (OR "Epic Link" is not EMPTY) statement I presume.

Is does exclude Epics outside of the date range where there are no issues linked though, so it's closer.

[Tim Perrault]

project = INFRA AND (issuetype = Epic AND status != done AND (duedate <= now() OR duedate <= 60d) AND "Epic Link" is not EMPTY) ORDER BY "Epic Link" ASC

I wonder if we change that OR to AND, but like @Mikael Sandberg said it might be easier to use a plugin. I like scriptrunner because it does add an enhanced search, but also a ton of other features.

https://scriptrunner-docs.connect.adaptavist.com/jiracloud/enhanced-search.html