How to write a nested query

[Anupam Miharia]

So we have Initiatives that have Epics and they in turn have Stories that are pointed.

I would like to query for a given initiatives, give me a sum of all points planned and all points completed.

How do I structure this query in JQL?

[Trudy Claspill · Accepted solution]

Hello @Anupam Miharia 

Welcome to the Atlassian community.

Jira does not have native functionality to support nested queries. That would require a third party app.

[Anupam Miharia]

Which third party app would you suggest for this kind of query? It seems pretty simple to be able to do this.

[Trudy Claspill]

For additional reference, here is the documentation regarding searching fields related to the Advanced Roadmaps functionality.

https://support.atlassian.com/jira-software-cloud/docs/search-for-advanced-roadmaps-custom-fields-in-jql/

You'll notice that none of the functions allow insertion of another filter.

However with the portfolioChildIssuesOf() function you can get all the child issues of Epics that are under a single Initiative. That doesn't provide you with a sum of the story points, just a list of the issues.

I don't have a recommendation for a specific third party app that would enable you to create the specific query that you want. It would have to be able to parse the extended issue hierarchy architecture also. 

You can search the Altassian Marketplace for apps that extend JQL functionality and/or apps that extend the reporting capabilities to sum up information from child issues.

Alternately you can use Automation Rules to sum up story points from child issues to parent Epics, and then from child Epics to parent Initiatives, and record the information in a custom field at the Initiative level. Then your filter would only need to retrieve Initiatives.

[Anupam Miharia]

Thank you @Trudy Claspill 

[Hannes Obweger - JXL for Jira]

Hi @Anupam Miharia

if I may offer a concrete recommendation from the Atlassian Marketplace, this would be trivial to do using 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 number of advanced features, including support for (configurable) issue hierarchies, as well as sum-ups. With these, you can build a view like this in just a couple of clicks:

As you can see above, your view is fully interactive - meaning that you can work on your issues directly in JXL (but also trigger various operations in Jira, or export your data to CSV or Excel in just a click). You can also configure different sum-up styles, and combine the above with JXL's other advanced features, such as bulk editing via copy/paste, issue grouping by any issue field(s), or conditional formatting.

Any questions just let me know,

Best,

Hannes

[Anupam Miharia]

Thanks @Hannes Obweger - JXL for Jira  I will check it out