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

[neelam annad]

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

[Muhammad Ramzan_Atlassian Certified Master_ · Accepted solution]

@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 ?

[Ste Wright]

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]

Very helpful post! Thank you very much!

[neelam annad]

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

[Ste Wright]

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

[Lenin Raj]

Hi @neelam annad 

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

[neelam annad]

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?