Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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

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

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

last_sprint as ( -- get the last started sprint
  select * from Sprint where start_date is not null and state = 'ACTIVE' order by start_date desc limit 1),

Then just for convenience save the search expression for this sprint name

last_sprint_like as (
  select concat('%', (select id from last_sprint), '%')
),

Get Related Tickets

Get the Tickets that are in any way related to the sprint we chose

affected_tickets as ( -- get all tickets somehow related to this sprint
  select distinct(t.*)
  from Ticket t 
  join TicketHistory th on th.workitem_id = t.id 
  where th.sprint like concat('%', (select id from last_sprint),'%') -- Ticket was ever added to sprint
    or (select id from last_sprint) = any(t.sprints) /* Ticket is in sprint right now (including the case when it was added 
    to sprint on ticket creation, which is not usually mentioned in Jira history)*/
),
  • No labels