Story points calculation in Sprint Velocity Report
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
There are cases when the story points in a Sprint report do not match the values shown in the Sprint Velocity Report. This article will explain how the points are calculated and provide SQL queries to look for issues that contribute to the total story point calculation.
Solution
The Sprint Velocity report will count all the story points added to the sprint even if the issues were in a resolved status (Done, Won't Do, Resolved, Closed, Duplicate etc...) as it has to show what was committed from the start, but the sprint report will ignore these as the actual work is not required as the issue is already in one of the resolved statuses.
Query1: Issues associated to sprint
Replace <SprintID> with the Sprint ID.
1
2
3
4
5
6
7
select p.pname, p.pkey, j.issuenum, r."NAME" as BoardName, s."NAME" as SprintName from customfieldvalue cv, customfield cf, jiraissue j, project p,"AO_60DB71_SPRINT" s ,"AO_60DB71_RAPIDVIEW" r where cf.cfname ='Sprint'
and j.project = p.id
and cv.customfield = cf.id
and cv.issue = j.id
and s."ID" = '<SprintID>'
and s."RAPID_VIEW_ID" = r."ID"
and cv.stringvalue = (select cast("ID" As varchar) from "AO_60DB71_SPRINT" where "ID" in (<SprintID>));Query 2: Issues in sprint at time of when sprint started
Replace the Sprint titles and dates.
1
2
3
4
5
6
7
8
9
select p.pkey||'-'||j.issuenum as issuekey, ci.field, ci.oldstring as previoussprint, ci.newstring as newsprint,
cg.created
from changeitem ci, changegroup cg, jiraissue j, project p
where cg.issueid = j.id
and ((ci.oldstring is null and newstring like '%SCRUM Sprint%') or newstring like '%SCRUM Sprint%')
and ci.groupid = cg.id
and ci.field = 'Sprint'
and j.project = p.id
and cg.created >= '<fromdate in YYYY-MM-DD format>' and cg.created <= '<todate in YYYY-MM-DD format>'Use the issue results in Query 2 to compare if the total story points of these issues match that of the Sprint Report.
If there is a discrepancy, check if there are any issues in the sprint that do not appear in Query 2.
One of the common reasons why the story points of issues are not included in the Sprint report is because they were put into a Resolved type of status before the Sprint was started.
The Velocity report will count all the story points added to the sprint even if the issues were in a Resolved status (Done, Won't Do, Resolved, Closed, Duplicate etc...) as it has to show what was committed from the start.
However, the sprint report will ignore these as the actual work is not included because the issue is already in one of the resolved statuses.
Was this helpful?