Versions Compared

Version Old Version 7 New Version Current
Changes made by

Aleksandr Bogush

Aleksandr Bogush

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Table of Contents

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

Code Block
breakoutModefull-width
languagesql
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
),

...

Code Block
breakoutModefull-width
languagesql
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

Info

This is where you customise thew metric to fit your project needs

Code Block
breakoutModefull-width
languagesql
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

Info

This is where you customise thew metric to fit your project needs

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

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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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”

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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, 0-1, 01),
		        (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)

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
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
),

...

Info

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

Code Block
breakoutModefull-width
languagesql
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

Code Block
breakoutModefull-width
languagesql
select to_char(dd, 'MM/dd/MM HH:MI DayDy') 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
Info

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

Expand
titleSee the full code
Code Block
languagesql
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 =in ('ACTIVE', 'CLOSED') 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, 0-1, 01),
		        (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/MM HH:MI DayDy') 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!

Insert excerpt
Integrations
Integrations
nameCopyright
nopaneltrue