nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

KyleB

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 &gt; 2

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

Thoughts?

Accepted answers

All comments

Volodymyr Krupach @BrizoIT

You can use "in" clause:

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

KyleB

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.

Jeanne Howe

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.

KyleB

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.

mike mogensen1

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))

Jon Cooper

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.

Jeanne Howe

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")

Luke Galardi

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

Aron Gombas _Midori_

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.

Victor Michalet

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!

Luke Galardi

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.

Dinesh Chaturvedi

This does not work for me, I cannot find out all the sprint numbers I want to exclude include. I am working on a project were we have created 4 Jira projects one fore each scrum team and we share work. A issue worked by one team and not finished by them can he handed over to another team based on priority.

sudarshan_x34rab

This work good with excel.! I just managed to get the result and % of US completed in a sprint and which overflowed.!

But this is required using a JQL.

sudarshan_x34rab

Hello,

Just want to activate this thread.

Anyone hopes on this Jira Query?

Thanks.

sri sura

Hey Sudarshan,

I tried many combinations with JQL as well, but could not figure it out.

Another easy way my co-worker figured out without having to do the JQL, is to view the burndown reports and see the number of tickets carried forward in each sprint. This is the easiest turn around I guess.

Michael Durbin

Yeesh! Extremely disappointing that such a thing still does not exist! I get wanting to farm this out to the pay plug ins, but even that is failing. We use Script Runner cloud and it doesn't support this either! Sure at a point in time when a new sprint is started you can find the stories that just carried over, but once the current sprint is over there is no good way to find stories carried over from one closed sprint to another.

Come on Atlassian, this is a no brainer!

Nauman Ikram

sprint in openSprint()

and sprint in closedSprint()

give the right answer if I want to search issuetype = Bugs with specific statuses.

Thanks.

Diane Heckman

Yes, but once you close the current sprint, you can no longer tell which stories carried over into opensprint -1 (the last sprint). I need to track all sprints in the quarter, and track whether we are carrying over fewer stories as we "improve". I still haven't found a way in JQL.

Manisha Kode

I use this query to find carried over stories

project = "Projec name" AND issuetype not in ("General Subtask", Retrospective) AND sprint is not EMPTY AND sprint in closedSprints() AND sprint in openSprints() AND status not in (Closed, Complete, Cancelled, Done)

Vivek Singh_svfogg

This is somewhat giving close result.

sudarshan_x34rab

Hello,

I use this JQL to get all the issues (except sub-tasks) which were present in closed sprints

project = "Project name" AND Sprint in closedSprints() AND type != Sub-task

with this every release we try to find how many story were involved in more than 2 or 3 sprints, and try to find out why it took so long.!

There is some small amount of excel work involved.!

Manisha Kode

I tried to use excel but you get all sprints in different columns - do you have a way to get the last sprint as I wanted to pivot to understand particular type of stories completed in each sprint - if the story is carried over multiple times i am not able to get the last sprint when it was closed easily .

sudarshan_x34rab

Not sure if i have understood your requirement..!

But these are my thought (this is already in this thread)

use the JQL to get the required results.
make sure u have set all the required columns for export (atleast ID, Sprint)
export to excel.
now you will have one column in excel named sprints -- it would have values like Sprint1, Sprint2, etc) for multiple sprints or (Sprint1 - if the story is present in one sprint.
so in this column you split based on the comma (,) to get it more structured.
now from here with excel functions u can get various graphs and insights.
my query only gets stories from closed sprints.
to get the last sprint when a story was closed -- you can add another part to the query as Status = Done
- this would result stories with status done.
- so the last sprint value will tell you when the story was closed.

Hope this helps.!!

Mind: I so wish Jira can provide some widgets and graphs inbuilt for this JQL.!

Vivek Singh_svfogg

Please use this JQL

project = "projectid" AND issuetype = story and sprint in openSprints() and sprint in closedSprints()

This will give you all User Stories which all are open in current sprint and been carried forwarded from last sprint or last few sprints.

Then export the result in excel.

e.g. If you are in Sprint 5 and couple of user stories been carried forwarded from Sprint 1,2,3,4, then you will be getting 5 Sprint column separately.

Then put filter and remove blank post filter in Sprint 5 column (Last Sprint Column in this case), this will give you exact number of User Stories which has been started in Sprint 1 and carried forwarded till Sprint 5.

Similarly you can do for other sprints as well

e.g. if any User Story Started in Sprint 2 and Carried Forwarded till Sprint 3 only than Apply filter and remove the blank in 3rd Sprint Column, will give you exact result.

Hope this help, if still not giving the accurate result, please let us know........

For better understanding of your data, you can concatenate the all sprint column in a new column, this will also give you very clear picture of carried forwarded User Stories

Buck_ez0a76

Here's my query, hope this helps.

project in (PROJECTNAME) AND status = DONE AND sprint in closedSprints() AND sprint in openSprints() ORDER BY cf[10010] ASC, Sprint ASC

Raju Mandapaka

issuetype = story and (Sprint = 3721 and Sprint =4423)

this Jql works for me, it will shows all the stories spill over from sprint 3721 to 4423

Roland Muts

This is infuriating. I need to do some data mining on data. I have 17 closed sprints, 1 active sprint and 5 future sprints..

When i search for

project = "ABP" and sprint = 21 and status = Done

there are multiple tickets with at least two sprints assigned, because the story slipped from one sprint to another. What do i need to do to get just one sprint returned?

How can i get a historic view of what ticket was closed in what sprint, using JQL?

After exporting the search to excel, i see for those tickets multiple columns for the sprint designation.

How can i get just the sprint in which any ticket was closed?

Andrew Newbury

I was also frustrated tracking spillover, so per How can I find an issue that is in multiple sprints? I wrote a PowerShell script, available on GitHub, to do this. It queries a specified Jira project to find work items (stories, tasks, and bugs) that have been:

Modified within a user-defined timeframe
Worked on across multiple sprints (indicating spillover)

Results are then displayed on screen and exported to a tab-separated text file for further analysis in Excel or similar apps. It accepts command line arguments so you can bulk query multiple projects, I've used it for dozens of projects to pull data to identify long-term spillover trends across a portfolio.

I wrote an article, Mastering agile spillover analysis and strategic solutions. to then describe what teams can do about it.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-software