Get our free guides to start using Jira and Confluence Cloud like a pro.
Skip to end of metadata
Go to start of metadata

The Source Table:

The Result:

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Transformer macro.
  3. Wrap the Table Transfromer macro in the Pivot Table macro.

              



Macro configuration:

Table Transformer:

Use the following SQL query:

SELECT *,
'sprint'->split("SCRUM Sprint ")->reverse()->0 AS 'sprint' 
FROM T1 WHERE T1.'Status' = "Done" AND 'sprint'->split("SCRUM Sprint ")->length > 2
UNION ALL CORRESPONDING 
SELECT *, "To Do" AS 'Status',
'sprint'->split("SCRUM Sprint ")->slice(1,-1)->toString() AS 'sprint' 
FROM T1 WHERE T1.'Status' = "Done" AND 'sprint'->split("SCRUM Sprint ")->length > 2
UNION ALL CORRESPONDING 
SELECT *,
'sprint'->split("SCRUM Sprint ")->toString() AS 'sprint' 
FROM T1 WHERE T1.'Status' <> "Done" OR 'sprint'->split("SCRUM Sprint ")->length < 3

Pivot Table:

Row Labelssprint
Column LabelsStatus
Calculated Columnstory points
Operation TypeSum

Additional parameter: