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

T February 27, 2013

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.

7 answers

1 accepted

20 votes
Answer accepted
JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 27, 2013

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 February 1, 2018

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

Like # people like this
Raghunandan_Sira August 14, 2019

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

Just used it today to extract the results I needed.

Like Sharon Cryan likes this
Roland Goguen October 17, 2019

@JamieA This is great, many thanks!!!

Alex Lithgow April 16, 2020

@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 April 16, 2020

@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 January 15, 2021

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")
Like # people like this
Carol Orton May 17, 2021

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 4, 2021

@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 December 20, 2021

Can this be done without using script runner functions?

Richard Cross July 7, 2022

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

Kevin Paulovkin February 2, 2023

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 April 19, 2023

Thanks Jamie!

9 votes
Stephanie-(ATO) November 13, 2019

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?

1 vote
Vincenzo Ciaravella November 23, 2021

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!!!

1 vote
Luiz Gregorio May 15, 2019

Still useful in 2019.

It solved my problem!

Thank you!

Carol Jones February 1, 2021

Still useful in 2021 too! Thanks @JamieA !

Filipi Lima
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 28, 2022

Still useful in 2022 :)

0 votes
Yana Stoliarova May 17, 2021

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 May 17, 2021

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.  

Like Yana Stoliarova likes this
Yana Stoliarova May 18, 2021

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 May 18, 2021

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. 

0 votes
Jason Ambrose October 30, 2019

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 February 1, 2021

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

0 votes
JD Scott December 27, 2018

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!

Suggest an answer

Log in or Sign up to answer