1. Aggregating Issues by Assignee


2. Evaluating Calculated Planned and Spent Time Per Assignee


3. Visualizing of the Amount of Planned and Spent Time Within the Project


4. Visualizing the Issues Distribution by Sprints or Releases


5. Comparison of Graphs of Open and Resolved Tasks


6. Visualizing Story Points Performance


7. Calculating Velocity


8. Visualizing of Completed Story Points by Teams


9. Building a Burnup Chart


10. Building a Gantt Chart


11. Creating a Dashboard Based on One Table





Before you start

  1. Before you can use the Jira Issues macro, your Confluence and Jira application must be connected via Application Links. People viewing the page will see the publicly accessible issues from the Jira site. If your Jira site has restricted viewing (that is, people need permission to view issues) then they will need to authenticate before seeing the restricted issues.
  2. The Table Filter and Charts app processes the issues displayed on a page. If you want to filter, aggregate and visualize all the issues, you need to set the maximal number of issues to display in the Jira Issues macro settings.
  3. In all the use cases below, we use JQL: 'project = name' and apply filtration with the Table Filter macro. You can filter issues using JQL.
  4. When you work with date values and worklogs you need to be sure that the date format and worklog settings are correct.


Aggregating Issues by Assignee

Macro combination:

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

Macro configuration:

Table Filter:

Filter ColumnStatus
Filter TypeDropdown
Filter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssignee
Column LabelsStatus
Calculated ColumnKey
Operation TypeCount, Literal

Additional parameters:

  • Set the Literal value delimiter option: Bulleted list.
  • Hide totals of the pivot table.





Evaluating Calculated Planned and Spent Time Per Assignee

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Add a table with a single column 'Time Evaluation' and containing the Status macros. (info)
  5. Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.

If you don't need to have colorful statuses in the Time Evaluation column, don't add this single column table.


Macro configuration:

Table Filter:

Filter ColumnStatus
Filter TypeDropdown
Filter ValuesDone

Pivot Table:

Row LabelsAssignee
Column Labels-
Calculated ColumnOriginal Estimate, Time Spent
Operation TypeSum

Table Transformer:

Use the following SQL query:

SELECT *,
CASE WHEN 'Sum of Time Spent' > 'Sum of Original Estimate' 
THEN "OVERDUE" 
ELSE "IN TIME" END AS 'Time Evaluation'
FROM T1






Visualizing of the Amount of Planned and Spent Time Within the Project

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro in the Chart from Table macro.

Macro configuration:

Table Filter:

Filter ColumnStatus
Filter TypeDropdown
Filter ValuesDone

Pivot Table:

Row LabelsT
Column Labels-
Calculated ColumnOriginal Estimate, Time Spent
Operation TypeSum

Chart from Table:

TypeBar
Labels ColumnT
Values ColumnSum of Original Estimate, Sum of Time Spent






Visualizing the Issues Distribution by Sprints or Releases

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro in the Chart from Table macro.

Macro configuration:

Table Filter:

Filter ColumnStatus
Filter TypeDropdown
Filter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssignee
Column LabelsStatus
Calculated ColumnKey
Operation TypeCount, Literal

Chart from Table:

TypeStacked column
Labels ColumnFix Version(s)
Values ColumnCount Improvement, Count New Feature

Additional parameters:

  • Set the Show data labels option: Percentage.





Comparison of Graphs of Open and Resolved Tasks

Macro combination (the chart is based on two transformed pivot tables):

Step 1. Configure Table1:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira issues macro in the Pivot Table macro.
  3. Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.

Step 2. Configure Table2:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.

Step 3. Wrap Table1 and Table2 in the Chart from Table macro.

Macro configuration:

Step 1. Configure Table1:

Pivot Table:

Row LabelsCreated
Column Labels-
Calculated ColumnSummary
Operation TypeCount

Additional parameters:

  • Set the Date period aggregation option: Week.

Table Transformer:

Use the following SQL query:

SELECT 'Created', SUM (TT2.'Count') AS 'Count'
FROM T1 AS TT1 
JOIN T1 AS TT2 on TT1.'Created' >= TT2.'Created'
GROUP BY TT1.'Created'
ORDER BY TT1.'Created'





Step 2. Configure Table2:

Table Filter:

Filter ColumnResolved
Filter TypeDropdown
Filter ValuesReversed filter '-empty-'

Pivot Table:

Row LabelsResolved
Column Labels-
Calculated ColumnSummary
Operation TypeCount

Additional parameters:

  • Set the Date period aggregation option: Week.

Table Transformer:

Use the following SQL query:

SELECT 'Resolved', SUM (TT2.'Count') AS 'Count'
FROM T1 AS TT1 
JOIN T1 AS TT2 on TT1.'Resolved' >= TT2.'Resolved'
GROUP BY TT1.'Resolved'
ORDER BY TT1.'Resolved'




Step 3. Wrap Table1 and Table2 in the Chart from Table macro.

Chart from Table:

TypeTime Area
Dates ColumnCreated, Resolved
Values ColumnCount




Visualizing Story Points Performance

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro in the Chart from Table macro.

Macro configuration:

Table Filter:

Filter ColumnTSprint
Filter TypeVisualDropdown
Filter ValuesImprovement, New FeatureReversed filter: '-empty-'

Pivot Table:

Row LabelsSprint
Column LabelsStatus
Calculated ColumnStory points
Operation TypeSum

Chart from Table:

TypeColumn
Labels ColumnSprint
Values ColumnSum of story points Done, Sum of story points In Progress, Sum of story points To Do

Additional parameters:

  • Set the Show trendline option.






Calculating Velocity

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.
  5. Wrap the Table Transformer macro in the Chart from Table macro.

Macro configuration:

Table Filter:

Filter ColumnSprint
Filter TypeDropdown
Filter ValuesReversed filter '-empty-'

Pivot Table:

Row LabelsSprint
Column LabelsStatus
Calculated ColumnStory points
Operation TypeSum

Table Transformer:

Use the following SQL query:

SELECT 'sprint', 
MIN(TT1.'Sum of story points') AS 'Story Points Committed',
MIN(TT1.'Sum of story points Done') AS 'Story Points Completed',
AVG(TT2.'Sum of story points Done') AS 'Velocity'
FROM T1 AS TT1 
JOIN T1 AS TT2 on TT1.'sprint' >= TT2.'sprint'
WHERE TT1.'sprint'<>"Total"
GROUP BY TT1.'sprint'
ORDER BY TT1.'sprint'

Additional parameters:

  • Set the Decimal places option: 1.

Chart from Table:

TypeColumn
Labels ColumnStory Points Committed
Values ColumnStory Points Completed






Visualizing of Completed Story Points by Teams

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Add a table containing additional information for merging. (info)
  4. Wrap the Table Filter macro and the table containing additional information in the Table Transformer macro.
  5. Wrap the Table Transformer macro in the Pivot Table macro.
  6. Wrap the Pivot Table macro in the Chart from Table macro.

You can include any table from your Confluence instance using the Table Excerpt and Table Excerpt Include macros.


Macro configuration:

Table Filter:

Filter ColumnStatusSprint
Filter TypeDropdownDropdown
Filter ValuesDoneReversed filter: '-empty-'

Table Transformer:

Select the default Lookup tables preset and a column for matching the records. (info)

If the columns for matching have different labels in multiple tables, use the query from the example.

Pivot Table:

Row LabelsSprint
Column LabelsTeam
Calculated ColumnStory points
Operation TypeSum

Chart from Table:

TypeColumn
Labels ColumnSprint
Values ColumnSum of story points Team 1, Sum of story points Team 2, Sum of story points Team 3







Building a Burnup Chart

Macro combination (the chart is based on two transformed pivot tables):

Step 1. Configure Table1:

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

Step 2. Configure Table2:

  1. Insert the Jira Issues macro (or use one Jira issues macro for both tables with the help of the Table Excerpt and Table Excerpt Include macros).
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Place the Pivot Table macro in a cell of the manually created table containing start and end dates of a sprint.

Step 3. Wrap Table1 and Table2 in the Chart from Table macro.

Macro configuration:

Step 1. Configure Table1:

Table Filter:

Filter ColumnStatusSprint
Filter TypeDropdownDropdown
Filter ValuesDoneECS Sprint 3

Pivot Table:

Row LabelsResolved
Column Labels-
Calculated ColumnStory points
Operation TypeSum

Table Transformer:

Use the following SQL query:

SELECT 'Resolved', SUM (TT2.'Sum of story points') AS 'Actual'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Resolved' >= TT2.'Resolved'
GROUP BY TT1.'Resolved'
ORDER BY TT1.'Resolved'




Step 2. Configure Table2:

Table Filter:

Filter ColumnSprint
Filter TypeDropdown
Filter ValuesECS Sprint 3

Pivot Table:

Row Labels-
Column Labels-
Calculated ColumnStory points
Operation TypeSum


Step 3. Wrap Table1 and Table2 in the Chart from Table macro.

Chart from Table:

TypeTime Line
Dates ColumnResolved
Values ColumnIdeal, Actual





Building a Gantt Chart

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Table Transformer macro. (info)
  4. Add a table containing project milestones. tinymce.emotions_dlg.information
  5. Wrap the Table Filter macro and the table containing milestones in the Table Transformer macro.
  6. Wrap the Table Transformer macro and the table in the Chart from Table macro.

You can include any table from your Confluence instance using the Table Excerpt and Table Excerpt Include macros or use any default and custom columns of the Jira Issues macro.


We insert the Table Transformer macro in order to calculate the Completion ratio based on the Jira Issues macro columns. If you have any other column containing percentage values or don't need to visualize progress bars in the chart, you can skip this step.


Macro configuration:

Table Filter:

Filter ColumnIssue typeSprint
Filter TypeVisualDropdown
Filter ValuesNew feature, ImprovementECS Sprint 2, ECS Sprint 3, ECS Sprint 4

Table Transformer:

Use the following SQL query:

SELECT *,
CASE WHEN 'Status' = "Done" 
THEN "100%" 
WHEN 'Status' = "To Do" 
THEN "0%" 
ELSE (100 - ('Remaining Estimate' / 'Time Spent' * 100) + "%") 
END AS 'Completion Ratio'
FROM T*

Chart from Table:

TypeGantt
Labels ColumnSprint, Key, Milestone
Values ColumnCreated, Due, Completion ratio, Date






Creating a Dashboard Based on One Table

You can create a dashboard with multiple charts and pivot tables based on one Jira Issues macro with the helsp of the Table Excerpt and Table Excerpt Include macros.

The following options are useful for better viewing:

  • Hide filtration panel (Tabel Filter)
  • Hide control panel (Pivot Table)
  • Hide chart controls (Chart from Table)
  • Show/hide source table (in Table filter, Pivot Table, Chart from Table)
  • Set chart dimensions
  • Align a chart
  • Chart legend position
  • Set colors and line settings




Create a user macro to hide the unnecessary information from the Jira Issues macro:



## @noparams


<style>

.refresh-issues-bottom { display: none}

</style>