JQL - Retrieve all issues for a component, including Epic Sub tasks

[David Kelman]

I am hoping you may be able to help me with a jira JQL query when using the search / filters.

I am trying to create a pie chart in a dashboard to show all open tickets in a sprint, which the below code kinda does, however as the only open item in my component for the current sprint is an Epic, I am only returning 1 total issue in my pie chart

project = ProjectTestExample AND Component in (TestComponent) AND status in (Backlog, "BE Refinement", Blocked, Cancelled, "Deployed Dev", "Deployed UAT", "FE Refinement", "In Design", "In Progress", "L&D", Ops, PR, "Ready for Release", "Sprint Ready", "Technical Design", Test, "Test Done", "Test Failed", UAT) AND Sprint in openSprints()

So I then added an OR function and referenced the epic in question, however when I add the epic in question it returns all items, regardless of status to the pie chart

project = ProjectTestExample AND Component in (TestComponent) OR "Epic Link" = AB-1234 AND status in (Backlog, "BE Refinement", Blocked, Cancelled, "Deployed Dev", "Deployed UAT", "FE Refinement", "In Design", "In Progress", "L&D", Ops, PR, "Ready for Release", "Sprint Ready", "Technical Design", Test, "Test Done", "Test Failed", UAT) AND Sprint in openSprints()

I feel like referencing the individual epic link is an inefficient way to go as it involves alot of maintenance. Is someone able to advise on a better way of utilising this code?

[Bill Sheboy · Accepted solution]

Hi @David Kelman -- Welcome to the Atlassian Community!

It appears you are running into an Boolean order of precedence issue, so let's see if we can shorten the query and make it easier.  In general, it can help to structure your query with all of the common expressions first (i.e. true for all issues you want) and then add the conditional OR ones.

First, I am going to assume that all of the issues have that component set and that all of the status values you listed are either in the "todo" or "in progress" categories.  That allows simplifying a bit.  I added some line breaks and spacing to make it easier to read.

project = ProjectTestExample
 AND Component IN (TestComponent)
 AND Sprint IN openSprints()
 AND statusCategory IN ("To Do", "In Progress")
 AND (
      key = AB-1234
      OR "Epic Link" = AB-1234
     )

Is this what you wanted?

Regarding query maintenance, it can be challenging to keep these up to date as work evolves.  Consider if there is a way to make your query generic enough to not require updates, or investigate options with Automation for Jira rules to automatically update your queries using the REST API.

Kind regards,
Bill

[David Kelman]

Hi Bill, thanks for the help!

Unfortunately I was unable to get it to work, but I was also looking for a way to future proof the code so when new stories or epics are added to the active sprint, there would be no maintenance required or editing of the code to reference the new epics., so normal issues & issues toed to an epic would all be visible.

Thanks in advance.

David

[Bill Sheboy]

Hi David,

I am wondering if I misunderstood your use case.  Let's try some specific questions to get back on track:

1. What do you want the pie chart to indicate?  (i.e. What problem are you trying to solve?)
2. What statistic type are you using for the pie chart?
3. For the sprint, are the epics and stories/tasks both in it, or only the stories/tasks?
4. Which issues have the component assigned: the epic, or also the stories/tasks?
5. Was I correct that you only want to report on "to do" and "in progress", or do you also want to include "done" issues?

Thanks!
Bill

[David Kelman]

Hi Bill,

1. Dashboard Items
   1. Pie Chart: I am looking to get a pie chart to show all issues that have entered the current sprint, and what status they are. Done, In progress, blocked ect. The aim is we can use the pie chart to show sprint progression. This we want to do on a component per component basis.
   2. Filter Results: We also are looking to use the dashboard gadget "Filter results" to display all the items that are still to be completed, so this gadget would omit the completed / done issues.
2. Statistic: Status - The status of the issues
3. Sprint: We have Epics, stories and tasks in a sprint. So there would be a mixture of Epics with their sub tasks / stories associated with the epic, and then stories that are not associated with an epic in the same sprint.
4. The component is assigned to both epics and individual stories / tasks
5. What to report on: Pie Chart - All Status' | Filter Results - All but completed Status'

Edit: As a side note there are multiple epics in a sprint for a component, so ideally we do not want to have to reference each epic as we would constantly be updating the JQL to include new epics that come into the sprint

Thanks for the help, really appreciate it.

[David Kelman]

So after continuing my dashboard with the intention of updating the code later I came across another component that had multiple epics in the sprint, but the issues were appearing on the dashboard pie chart and filter results.

it appears to be if the child cases of the epic are tasks, they will appear, but if the child cases of the epic are stories, they will not appear.

So to add to the above, is there a way to include stories of epics without referencing the epic? 

[David Kelman]

After looking into this further I need to facepalm quite hard.

The reason why they were not showing up in a sprint was because....... they were never in a sprint.

Turns out the team are just putting the epic in a sprint, but leaving the stories out of a sprint.

Did a few tests to see if my dashboard updated, which it did so it was not my code that was faulty, but the way the team was operating was not useful to the way I was creating the dashboard.

Time for some discussions on ways of working!

Thanks for the help, and I am so sorry for the time you have spent looking into this when it was something I should have spotted on my end.

Have a good day!

[Daniel Turczanski - JQL Search Extensions]

Hi @David Kelman,

As you're on Jira Cloud, the correct answer is to get an app that provides JQL extensions you're looking for.

With standard JQL, you can only get a list of issues and export them to Excel for further processing. This works if you want to do a one-off analysis. If your use case is more dynamic than that, look beyond standard Jira.

Standard JQL doesn't easily allow it, but you can quickly find the results using our professional indexing service JQL Search Extensions

After you install the app, you can simply search:

component='TestComponent' OR issue in childrenOfIssuesInQueryRecursive("compoent='TestComponent'")

Check out the documentation for more examples.

I hope this helps!
Daniel