How to make a filter considering multiple conditions dependent on each other?

Question

Hello! Please help me with this situation.

I have epics which contain a number of tasks with different issue types in various statuses. I am trying to create a filter that would show all the epics that are in To Do, In Progress or In Testing and all their children that are not in a Backlog status. What I need is not to have any Done epics returned, but I do need to have the Done children issues if the Epic in not in Done.

As a long story short, I am trying to get the Gantt app to show only the 'active' epics as projects and their children as tasks within each project.

The problem I am having is that I can't seem to write a filter that would bring only the children of those epics. When I add the the second condition I am getting Done tasks from Done epics, but I know the first part of the filter is working because I don't have any done epics.

The closest I got to an accurate response was with this:

project = "dev-project" AND (issuetype = Epic AND status in ("To Do", "In Progress", "In Testing")) OR project = "project-dev" AND ((issuetype in (Story, Task, Bug) AND status not in (Backlog) AND "Epic Link" is not EMPTY) AND parentIssueType = Epic AND parentStatus not in Done)

As a mention, when I remove the OR project = "project-dev" from the second part, I am getting No issues were found to match your search.

Thanks!

Costina Anghel - Iliescu · Answer

Thank you for your help! JQL Search Extensions was what worked for me, but combined with a classic JQL filter.

issue in childrenOfIssuesInQuery("project='dev-project' and type = epic and status not in (Backlog, Done)") AND status != Backlog OR project = "dev-project" AND issuetype = Epic and status not in (Done, Backlog)

Thanks again,

Costina

mauricio.groth · Answer

Hi @Costina Anghel - Iliescu

As you're on Jira Cloud, the correct answer is to get an app that provides JQL extensions you're looking for.

With standard JQL, you can only get a list of issues and export them to Excel for further processing. This works if you want to do a one-off analysis. If your use case is more dynamic than that, look beyond standard Jira.

Standard JQL doesn't easily allow it, but you can quickly find the results using our professional indexing service JQL Search Extensions

You can use this query to find all epics and children, also will return the done children if the Epic is not in Done.

issue in epicsOfChildrenInQuery("issuetype in (Story, Task, Bug) and status != backlog") and project = "dev-project" or issue in childrenOfEpicsInQuery("status in ('to do', 'in progress', 'in testing')") and project = "dev-project"

Check out the documentation for more examples.
I hope this helps!
Maurício

Mikael Sandberg · Answer

Hi @Costina Anghel - Iliescu,

Welcome to Atlassian Community!

JQL out-of-the-box does not allow you to create nested filters that includes the epics and the associated issues, you would need an app that extend JQL in order to do that. For example JQL Tricks  or  Enhanced Search can both do this (I have used both in the past), but if you search the Marketplace you will find other apps that can do this too.