Get list of Epics where there are No Open child issues

[Melissa Donatell]

I'm trying to put together a filter that captures the following data and I'm having a little trouble figuring it out.

Looking for  a list of Epics where all the Child Issues are Complete. (No Open linked issues)

• Epic Project = DF
• Epic Status: Not in Complete
• Child Issues 
  • Status in Done, Complete, Completed
  • Child issues can be in Project DF or DRVIZ
• I only want the list of Epics, not the children

I'm using the Query to find out if I can mark the Epic as Complete or if I need to create more Child Issues.

Thanks for any help you can provide.

[Dave Rosenlund _Trundl_ · Accepted solution]

Welcome to the community, @Melissa Donatell   👋

Someone smarter than me will hopefully have a better answer/workaround, but AFAIK, what you want to do is not possible with Jira out of the box. Sorry.

That's because the LinkedIssues function only works on one issue at a time (you have to specifiy the issueKey one Epic at time).

So, I don't think it's possible with a JQL filter.

That said, maybe the CascadesOption will work for you (I've never used it myself), but it requires you to have a custom field, so I'm skeptical that it will.

You may want to keep an eye on Jira's new Lists feature. It's only been released on a limited basis for now (you may not have it yet). Atlassian made some big announcements about enhancements in this area last week at Team'24. It's going to get better and better.

You may want to check out the Atlassian Marketplace in the short term. There may be an app there that can help you do this.

Off the top of my head, Structure (from Tempo) and/or JXL (from Fine Software) may be able to help you. There may be others, too.

Hope this helps,

-dave

[Casey Gould]

Hi Melissa,

While it's true (to my knowledge at least) that you can't do this directly from the search bar, this is one of the things you can accomplish through Jira automations WITHOUT ADD-ONS. Caveat: it depends on your access to Jira automations.

For example, I have an automation set up that sends a "Hey, should this be closed?" email out to the Epic's owner if all the children are done and* the issue is past the due date. (I have this added due date check so that it doesn't ping them on barely-started epics that actually need more child stories written.)

I'm putting in a number of details in case you or someone else who comes across this later hasn't had much reason to get into Jira automations before, or at least not to hack your way around JQL search limitations (ex: text field lengths, date field comparisons, etc.)

There are a few components to consider:

1. Trigger: When or how often you want to check this
2. Conditions: Identifying your epics
3. Action: What do with them

---

My automation:

When any story (in the projects this automation is enabled for - under Rule details) is moved to Done, check the parent Epic to see if all its children are now Done.

1. Trigger: When: Issue Transitioned to Done
2. If: Parent exists
3. Branch: For: Parent
4. If: my personal criteria, but includes:

   type = Epic AND statusCategory != Done

5. And if: (Related issues condition)
   Children | All match specified JQL | JQL:

   statusCategory = Done

6. Then: Send email

---

1. Trigger

(green automation component)

There are two trigger approaches I would suggest, which depend on your needs and define the tops-down or bottoms-up (like mine above) format of the conditions.

Scope: If you go tops-down, you'll want to set the scope of your automation to DF. If you're going bottoms-up, it'll be DF and DRVIZ.

Running this check every time a story is closed in DF or DRVIZ is one of the more immediate approaches (bottoms-up), but you could also schedule a tops-down check daily/weekly.

The perk of using "Scheduled" triggers is that it gives you the option to manually fire the automation when you want. If you go this route, this is where you'll define which epics you want to check. Probably something like:

type = Epic AND project = DF AND statusCategory != Done

2. Conditions

If you're using the bottoms-up format triggered when a child issue type is closed, you'll need the Branch (purple automation component) to navigate to the Parent before checking the conditions (yellow automation components) on that Parent. Then once you're on the parent, this (Step 4 in my automation) is where you would check that it's an open DF Epic:

type = Epic AND project = DF AND statusCategory != Done

If you're doing a scheduled check on the Epics (already defined in your trigger), then you can skip that "branch and check" step and jump right to:

For both types, the key element is going to be adding a component > IF type (yellow) > Related issues condition:

• Children | All match specified JQL | JQL:

  statusCategory = Done

At this point, you've found the epics that match your criteria, which only leaves what to actually do with them.

3. Action

Above, I have my automation send an email with a link to the Epic. You could also comment on the status and @ mention yourself or the assignee.

If you want one consolidated list to view, I would add a label to the epic. Then you can create a filter for open epics with that label and either view the filter directly, add it to a dashboard, or set a subscription to run shortly after your scheduled check.

Labels - Pros: One consolidated email, and you can set the subscription to only email you when there are results. Cons: labels can be removed so you could theoretically miss some and - conversely - you may want to clean up with another automation to take the label off when an Epic is closed.

Final note: I generally use statusCategory instead of status on my checks so that it encompasses all the Green "Done" statuses, including ones like "Won't Do" or your three states mentioned.

Your use case here is a very common one, so I hope this helps!