JQL - How to filter stories spilled over multiple sprints?

[Sanjog Sigdel]

Hello Community,

I tried this query to list the stories moved over two sprints 510 and 511

project = "Project Name" AND issuetype = Story AND Sprint CHANGED during( "510" TO "511")

But Jira shows following error:-

• History searches do not support the 'sprint' field.
• The value '510' does not exist for the field 'sprint'.
• The value '511' does not exist for the field 'sprint'.

Have any of you used the filter to list stories spilled over iterations? If yes can you please suggest my how to do the right query?

Thank You

[Yuliia_Borivets__SaaSJet_]

Hi @Sanjog Sigdel 

Do you want to see only issues that changed from one sprint to another (from 510 to 511) or the ones that went through two sprints (510+511)?

I can suggest Issue History for Jira app as an alternative solution developed by my team. Here you can search by JQL and get the whole history of changes for these two sprints. 

1. Write JQL as follows:

project = "Project Name" AND issuetype = Story AND Sprint in (510,511)

2. Add the Sprint column to the grid.

You will get the whole picture: what stories were in one of the sprints, what were transferred from one to another.

If you want only those stories moved from one sprint to another, you can export the report with data to an XLS file or CSV and filter there.

Here is a similar use case for projects: How many tickets were moved from one Jira project to another?

That's the only workaround that came to my mind.

There is a 30-day free trial, so you check if it can help you.

[Sanjog Sigdel]

Hi @Yuliia_Borivets__SaaSJet_ 

I'll give it a try. But it's not always two specific sprints a ticket gets spilled. Sometimes tickets slippage happens for more than two sprints. Also it's Sprint I'd 511 and 512 for now. But next month it will be 513 and 514.

Any suggestions on making the filter dynamic with reference to open sprint and future sprint?

[Yuliia_Borivets__SaaSJet_]

@Sanjog Sigdel If tickets are moved to more than two sprints, I suggest monitoring sprint history (in the sprint column). It shows all sprints an issue was in one raw. For example, select one sprint 512 you are interested in, and you will get all tickets and information about sprints those tickets are now or went through.

If you try an app and need more help with your case, you are welcome to schedule a demo and our experts will help with configuration.

[Nikhil Kumar]

Hi @Sanjog Sigdel, I too ended up using the workaround @Yuliia_Borivets__SaaSJet_suggested, after downloading the list of issues in excel I use COUNTA and COUNTIF functions to identify columns which are not empty and then column values which have spilled from oner particular Sprint to another. Please suggest if there are any better approach. 

[Kavitha Chandrasekaran]

project = "Project Name" AND issuetype = Story AND (Sprint in closedSprints() and sprint in futureSprints())

If an issue was present in the past closed sprint and in future sprint (if the latest sprint is not started, this query will work)

or

project = "Project Name" AND issuetype = Story AND (Sprint in closedSprints() and Sprint in openSprints())

If an issue was present in the past closed sprint and in open sprint (if the latest sprint is started)

or

project = "Project Name" AND issuetype = Story AND (Sprint in (2, 3) and Sprint in openSprints())

If you want to search is an issue was in any of the past sprints, you can specify the sprint ids. 

You can modify this query with AND/OR clauses to check for specific sprints

Try to keep the JQL generic, so it can be applied in the future. 

Hope this helps. 

[Walter Buggenhout]

Hi @Sanjog Sigdel,

As the error message says, Jira does indeed not support history searches on the sprint field. That means you cannot perform searches like WAS or CHANGED TO etc. on sprints unfortunately. There is a feature request for it that you can track here: JRACLOUD-39256 (it's a very old one, so I would not expect any progress there soon)

You can see issues that were removed from sprints in the sprint report, if that may help.

Hope this helps! 

[Sanjog Sigdel]

Looks like the request on history searches wont be supported by Jira. Keeping the question open for some time. Let's see if there are any workaround. Otherwise I'll close the question accepting this answer.

@Walter Buggenhout  Thank You for letting me know about it.

[BurakK]

Hi @Sanjog Sigdel , I use this JQL to view stories that carry forward and still in open sprint. I thought it might be helpful, that's why I want to share in here:

project="Project Name" AND issuetype=story AND sprint in openSprints() AND sprint in closedSprints() AND status !=Closed

[Nic Brough -Adaptavist-]

You don't need to worry about the "changed" part of this.

When an issue rolls over into the next sprint, it does not lose the information that it was in the sprint (that your team need to talk about where they failed to complete what they said they would)

You can find these with the query

 project = "Project Name" AND issuetype = Story AND Sprint = "510" AND Sprint = "511"

(I suggest typing the two sprint = clauses because autocomplete will help you find the sprint names)