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.
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!
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")
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.
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 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?
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
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.!
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 .
Not sure if i have understood your requirement..!
But these are my thought (this is already in this thread)
Hope this helps.!!
Mind: I so wish Jira can provide some widgets and graphs inbuilt for this JQL.!
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.
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.
This approach is tedious but it works for stories in more than 1 sprint...
find all of the sprints for your board:
(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 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.
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).
Hi All! We’re excited to share the launch of an announcement banner that lets Jira site administrators communicate directly to their users across their Jira Cloud instance. ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events