Need Help with a JQL Query (Stories that have epics already in Done status)

[Athar Faheem]

I am not able to frame a JQL query to find out the below. Can anyone help me with it?

Our JIRA project has Stories linked to Epics.

I need to find out all stories that have been assigned to Epics AND the Epic that these stories have been assigned to have an Epic Status as Done. 

Update [Found the Solution]
I was able to finally resolve this issue with help of some JQL experts. The below query did the trick. Pardon my ignorance about JQL functions. But that is something I learnt can really enrich the JQL queries. 

issueFunction in issuesInEpics('project in (XXXXXX) AND status = "Done"') and issuetype = Story and status not in (Done, Descoped, Deleted)

XXXXXX - JIRA Project Key

[Amay Purohit_RVS]

Hi @Athar Faheem 

Welcome to the community !!

If you would be interested in an add-on for this need, you can try out our plugin.

Issue Hierarchy 

The app shows your Epic hierarchy in a tree view. There is an additional JQL filter as well for child issues. You can add it as a dashboard gadget too.

Also the app can help you easily Sum up values (time tracking/story point/custom numeric fields) to see overall progress for your Issues at each parent level, in percentage terms. 

Do give it a try.

(Disclaimer: I work on RVS, the vendor for this app)

[Trudy Claspill]

Hello @Athar Faheem 

Welcome to the Atlassian community.

Your requirement can't be fulfilled with only the native JQL capabilities. It could be fulfilled with the help of certain third party apps that extend JQL capabilities, like ScriptRunner Enhanced Search or JQL Search Extensions. Do you have any apps like that?

[Athar Faheem]

No, we don't have any extended JQL capabilities like that (unfortunately). 

[Trudy Claspill]

You could do a work around with two queries.

In the first one retrieve all the Epics with a status of Done. Export that result set to get a list of the Epics' issue keys.

Plug that in as a comma separated list in a second query like this:

IssueType=Story and Parent in (comma separated list of Epic issue keys from first query)

[Athar Faheem]

Thanks for the suggestion. However, I want to place the "magical" query in a JIRA Dashboard for managing data hygiene. 

So this work around will not work unfortunately. : (

[Trudy Claspill]

Then I believe the only way to satisfy all your requirements is to acquire a third party app such as the ones I suggested.

[Athar Faheem]

I was able to finally resolve this issue with help of some JQL experts. The below query did the trick. Pardon my ignorance about JQL functions. But that is something I learnt can really enrich the JQL queries. 

issueFunction in issuesInEpics('project in (XXXXXX) AND status = "Done"') and issuetype = Story and status not in (Done, Descoped, Deleted)

XXXXXX - JIRA Project Key

[Trudy Claspill]

Correct me if I am wrong, but I believe that solution requires the use of a third party app, doesn't it?

[Charlotte Santos -Appfire-]

Hi @Athar Faheem

I’m Charlotte, a support engineer at Appfire, and I’m here to help you.

Unfortunately, using JQL of Jira, you’ll not be able to do it dynamically.

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find all Stories whose Epics are Done:

issue in childrenOfEpicsInQuery("status = Done") AND type = Story

Please contact our support if you have any other questions about this query.

We’ll be happy to help you! 😉

[Hannes Obweger - JXL for Jira]

Hi @Athar Faheem

welcome to the community!

As Trudy suggested, this can't be done in native JQL. 

If you're open to solutions from the Atlassian Marketplace, you may also want to have a look at 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 or Google Sheets. It also comes with a long list of so-called smart columns that aren’t natively available, including the parent status.

With this, you can easily find the issues you are looking for, e.g. like so:

As you can see above, you can easily sort and filter by the parent status. You can also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes