JQL query - Closed Deliverable with open children (epic, story, sub-task)

neelam annad January 25, 2020

Can someone help me as for the below JQL query - JQL query - Closed Deliverable with open children (epic, story, sub-task)?

3 answers

1 accepted

0 votes
Answer accepted
Muhammad Ramzan(Atlassian Certified Master)
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.
January 25, 2020

@neelam annad , what do you means by deliverables?

 

Do you want to extract closed issues with open sub tasks or 

you want to search fixversions with open issues 

 

could you please explain ?

1 vote
Stephen Wright _Elabor8_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 25, 2020

Hi @neelam annad

Assuming the Epic is the top layer, There is this query:

issue in childIssuesOf("EPIC-123") and statusCategory != Done

^ This effectively would give you all issues below one Epic which are not in a status in the "Done" category (i.e green statuses). This works with Portfolio also - so if you had more hierarchical layers above Epic, you could search down from an Initiative (for example), through all its Epics > Sub-Tasks.

If you have more than one green status though, this also works:

issue in childIssuesOf("EPIC-123") and resolved is empty

I'm pretty sure these functions are in the base advanced searching, but let me know if they don't appear as our instance has got quite a few add-ons.

If Epic is not the top layer, you can also put the top hierarchical layer into this to see all child issues.

-------------------

If searching one specific Epic is insufficient, and you want to search "all" Epics, then you'll need to consider an app such as ScriptRunner from the Marketplace. Scriptrunner allows you to set a sub-query to be considered in the main query.

So for example, if I want to search for all open issues within Epics with are resolved, I could do:

issueFunction in issuesInEpics("resolved is not empty") and resolved is empty

^ The "resolved is not empty" is the sub-query run against the Epics, before the main query runs against the stories which are shown in the results. Extending this to sub-tasks:

issueFunction in subtasksOf("issueFunction in issuesInEpics('resolved is not empty') and resolved is empty") and resolved is empty OR issueFunction in issuesInEpics("resolved is not empty") and resolved is empty

And you have the results across all Done Epics with open subordinates.

This might not be the cleanest JQL method, but it visualises the logic behind the query - you can shorten it by saving the first query as a filter then searching for "filter = XXXX" in the second query, for example:

issueFunction in subtasksOf("filter = 12345") and resolved is empty OR filter = 12345

See more JQL functions in ScriptRunner here, or look for alternatives in the Atlassian Marketplace (try searching JQL or Search).

Again if Epic isn't the top layer, let us know what is so we can help further :) - these queries can work with FixVersions, Portfolio Hierarchical layers and similar.

Ste

Steffen Jantke April 23, 2020

Very helpful post! Thank you very much!

neelam annad April 23, 2020

Thank you, I want to extract the data from deliverable level. All the deliverables -> all the epics -> all the stories -> all the sub tasks.

Stephen Wright _Elabor8_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 26, 2020

Hi @neelam annad 

Is deliverable an additional issue type above Epic (eg. set via Jira Portfolio) or is it what you're referring to Projects as?

Ste

0 votes
Lenin Raj
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 25, 2020

Hi @neelam annad 

Can you explain your query again? Do you need to write a new JQL query?

neelam annad April 26, 2020
issueFunction in subtasksOf("issueFunction in issuesInEpics('resolved is not empty') and resolved is empty") and resolved is empty OR issueFunction in issuesInEpics("resolved is not empty") and resolved is empty

 

if we want to start from sub-task till deliverable what should I do?

Suggest an answer

Log in or Sign up to answer