I am trying to find all Epics that are complete, but the story is still in Progress

[Nichelle Wagner]

my query is issuettype = Epic AND status = Complete AND issutetype = Story AND status = "In Progress"

However, the query is not returning any data and I know there are records out there because I have come across them accidentally.

[Hannes Obweger - JXL for Jira]

Hi @Nichelle Wagner @Scott Morris

Unfortunately, this is trickier than one might think; as a hierarchical query, it would really require some kind of join or subquery, which isn't available in plain Jira/JQL.

A few directions forward:

• If it's a one-off thing, you could first query the relevant parents (epics), and then use the keys of these epics in a second query, in an ""Epic link" in (KEY-1, KEY-2, ...)" clause.

If you want to run your search dynamically, without manually "stitching" two queries together, you'll need extra tooling:

• You might be able to use Jira Automation to "propagate" parent information down to the parent's children, and then use the respective field(s) on the children to include them into your filter. Obviously, this will add a fair bit of complexity to your system.
• There's different apps from the Atlassian Marketplace that can help with that. First, there's a number of apps that extend JQL by additional functions, including hierarchy-related functions. I've used JQL Search Extensions a few times and it works well.
• Alternatively, you could try one of the more hierarchy-focused apps from the Marketplace. These apps typically have their own ways of figuring out parent/child relationships between issues, and provide more powerful ways of searching through issue hierarchies. I myself work on such an app, in which your use case would be easy to solve - I'll provide more details below.

Hope this helps,

Best,

Hannes

[Hannes Obweger - JXL for Jira]

... and to expand on the last point: If you're open to solutions from the Atlassian Marketplace, your use case should be easy to solve using the app that my team and I are working on: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel, Google Sheets, Smartsheet, or Airtable. It also comes with a long list of so-called smart columns that aren’t natively available, including the parent status. 

So the easiest way forward would probably be to add status and parent status columns to your sheet, and filter down to the relevant stories like e.g. so:

I should add that JXL can do much more than the above, and would actually offer multiple possible solutions to what you're looking for. Its advanced features include e.g. support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

This all just works - there's no scripting or automation whatsoever required.

Any questions just let me know!

[Cassie the Jira Mechanic]

@Nichelle Wagner This is an issue with your JQL

When you use AND the issue must be both conditions. Here you are saying must be an EPIC AND STORY. No issue can be both issue types at the same time. So it should ready OR rather than AND.

 issuettype = Epic AND status = Complete AND   OR  issutetype = Story AND status = "In Progress"

This will return issues that are:
epics in the status complete 
storys in the status "In Progress"

Here is some free documentation:
Use advanced search with Jira Query Language (JQL) | Jira Work Management Cloud | Atlassian Support

Here is a skill building training course I recommend to better understand when to use AND vs OR . Intro to JQL in Jira Software : Atlassian

Hope this helps! Please give it a try and if this fixes it 'Accept' the answer so others know this works!

[Cassie the Jira Mechanic]

Re-reading this though I think you are searching for stories that are open but their parent epic is not.  I actually have a scheduled automation that runs to check this. But it is not something without listing out your epics via JQL that can be done without an addon. I am assuming a lot here but if you want this data so you can update these issues accordingly see this automation to point you in the right direction. You can follow this and tweak it per your needs. You can also adjust the trigger to be when an issue is transitioned.. etc.

[Nichelle Wagner]

The EPIC is marked Done, but in some instances the Story is still open.  I want to find all instances where the EPIC is marked Done and the associated Story is showing as "In Progress".

[Cassie the Jira Mechanic]

@Nichelle Wagner the automation will be similar with a few tweaks to above.. if you want reach out on linkedIn ill be happy to help and after we can update this with the final solution.

[Nichelle Wagner]

Somewhat new to Jira.  I understand the automation, but not sure how to incorporate into the tool.  I will reach out on Linkedin

[Scott Morris]

did you all find a solution to this request? i am also looking for the proper JQL syntax that can find all completed epics that contain an open work item. 

its not an everyday search, but a good maintenance search to ensure all work has been completed without visiting all the epics to verify one-by-one