How do i write the JQL to find all the stories associated with multiple sprints?

I am attempting to write a query that can help show me stories that have carried over.  In Jira, they are still associated to all of the sprints they have been a part of.  So in this example, I have story X that I added to sprint 1.  Then that story was not completed and it carried over to sprint 2.  And say for whatever reason it then carried over to sprint 3.  I want to write a JQL statement that will show me this so I can apply this to a large swath of projects.

I can write this query that returns the result:

status != Closed and sprint = 1

I can also write this query that would return the same result:

status != Closed and sprint = 2

But what i really want is something like this:

status != Closed and NumberOfSprints > 2

But I can't figure out how to do something equivalent to NumberOfSprints.

Thoughts?

4 answers

I have jql that looks to see if an issue is in both a closed sprint and an active sprint

sprint is not EMPTY and sprint in closedSprints() and sprint in openSprints()

 

You could run this and then combined it with a query that looks for issues in closedSprints() and futureSprints() - this would find any issues moved to a sprint that has not yet started (so it's not "open")

 

Thank You Jeanne.  How would one find an issue that is only in more than one closed sprints?

Just wondering if you ever found a solution to this?

I am still trying to get a way to filter issues that have been carried forward for 3 sprints or more.

Thanks!

Hello Victor, I really have not found found anything I can do solely in Jira to get these results.  One can export the results of a jql query to excel and work with the data from there, but that is a two step process which our end users would not be as comfortable with.

When time permits, I am going to finish a sql query I am working on to find issues that meet this criteria, then try to build a custom jql tag.  

I'll update this thread should I get a solution.

0 votes
Volodymyr Krupach Community Champion Feb 07, 2015

You can use "in" clause:

status != Closed and sprint in (1,2,7)

In this case, I want to find stories that have appeared in more than two sprints, as an indication of active stories that have been carrying over repeatedly. At this point, I would not know which specific sprints they appear in.

Kyle, Have you had any luck getting the jql? I am looking to do the same thing - find issues that have been carried over into multiple sprints.

I have not had any luck as of yet. I've thought about maybe putting a label on stories that carryover, but that involves a lot of manual intervention and will be very error-prone. But no luck on using JQL to do it yet.

also this answer gives you the list of stories that are in sprint 1 OR, 2 or 7, not AND.

I want to do this too so that I can see how frequently 13 point stories are completed in a single sprint.  Really what I want to see is the frequency that a story is carried over into another sprint by story point estimate (i.e. 90% of 5 point stories are completed in 1 sprint, 90% of 13 point stories get carried over).

This approach is tedious but it works for stories in more than 1 sprint...

find all of the sprints for your board:
https://<your JIRA>/jira/rest/agile/1.0/board/<your board ID>/sprint

using all of the sprint IDs create a query similar to this:

issuetype != Sub-task AND
(sprint = 111 AND sprint in (222, 333, 444) OR
sprint = 222 AND sprint in (111, 333, 444) OR
sprint = 333 AND sprint in (111, 222, 444) OR
sprint = 444 AND sprint in (111, 222, 333))

This is probably contrary to the post (i.e. a way to do this with JQL), but i do reports on this out of excel. Here are the steps:

1. Do an issue search for: project = "Project Name" and status = Done order by key asc

2. Filter using the Colums filter for "Key" and "Sprint" only. 

3. Export to Excel, remove the top 3 Rows and Bottom row (junk Jira puts in the export) 

4. Save as CSV, open new excel worksheet and import data from Txt, choose the CSV and add Delimeters on Commas. (this will save each sprint label into a new colum for every key) 

5. on the last empty column, use the CountA function to count non-blank cells in as many columns as you have sprint data in (i sometimes have up to 5 :( ) 

The end result is a list of all stories and how many Sprints they touched. You can also add additional fields in step 2 like Assignee if you want to see who has a habit of carrying over stories etc... just make sure the spint field is at the end of the CSV for ease of importing. 

This is smart, and you could save most (all) of the manual work if you used a custom template with the Better Excel Plugin. It supports

It would export sprints separated by comma to a single column and as it supports functions and formulas, you just need to count the sprint names.

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Thursday in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

928 views 5 18
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot