Sprint Burndown Chart via Custom Metrics
- Aleksandr Bogush
This article describes how you can build a Sprint Burndown chart for your team
This custom metric requires Custom Selection. Inside this Custom Selection we see several stages of calculation resulting in the “simple” chart. Let’s go through these stages
- 1 Creating a Metric
- 2 Creating a Custom Selection
- 2.1 Field Number Mapping in Ticket History
- 2.2 Define what are the closed statuses
- 2.3 Get the Sprint you want to count the Burndown Chart for
- 2.4 Get Related Tickets
- 2.5 Get All History entries related to this sprint
- 2.6 Get Date Changes in Affected History
- 2.7 Bring history entries to the format “was → became”
- 2.8 Make sense of “was->became” format related to the Sprint in question
- 2.9 Specify the rules of addition/deletion of story points
- 2.10 Specify how each history entry affects the story points of its Ticket (the story points that get added to the scope of the sprint)
- 2.11 Combine the changes to Tickets into overall delta of the Sprint scope
- 2.12 Combine deltas into a cumulative scope size on each change
- 2.13 Generate hourly series of time points from start to finish of a sprint
- 2.14 Select the Scope Dynamics by these series
- 2.15 Full Code Recap
- 3 Visualising the Metric
- 4 Checking the looks
Creating a Metric
See Custom Metric development for details
Creating a Custom Selection
See Custom Metric development to see how to get to a Custom Selection mode.
Further will be the explanation of the code you should put in the “PerfQL” field
Field Number Mapping in Ticket History
field_number_mapping as (
select 'status' as field, (select field from TicketHistory where status is not null and length(status) > 0 limit 1) as num
union select 'story_points' as field, (select field from TicketHistory where story_points is not null limit 1) as num
union select 'sprint' as field, (select field from TicketHistory where sprint is not null and length(sprint) > 0 limit 1) as num
),This comes from a format TicketHistory is stored in. One of the columns describes the number of a field of a Ticket that was modified in that TicketHistory entry. Since these numbers may vary from project to project and from Jira Instance to Jira Instance, we need to find out what numbers correspond to the fields we are going to be interested in: Story Points, Sprint, Status
Result Example:
field | num |
|---|---|
story_points | 7 |
sprint | 1 |
status | 0 |
Then we save these numbers to “variables” (results of common table expressions) just for convenience:
status_field as (
select num from field_number_mapping where field = 'status' limit 1
),
sprint_field as (
select num from field_number_mapping where field = 'sprint' limit 1
),
story_points_field as (
select num from field_number_mapping where field = 'story_points' limit 1
),Define what are the closed statuses
This is where you customise thew metric to fit your project needs
closed_statuses as (
select a
from (
values ('Closed'), ('Verified')
) s(a)
),This step will be eliminated once the support of TTS Settings in Custom Metrics is introduced, which is pretty soon, but for now here you can define which statuses you count as “closed”
Get the Sprint you want to count the Burndown Chart for
This is where you customise thew metric to fit your project needs
Then just for convenience save the search expression for this sprint name
Get Related Tickets
Get the Tickets that are in any way related to the sprint we chose
Get All History entries related to this sprint
Get Date Changes in Affected History
Purely for convenience reasons
Bring history entries to the format “was → became”
Example of the output:
Make sense of “was->became” format related to the Sprint in question
Example of the output:
Specify the rules of addition/deletion of story points
The output:
Specify how each history entry affects the story points of its Ticket (the story points that get added to the scope of the sprint)
Example of the output:
Combine the changes to Tickets into overall delta of the Sprint scope
Example of the output:
Combine deltas into a cumulative scope size on each change
Example of the output:
On this stage we have everything we need to just visualise the line from ‘cumulative’, because that is the amount of story points in sprint scope on each scope change. All the further data manipulations just serve the purpose of better visualisation
Generate hourly series of time points from start to finish of a sprint
Example of the output:
Select the Scope Dynamics by these series
Example of the output:
Full Code Recap
Then you should click “Save” to get back to the Visualisation Screen
Visualising the Metric
After saving, add the metric to your dashboard. See Handle Delivery Metrics Dashboard Page | How to Handle a Card for details.
Checking the looks
Good job! That was a tough one! You really deserve a cup of something delicious, so go grab it to reward yourself!