Filter: all Epics that are incomplete but only have Stories that are complete

[T]

Is there a way for a filter to say something like:

Show all Epics that are incomplete but only have Stories that are complete.

I guess the problem here is how to relate an Epic to a story in a filter.

[JamieA · Accepted solution]

This is tricky but doable. But you need the jql functions from the script runner plugin (couple of other plugins can do similar to be fair).

The query is like this:

status != Done and 
    issueFunction in linkedIssuesOf("status = Done", "has epic") and 
    not issueFunction in linkedIssuesOf("status != Done", "has epic")

To translate this:

status != Done: Find epics that are not done. You might need to change the status names

issueFunction in linkedIssuesOf("status = Done", "has epic"): This is the tricky but... find issues that have a "has epic" link to an issue with status = Done.

not issueFunction in linkedIssuesOf("status != Done", "has epic"): Even harder here is the requirement that all the linked issues must be Done, so we filter out any epics that that have stories that are not done.

Not the sort of thing you could expect a user to do but you might be able to set it up for them.

[Emily Stewart]

Hi @Jamie Echlin, is there a way to make your awesome jql work on the ScriptRunner Enhanced Search on cloud? Thanks!

[Raghunandan_Sira]

Hi @JamieA , your solution is still enduring in Aug 2019 !

Just used it today to extract the results I needed.

[Roland Goguen]

@JamieA This is great, many thanks!!!

[Alex Lithgow]

@JamieA - Can you show me how to do the opposite? 
Look for Done/Resolved Epics where there are issues in Epic that are NOT Done / Resolved??? 
I have been playing with your query but cannot seem to run that opposing scenario.  Thanks!

[Alex Lithgow]

@JamieA  Also, how would I filter the the other issue types (Story, Task, etc.), not just Epics?

Search for issues where their Epic is Done/Resolved but they are Not.

Search for issues where their Epic is NOT Done/Resolved but they ARE Done/Resolved.

[Ben Kettlewell]

Had to modify this to work on Scriptrunner Enhanced search on Cloud but couldn't have done it without you.

status != Done AND issueFunction in epicsOf("Project = KEY") and not issueFunction in epicsOf("status != Done AND status != Closed")

[Carol Orton]

Hi,  I tried the query above (on prem - not cloud yet) - and it works great in Jira (Thank you) but when I put it in confluence in a Jira Issue/Filter it works when I click the search before inserting it to the page but if I use the Total count it doesn't save the query or display it (it will if its a table).  Also its intermittent as I did get it to work finally (not sure what change I made it make it work or if it was just a fluke) but then I changed it for the next project version (fixVersion) and it won't save.   Here is the query

Project = xxxxx and fixVersion = "xxx xxx-xx" and status != Verified and issueFunction in linkedIssuesOf("status in (Verified, rejected, done, active, draft)", "has epic") and not issueFunction in linkedIssuesOf("status not in (Verified, rejected, done, active, draft)", "has epic")

[Kathy Barton]

@Ben Kettlewell thx! that worked like a charm except I had to add the project key to both subqueries and change status to statuscategory since we have way too many statuses that all mean done :) 

statuscategory != Done AND issueFunction in epicsOf("Project = KEY") and not issueFunction in epicsOf("project=KEY AND status != Done AND status != Closed")

[DTS Infra Admin]

Can this be done without using script runner functions?

[Richard Cross]

Unfortunately, I didn't find any of these queries worked 100% reliably, sometimes listing Epics with incomplete issues.

[Kevin Paulovkin]

Here is another example of what worked for me using Scriptrunner Enhanced Search:

project = KEY AND issuetype = Epic and resolution = unresolved AND not issueFunction in epicsOf("project=KEY AND resolution = unresolved")

[Patrick OBrien]

Thanks Jamie!

[Stephanie-(ATO)]

So this is great, and works for those with Script Runner or a like tool, but is there a query I could use without an add-in or paid plugin? We can't (yet) use Script runner or the link, and we need our RTE's to be able to find Features (Epics) and Capabilities that need to be decomposed (don't have stories or Features on them) without the use of Script Runner. Is this possible?

[Vincenzo Ciaravella]

Is there any way to get the results of the original question (Show all Epics that are incomplete but only have Stories that are complete.) without the use of Script Runner.  We use JQL Tricks and I can't seem to find a way out of this dilemma.

Any feedback is appreciated.

Cheers!!!

[Luiz Gregorio]

Still useful in 2019.

It solved my problem!

Thank you!

[Carol Jones]

Still useful in 2021 too! Thanks @JamieA !

[Filipi Lima]

Still useful in 2022 :)

[Yana Stoliarova]

To be more accurate about what was asked:

statusCategory != Done and
issueFunction in linkedIssuesOf("type = Story AND statusCategory = Done", "has epic") and
not issueFunction in linkedIssuesOf("type = Story AND statusCategory != Done", "has epic")

In case "Done" is not the only green status. And if you are interested only in Story issue type

[Carol Orton]

Thank you for your quick response - but unfortunately I have multiple end states and need them to be in the query.  It doesn't seem to be the end states - if I have just the first half of the query - it works.   When I add the ----  and not issueFunction inlinkedIssuesOf - it won't save it.  

[Yana Stoliarova]

We can improve the JQL if you can provide:

1. Which statuses you treat as incomplete for your Epics?
2. Which statuses demonstrate that Story is complete?

[Carol Orton]

Here is the query I was using - as I said works great in Jira - and works in confluence as a table but I am having trouble displaying as total issue count.  

Project = xxxxx and fixVersion = "xxx xxx-xx" and status != Verified and issueFunction in linkedIssuesOf("status in (Verified, rejected, done, active, draft)", "has epic") and not issueFunction in linkedIssuesOf("status not in (Verified, rejected, done, active, draft)", "has epic")

Technically all I care about is that all the stories are in Verified or Rejected and any tasks/subtasks are in Done or rejected.   The Active and Draft status is because we have a type of requirement and type of Initiative that also link to the epic.  I don't really care what status they are in because the epic closes first but it wouldn't find my completed epics unless I used active and draft to weed out the other types.  

What was odd is that I did get it to work (with the total count) and then something changed because when I changed only the fixVersion to the next increment it wouldn't save the query in Confluence unless I changed it from total count to table. 

[Jason Ambrose]

Try this JQL: issuetype = Epic AND resolution is not EMPTY AND issueFunction in linkedIssuesOf("resolution is EMPTY", "has epic")

This should work to identify all epics that are resolved with linked issues that are unresolved.

[Carol Jones]

or

resolution is not EMPTY AND issueFunction in epicsOf("resolution is EMPTY")

the above is to assist with finding epics that were closed out but still have open items associated

Our team also utilizes:

status!="In Progress" AND issueFunction in epicsOf("sprint in openSprints() AND resolution is EMPTY")

to identify Epics that aren't currently in the "In Progress" status but should be due to having associated stories in the current sprint that aren't yet completed

[JD Scott]

Thanks @JamieA

You gave this answer 5 years ago, and due to the magic of the interwebs, I was able to find your JQL today and use it to find the Epics I was looking for.

Cheers mate!