Using JQL, how do I find points for all stories under some epics having labels "XYZ"

[Sanjay Munsi]

I am surprised with how I can do this with JQL. 

In JIRA world, epics have features and features have stories. I am trying to do the following.

1) Find all epics with label: "Physics" and "Math"

2) Look at all the features for each of these type of epics and then the stories under all these features and story points. I have to do this both "Physics" and "Math" epics.

3) Aggregate all the story points. That will give the level of efforts for each kind of epic.

Can someone please let me know how I can do these all using JQL? Thanks in advance for your help.

[Trudy Claspill · Accepted solution]

Hello @Sanjay Munsi 

Welcome to the Atlassian community!

You cannot accomplish that with the native JQL functionality because it would require using subqueries. Are you open to using third party apps?

An alternative is to use automation to total up the story points from the stories to their parent Features and store the value there in a custom field, then total up all those values in the Features and store the information in their parent Epics. Then you would be able to create a JQL to retrieve the desired Epics and simply display the field that carried the tallied story points.

[Sanjay Munsi]

Thanks a lot @Trudy Claspill. Let me try with the alternative approach first. But it may be very manual and time consuming if we have too many epics with those labels and have too many features and stories under them. 

What kind of third party app can you recommend?

Thanks for your help again.

[Trudy Claspill]

There are several third party apps that extend the capabilities of JQL searches. You would need to investigate their specific features to ensure you could get what you need. Here is a search from the Atlassian Marketplace to help you get started.

https://marketplace.atlassian.com/search?hosting=cloud&product=jira&query=jql

The one I am most familiar with is Adaptavist Scriptrunner. I have not taken the time to try to implement your request with that app though. They provide additional functions that help you retrieve issues based on links and parent/child relationships, allowing you to imbed filters within those functions.

That might help you find all the issues you want to sum up. I'm not sure about the next step for how to show that sum.

[Sanjay Munsi]

Thanks again @Trudy Claspill for your help.

[Hannes Obweger - JXL for Jira]

Hi @Sanjay Munsi

just to add to the above: For a solution that covers both the "query" and the "sum-up" aspect of your use case, you may want to have a look at the app that my team is working on, JXL for Jira.

JXL is a full-fledged table/spreadsheet 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 issue hierarchies and sum-ups. With this, you should be able to model something like this in no time:

I just put this together in a couple of minutes; you can view, filter by, and sum-up any issue field, perform different sum-up calculations, and much more.

Hope this helps,

Best,

Hannes

[mauricio.groth]

Hi @Sanjay Munsi

I’m Maurício, a support engineer at Digital Toucan and I’m here to help you.

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

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find all your stories that are children of epics whose label contains both Physics and Math

issue in childrenOfEpicsInQuery("labels in (Physics, Math)") and type = Story

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

We’ll be happy to help you!
Best regards,
Maurício