How to search for all resolved Epics that still have unresolved stories?

[Joyce Ynoue]

Hi, I need some help to create a JQL to search:

1) All resolved Epics that still have unresolved stories 

2) All unresolved stories that is part of a Resolved Epic

Thanks for your help.

[Trudy Claspill]

Hello @Joyce Ynoue 

Welcome to the community.

You will not be able to do that either of those in a single JQL with native Jira functionality. You could do it if you leverage a third party app that extends JQL functionality.

I think it might also be possible to get the end results through an Automation rule that uses multiple lookupIssues actions and branching to get one part of the list, then iterate through that list and use the value to execute the next filter.

[Marlene Kegel - codefortynine]

Welcome to the community, @Joyce Ynoue.

I am Marlene from codefortynine.

Another option would be our cloud app Dynamic Custom Fields for Jira.

With our app you could create a field using Jira expressions that 1) displays the number of unresolved stories in an Epic. Once you've set up your field you could search for Epics with unresolved stories using JQL.

issuetype = Epic AND resolution != unresolved AND "Number of unresolved stories" != 0

We also offer a template 2) that displays the parent resolution. Once you've added this field to your stories, you can search for all unresolved stories that is part of a resolved Epic using JQL.

Creating a field using a template is very easy, which means you don't need any technical experience.

For your first use case you would have to write your own custom expression. If you need help with this, don't hesitate to reach out to us. We're always happy to help.

[mauricio.groth]

Hi @Joyce Ynoue 

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

1) All resolved Epics that still have unresolved stories 

issue in epicsOfChildrenInQuery("issuetype = Story and status != done") and status = done

2) All unresolved stories that is part of a Resolved Epic

issue in childrenOfEpicsInQuery("status = done") and status != done and issuetype = Story

Check out the documentation for more examples.

If you have any other questions, please contact our support. We’ll be happy to help you!
Best regards,
Maurício

[Hannes Obweger - JXL for Jira]

Hi @Joyce Ynoue

welcome to the community!

Yet another option may be to look into one of the more hierarchy-focused apps on the Marketplace. These typically don't rely on "plain" JQL to resolve parent/child relationships, but have their own ways of doing so.

I can only speak for the app that my team is working on - JXL for Jira - but here, your use case would be quite easy to solve:

• Create a new so-called sheet in JXL that pulls in any resolved epics, and any unresolved stories that might be part of these epics. Don't worry about the exact parent/child relationships, JXL will sort that out for you. E.g., a simple JQL like project = "X" AND ((issuetype = "Epic" AND resolution != "Unresolved") OR (issuetype = "Story" AND resolution = "Unresolved")) may do.
• In JXL, enable issue hierarchy. That's a one-click action.
• In JXL, hide top level issues without children. That's again a one-click action.

The resulting list of issues should be what you're looking for. This is how it looks in action:

You can work on these issues directly in JXL, trigger various Jira operations, or export the data e.g. to Excel or CSV.

Any questions, just let me know!

Hope this helps,

Best,

Hannes