I want JQL query to get story point Planned & completed in a latest closed sprint

[dallu divya]

I am trying to extarct data for multiple teams i need data for sprint that closed recently before the new sprint start . From that i need 2 colunm data Sprint ID , Story Point planned , STory point completed

[Nic Brough -Adaptavist- · Accepted solution]

Welcome to the Atlassian Community!

You will have to derive that, because the data is the same field.

Run a search for the sprint you are interested in (sprint = X will do it), and include the columns Status and Story Points in it.  Add up all the points to get "committed" and then the points for any issue that is in a status in the last column of the board to get "completed"

[Trudy Claspill]

Welcome to the community @dallu divya .

I want to add a clarification to what @Nic Brough -Adaptavist- said.

When you are looking at a sprint report or a Velocity chart and see the Story Points Committed value, that is the total of Story Points on issue in the sprint at the moment the sprint was started. After that, Story Point values could be changed and/or issues added/removed from the sprint.

There is no specific field that keeps track of that Committed value from the moment the sprint was started.

If you run the filter as Nic suggested and add up all the story points for a sprint at some later date, this may not match the Committed value you see for the sprint in the above reports. Nic's results will provide you the current Story Point values on issues currently in the sprint. This may not be the same as the values and issues that were in the sprint at the moment it started.

[dallu divya]

can you please write and give me ,I will my team details in that query

[Trudy Claspill]

The filter to retrieve the issues for a specific sprint is simply

sprint=<fill in your sprint here>

That will give you a list of all issues assigned to that sprint.

Change the output display to List View if it is not already set that way.

Add columns to the display for Sprint, Status, and Story Points.

The output will then contain all the information you need to total up the story points.

[Nic Brough -Adaptavist-]

Yes, sorry, I missed that bit. 

There is no JQL for "points committed at sprint start" because it is not held on issues.  It's held on the sprint.

[Vix JIRA]

@dallu divya  - I have a solution for this which I will detail below, with Queries.

Adding @Nic Brough -Adaptavist- and @Trudy Claspill so they can answer this type of question precisely and accurately in similar community asks by other members.

I have the same need where I want to find the:

1. Original Sprint Planned items (Planned)
2. Items Added after the Sprint started (Added)
3. Items Removed after the Sprint started (Removed)
4. What was ultimately completed in the Sprint (Achieved)

In order to do this, I have to follow these steps:

• Step 1: Find how many items were completed within the Sprint (Achieved)
  • This is also your Sprint Velocity
  • Take a note of the search (filter) ID
    
    
• Step 2: Find how many items were Added after the Sprint started (Added)
  • Take a note of the search (filter) ID
• Step 2: Find how many items were Removed after the Sprint started (Removed)
  • Take a note of the search (filter) ID

Once I have these, the formula to find the Sprint's original planned items is:
Planned Items = Achieved Items + Removed Items - Added Items

SIDE NOTE, someone above mentioned would need to add up each search result item's Story points one by one to get a total.  No, you don't .  You can add an issueFunction that gives you the sum total story points for a the entire search result items.

The issueFunction for the sum of Story Points of a Search Result:
(issueFunction in aggregateExpression("Total Story Points", "storyPoints.sum()"))

Before you start writing the Queries, make sure you know the following names or their corresponding IDs (personally, I always tend to use IDs)

• Board Name or ID = ??
• Sprint Name or ID = ??

JIRA QUERIES

NOTE: Replace all the italic word placeholders in my queries with specific information from your project

Achieved:
issueFunction in aggregateExpression("Total Story Points", "storyPoints.sum()") AND Sprint = Enter Sprint ID Here

Again, Take a note of the search (filter) ID because you'll needed for the Planned query

Added:
issueFunction in aggregateExpression("Total Story Points", "storyPoints.sum()") AND  issueFunction in addedAfterSprintStart(Enter Board Name or ID Here, Enter Sprint Name or ID Here)

Again, Take a note of the search (filter) ID because you'll needed for the Planned query

Removed:
issueFunction in aggregateExpression("Total Story Points", "storyPoints.sum()") AND labels in (IC-CommHub) AND issueFunction in removedAfterSprintStart(Enter Board Name or ID Here, Enter Sprint Name or ID Here)

Again, Take a note of the search (filter) ID because you'll needed for the Planned query

Planned:
issueFunction in aggregateExpression("Total Story Points", "storyPoints.sum()") AND filter in (filterID for Achieved, filterID for Removed) AND filter not in (filterID for Added)

BY THE WAY - All this complication would NOT be needed if only JIRA had the issueFunction of "addedBeforeSprintStart".  JIRA has addedAfter and removedAfter, why not addedBefore?  JIRA... always makes me wonder :)

[Trudy Claspill]

Hello @Vix JIRA 

Welcome to the Atlassian Community!

Thank you for documenting this solution.

I would like to point out that the solution you propose requires the Adaptavist ScriptRunner app. The solution is not based on native Jira JQL functions which are documented here.

The answers that @Nic Brough -Adaptavist-  and I provided were specific to using only native JQL functionality.

[Vix JIRA]

@Trudy Claspill - Noted.  Then Adaptivist ScriptRunner App is a lifesaver!

@Nic Brough -Adaptavist- assuming you're from Adaptivist, PLEASE tell them to add a "addedBeforeSprintStart" issueFunction, but as I think out loud, even that won't solve the issue of capturing the items that were in the Sprint right at the start of the sprint, since items can move a lot during planning sessions and before the Sprint starts.

So maybe something like InSprintAtStart, capturing the items that were in the sprint at the moment (day+time) the Sprint was Started.

[Nic Brough -Adaptavist-]

Heh, my history is "started with Jira 2, joined Adaptavist as a consultant a decade later, and have been there almost another decade".  Scriptrunner became an Adaptavist product 18 months after I joined!

I think you're right about adding that sort of function - it really won't help answer the question any better than just looking at what was in the sprint when you start it.

Adding things to a sprint after it starts should be an exception, and it can be reported on.  The sprint reports already tell you what happened during the sprint, but I'm not sure why anyone really wants that much detail about it.  The point of a sprint is that it a timebox for the team.  The numbers themselves are not that important to the team, the important bits are the conversations at the end of the sprint about "why did we draw more stuff in", "why did we drop stuff out", and, of course, most importantly, "why didn't we deliver what we said we would?"

[Fermindoza, Gene]

In Jira Cloud, the Burn Up report for each Jira project should give you the history and list of tickets at sprint start, during the sprint (added, removed) and completed at the end of the sprint with story point summary at each change.   

However there are situations where I'd like to extract that data to an AirTable for example so I can have a visual of multiple Jira projects at a single glance.

Adaptive ScriptRunner in Jira Cloud is even more limiting - these functions are no longer available.  There is no way to manually query the above.   Atlassian and Adaptivist folks why is that??? 

issueFunction in addedAfterSprintStart(boardName, sprintName)

issueFunction in removedAfterSprintStart(boardName, sprintName)

issueFunction in completeInSprint(boardName, sprintName)

[Nic Brough -Adaptavist-]

@Fermindoza, Gene - those functions were not removed, they still work in Server/DC installs.  

For Cloud though, we could not write two of them - Atlassian's Cloud API does not (yet) let us.  addedAfterSprintStart is possible though, so we have, and you can use it enhanced search.

[Shiri Feldman]

@Nic Brough -Adaptavist- I tried to use the addedAfterSprintStart function in Jira Cloud and I received only tickets from the current sprint. Is it possible to pull out tickets whose associated sprints have already been closed? Thanks

[Nic Brough -Adaptavist-]

Only one sprint at a time:

issueFunction in addedAfterSprintStart("ABC Scrum Board", "Sprint 3")

[Shiri Feldman]

Thanks @Nic Brough -Adaptavist- . Any estimate on when it would feasible?

[Nic Brough -Adaptavist-]

You can already do it it.

[Gayathri B]

@Nic Brough -Adaptavist-  I am following this thread as I am also into finding ways of having a view of Start and completion of sprints, based on which we can gather insights of what was committed and what was delivered by the teams. But whenever I try to use functions starting with this - "issueFunction" I always get an error that : 

Field 'issueFunction' does not exist or you do not have permission to view it.

What should I enable in order to be able to use Jira cloud functions ? Pls help thanks

[Trudy Claspill]

@Gayathri B 

As mentioned inone of the replies, the issuefunction option is a feature of the Adaptavist ScriptRunner app. You have to acquire that app from that Atlassian Marketplace

https://marketplace.atlassian.com

[Isabella Musial]

@Trudy Claspill @Vix JIRA 

I am trying to solve this same exact problem. 

EAZY BI had the option of "at sprint start" but AddedBeforeSprint start would totally work as well. 

I figure, if EAZY BI can do it, Jira can do it. 

Please add this to JQL query options!!! Please! 

[Trudy Claspill]

@Isabella Musial 

I am just another user of the product. I can't add functionality to Jira.

[Isabella Musial]

oh ok! 

[Dan -minware-]

Disclaimer: marketplace partner!

Hi @dallu divya -

I came across your question this afternoon and wanted to reach out to introduce you to minware. In addition to simply displaying the metrics you are interested in our sprint summary:

We also calculate per-ticket velocity so that you can easily identify the biggest estimate misses to target the conversation during your sprint retrospective.

Here you can see a task that was estimated to take half a day but pushed almost to 3!:

We try to make things as simple as possible so anyone on your team could lead a data driven sprint retrospective.

Hope this helps and you find what you are looking for!

[dallu divya]

can you write query and give for what you explained . I will fill with my project details