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

« Previous Version 15 Current »

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

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

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)*/
),

Get All History entries related to this sprint

affected_history as (
	-- get all history items related to the tickets related to sprint
  select th.workitem_id, th.start, th.field, th.status, th.sprint, th.story_points, th.start = t.created as on_creation 
	from affected_tickets t join TicketHistory th on th.workitem_id = t.id and th.field in (select num from field_number_mapping)
	
  union select t.id as workitem_id, t.created as start, (table status_field) as field, t.status, null as sprint, null as story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table status_field) where th.start is null 
	-- ^^ to substitute lack of history on ticket creation for status
  union select t.id as workitem_id, t.created as start, (table sprint_field) as field, null as status, array_to_string(t.sprints, ', ') as sprint, null as story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table sprint_field) where th.start is null
	-- ^^ to substitute lack of history on ticket creation for sprint
  union select t.id as workitem_id, t.created as start, (table story_points_field) as field, null as status, null as sprint, t.story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table story_points_field) where th.start is null
	-- ^^ to substitute lack of history on ticket creation for story_points
),

Get Date Changes in Affected History

Purely for convenience reasons

changes_dates as (
  select distinct workitem_id, start, on_creation from affected_history
	-- ^^ will need this further for grouping history entries by time (several changes may have been done at the same time but stored as different history items)
),

Bring history entries to the format “was → became”

became_extraction as (
	-- moving to format was -> became for 3 fields in focus. This is the 'became' part
  select *,
	(case when exists(select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field))
	      then coalesce((select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field) limit 1), 'No Sprint')
	      else null end) as became_sprint, -- if there are no history items where sprint was changed at this moment, there'll be null, otherwise the value of that 'same moment' sprint change
	(case when exists (select ah.status from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table status_field))
	      then coalesce ((select ah.status from affected_history ah where ah.field = (table status_field) and ah.start = cd.start and ah.workitem_id = cd.workitem_id limit 1), 'No Status')
	      else null end) -- if there are no history items where status was changed at this moment, there'll be null, otherwise the value of that 'same moment' status change
	as became_status,
	(case when exists(select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field))
	      then coalesce((select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field)limit 1), 'No SPs')
	      else null end) -- if there are no history items where story_points was changed at this moment, there'll be null, otherwise the value of that 'same moment' story_points change
	as became_story_points
	from changes_dates cd
),

was_extraction as (
	-- this is the 'was' part for corresponding 'became' entries
	select *,
	(select coalesce(ah.sprint, 'No Sprint') from affected_history ah where field = (table sprint_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_sprint,
	(select coalesce(ah.status, 'No Status') from affected_history ah where field = (table status_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_status,
	(select coalesce(ah.story_points::text, 'No SPs') from affected_history ah where field = (table story_points_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_story_points
	from changes_dates cd
	where on_creation = false -- because if the change was done after creation, jira creates 2 history items - one for the creation moment, one for change (donno why)
),

history_extraction as (
	-- here we combine 'was' and 'became' using join to get all the fields. select from this table to see results
  select be.workitem_id, be.start, be.on_creation, 
	coalesce(we.was_sprint, 'No Sprint') as was_sprint, coalesce(be.became_sprint, we.was_sprint) as became_sprint,
	coalesce(we.was_status, 'No Status') as was_status, coalesce(be.became_status, we.was_status) as became_status, 
	coalesce(we.was_story_points, 'No SPs') as was_story_points, coalesce(be.became_story_points, we.was_story_points) as became_story_points
  from became_extraction be
	left join was_extraction we 
	on be.workitem_id = we.workitem_id and be.start = we.start
),

Example of the output:

Make sense of “was->became” format related to the Sprint in question

meaningful_history_extraction as (
	-- here we apply criterias to fields so that they make more sense to us when making decision on whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
  select he.workitem_id, he.start, he.on_creation, 
	he.was_sprint like (table last_sprint_like) as was_my_sprint,
	he.became_sprint like (table last_sprint_like) as became_my_sprint,
	he.was_status in (table closed_statuses) as was_closed,
	he.became_status in (table closed_statuses) as became_closed,
	(case when he.was_story_points = 'No SPs' or he.was_story_points is null then 0 else he.was_story_points::numeric end) as was_story_points,
	(case when he.became_story_points = 'No SPs' or he.became_story_points is null then 0 else he.became_story_points::numeric end) as became_story_points
	from history_extraction he
),

Example of the output:

Specify the rules of addition/deletion of story points

decision_map as (
	-- here we set the rules by which we decide whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
	select *
	from (
		-- was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier
		values (false, false, false, false, 0, 0),
		        (false, false, false, true, 0, 0),
		        (false, false, true, false, 0, 0),
		        (false, false, true, true,  0, 0),
		        (false, true, false, false, 0, 1),
		        (false, true, false, true,  0, 0),
		        (false, true, true, false, 0, 1),
		        (false, true, true, true, 0, 0),
		        (true, false, false, false, -1, 0),
		        (true, false, false, true, -1, 0),
		        (true, false, true, false, 0, 0),
		        (true, false, true, true, 0, 0),
		        (true, true, false, false, -1, 1),
		        (true, true, false, true, -1, 0),
		        (true, true, true, false, 0, 1),
		        (true, true, true, true, 0, 0)	
	) s(was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier)

),

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)

operations as (
	-- here we join what we got with the decision map to get actual additions and deletions
  select he.*, 
	  he.was_story_points * dm.was_story_points_multiplier as was_sp_operand,
	  he.became_story_points * dm.became_story_points_multiplier as became_sp_operand 
	from meaningful_history_extraction he
	join decision_map dm on 
	  he.was_my_sprint = dm.was_my_sprint
	  and he.became_my_sprint = dm.became_my_sprint
	  and he.was_closed = dm.was_closed
	  and he.became_closed = dm.became_closed
	  and (he.was_story_points <> 0 or he.became_story_points <> 0) -- always good to filter out what doesn't affect the scope
	order by start
),

Example of the output:

Combine the changes to Tickets into overall delta of the Sprint scope

totaladditions as (
	-- here we combine the operands based on was_sps and became_sps into one delta - the change of sprint scope
  select was_sp_operand + became_sp_operand as delta, * from operations where was_sp_operand <> 0 or became_sp_operand <> 0 order by start -- always good to filter out what doesn't affect the scope
),

Example of the output:

Combine deltas into a cumulative scope size on each change

cumulative as (
	-- here we sum up all changes to get the sprint scope size on each event
  select sum(delta) over (order by start) as cumulative, * from totaladditions order by start
),

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

generated_sprint_border_series as (
	select date_trunc('hour', dd) dd from generate_series((select start_date from last_sprint limit 1)::timestamp, 
								  (select finish_date from last_sprint limit 1)::timestamp, '1 hour'::interval) dd
)

Example of the output:

Select the Scope Dynamics by these series

select to_char(dd, 'MM/dd HH:MI Dy') as "Time", (
  select cumulative from cumulative where start < dd and dd < now()
  order by start desc limit 1) as "Story Points"
from generated_sprint_border_series

We format the date to string in a certain way to specify the Days of Week as it is important for reading the Burndown Chart

Example of the output:

Full Code Recap

 See the full code
with 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
),

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
),

closed_statuses as (
	select a
	from (
		values ('Closed'), ('Verified')
	) s(a)
),

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),
  
last_sprint_like as (
  select concat('%', (select id from last_sprint), '%')
),
  
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 (includint the case when it was added to sprint on ticket creation, which is not usually mentioned in history)
),

affected_history as (
	-- get all history items related to the tickets related to sprint
  select th.workitem_id, th.start, th.field, th.status, th.sprint, th.story_points, th.start = t.created as on_creation 
	from affected_tickets t join TicketHistory th on th.workitem_id = t.id and th.field in (select num from field_number_mapping)
	
  union select t.id as workitem_id, t.created as start, (table status_field) as field, t.status, null as sprint, null as story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table status_field) where th.start is null 
	-- ^^ to substitute lack of history on ticket creation for status
  union select t.id as workitem_id, t.created as start, (table sprint_field) as field, null as status, array_to_string(t.sprints, ', ') as sprint, null as story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table sprint_field) where th.start is null
	-- ^^ to substitute lack of history on ticket creation for sprint
  union select t.id as workitem_id, t.created as start, (table story_points_field) as field, null as status, null as sprint, t.story_points, true as on_creation
	from affected_tickets t left join TicketHistory th on th.workitem_id = t.id and th.field = (table story_points_field) where th.start is null
	-- ^^ to substitute lack of history on ticket creation for story_points
),

changes_dates as (
  select distinct workitem_id, start, on_creation from affected_history
	-- ^^ will need this further for grouping history entries by time (several changes may have been done at the same time but stored as different history items)
),

became_extraction as (
	-- moving to format was -> became for 3 fields in focus. This is the 'became' part
  select *,
	(case when exists(select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field))
	      then coalesce((select ah.sprint from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table sprint_field) limit 1), 'No Sprint')
	      else null end) as became_sprint, -- if there are no history items where sprint was changed at this moment, there'll be null, otherwise the value of that 'same moment' sprint change
	(case when exists (select ah.status from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and ah.field = (table status_field))
	      then coalesce ((select ah.status from affected_history ah where ah.field = (table status_field) and ah.start = cd.start and ah.workitem_id = cd.workitem_id limit 1), 'No Status')
	      else null end) -- if there are no history items where status was changed at this moment, there'll be null, otherwise the value of that 'same moment' status change
	as became_status,
	(case when exists(select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field))
	      then coalesce((select ah.story_points::text from affected_history ah where ah.start = cd.start and ah.workitem_id = cd.workitem_id and field = (table story_points_field)limit 1), 'No SPs')
	      else null end) -- if there are no history items where story_points was changed at this moment, there'll be null, otherwise the value of that 'same moment' story_points change
	as became_story_points
	from changes_dates cd
),

was_extraction as (
	-- this is the 'was' part for corresponding 'became' entries
	select *,
	(select coalesce(ah.sprint, 'No Sprint') from affected_history ah where field = (table sprint_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_sprint,
	(select coalesce(ah.status, 'No Status') from affected_history ah where field = (table status_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_status,
	(select coalesce(ah.story_points::text, 'No SPs') from affected_history ah where field = (table story_points_field) and ah.start < cd.start and ah.workitem_id = cd.workitem_id order by ah.start desc limit 1) as was_story_points
	from changes_dates cd
	where on_creation = false -- because if the change was done after creation, jira creates 2 history items - one for the creation moment, one for change (donno why)
),

history_extraction as (
	-- here we combine 'was' and 'became' using join to get all the fields. select from this table to see results
  select be.workitem_id, be.start, be.on_creation, 
	coalesce(we.was_sprint, 'No Sprint') as was_sprint, coalesce(be.became_sprint, we.was_sprint) as became_sprint,
	coalesce(we.was_status, 'No Status') as was_status, coalesce(be.became_status, we.was_status) as became_status, 
	coalesce(we.was_story_points, 'No SPs') as was_story_points, coalesce(be.became_story_points, we.was_story_points) as became_story_points
  from became_extraction be
	left join was_extraction we 
	on be.workitem_id = we.workitem_id and be.start = we.start
),

meaningful_history_extraction as (
	-- here we apply criterias to fields so that they make more sense to us when making decision on whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
  select he.workitem_id, he.start, he.on_creation, 
	he.was_sprint like (table last_sprint_like) as was_my_sprint,
	he.became_sprint like (table last_sprint_like) as became_my_sprint,
	he.was_status in (table closed_statuses) as was_closed,
	he.became_status in (table closed_statuses) as became_closed,
	(case when he.was_story_points = 'No SPs' or he.was_story_points is null then 0 else he.was_story_points::numeric end) as was_story_points,
	(case when he.became_story_points = 'No SPs' or he.became_story_points is null then 0 else he.became_story_points::numeric end) as became_story_points
	from history_extraction he
),

decision_map as (
	-- here we set the rules by which we decide whether add or subtract story points from sprint scope based on what happened with the ticket at that moment
	select *
	from (
		-- was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier
		values (false, false, false, false, 0, 0),
		        (false, false, false, true, 0, 0),
		        (false, false, true, false, 0, 0),
		        (false, false, true, true,  0, 0),
		        (false, true, false, false, 0, 1),
		        (false, true, false, true,  0, 0),
		        (false, true, true, false, 0, 1),
		        (false, true, true, true, 0, 0),
		        (true, false, false, false, -1, 0),
		        (true, false, false, true, -1, 0),
		        (true, false, true, false, 0, 0),
		        (true, false, true, true, 0, 0),
		        (true, true, false, false, -1, 1),
		        (true, true, false, true, -1, 0),
		        (true, true, true, false, 0, 1),
		        (true, true, true, true, 0, 0)	
	) s(was_my_sprint, became_my_sprint, was_closed, became_closed, was_story_points_multiplier, became_story_points_multiplier)

),

operations as (
	-- here we join what we got with the decision map to get actual additions and deletions
  select he.*, 
	  he.was_story_points * dm.was_story_points_multiplier as was_sp_operand,
	  he.became_story_points * dm.became_story_points_multiplier as became_sp_operand 
	from meaningful_history_extraction he
	join decision_map dm on 
	  he.was_my_sprint = dm.was_my_sprint
	  and he.became_my_sprint = dm.became_my_sprint
	  and he.was_closed = dm.was_closed
	  and he.became_closed = dm.became_closed
	  and (he.was_story_points <> 0 or he.became_story_points <> 0) -- always good to filter out what doesn't affect the scope
	order by start
), 

totaladditions as (
	-- here we combine the operands based on was_sps and became_sps into one delta - the change of sprint scope
  select was_sp_operand + became_sp_operand as delta, * from operations where was_sp_operand <> 0 or became_sp_operand <> 0 order by start -- always good to filter out what doesn't affect the scope
),

cumulative as (
	-- here we sum up all changes to get the sprint scope size on each event
  select sum(delta) over (order by start) as cumulative, * from totaladditions order by start
),

generated_sprint_border_series as (
	select date_trunc('hour', dd) dd from generate_series((select start_date from last_sprint limit 1)::timestamp, 
								  (select finish_date from last_sprint limit 1)::timestamp, '1 hour'::interval) dd
)
select to_char(dd, 'MM/dd HH:MI Dy') as "Time", (
  select cumulative from cumulative where start < dd and dd < now()
  order by start desc limit 1) as "Story Points"
from generated_sprint_border_series

Then you should click “Save” to get back to the Visualisation Screen

Visualising the Metric

After saving, add the metric to your dashboard. See https://telescope-ai.atlassian.net/wiki/spaces/EPMDMO/pages/1056771045/Handle+Layout+of+Unit+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!

Copyright © 2024 EPAM Systems, Inc.

All Rights Reserved. All information contained herein is, and remains the property of EPAM Systems, Inc. and/or its suppliers and is protected by international intellectual property law. Dissemination of this information or reproduction of this material is strictly forbidden, unless prior written permission is obtained from EPAM Systems, Inc.

  • No labels