(OUTDATED) Table Filter and Charts for Confluence

Space shortcuts

Page tree

Versions Compared

Old Version 2

changes.mady.by.user Natalie Paramonova

Saved on

compared with

New Version Current

changes.mady.by.user Natalie Paramonova

Saved on

Key

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



Mybanner

This version of the app's documentation is outdated. Please find the information you're looking for here:



Handy Cards
viewfixed
text-sourceexcerpt
image-sourcefirst
content-typeall
sortdate
text-length0
typechild-pages




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.





Excerpt
hiddentrue


Panel
titleFind more use cases:

Table of Contents
maxLevel2

Before you start

configure link

display all issues

no more 1000 (Jira limitations)

in all the use cases below, we use JQL: 'project = name' and apply filtration with the TF macro

remember about date format and worklog settings

use one Jira issues table to create multiple pivot tables and charts

hide Jira user macro

настройки языка

Aggregating Issues by Assignee

Macro combination:

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

Image Removed

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Additional parameters:

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

Image RemovedImage RemovedImage RemovedImage Removed

Image Removed

Evaluating Calculated Planned and Spent Time Per Assignee

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira 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.
Handy Tip
id7045469702232363

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

Image Removed

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesDone

Pivot Table:

Row LabelsAssigneeColumn Labels-Calculated ColumnOriginal Estimate, Time SpentOperation TypeSum

Table Transformer:

Use the following SQL query:

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

Image RemovedImage Removed

Image Removed

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

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira 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.

Image Removed

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesDone

Pivot Table:

Row LabelsTColumn Labels-Calculated ColumnOriginal Estimate, Time SpentOperation TypeSum

Chart from Table:

TypeBarLabels ColumnTValues ColumnSum of Original Estimate, Sum of Time Spent
Handy Carousel

Image RemovedImage Removed

Visualizing the Issues Distribution by Sprints or Releases

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira 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.

Image Removed

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Chart from Table:

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

Additional parameters:

  • Set the Show data labels option: Percentage.
Handy Carousel

Image Removed

Image RemovedImage Removed

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 Table Filter 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 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.

Image Removed

Macro configuration:

Step 1. Configure Table1:

Pivot Table:

Row LabelsCreatedColumn Labels-Calculated ColumnSummaryOperation TypeCount

Additional parameters:

  • Set the Date period aggregation option: Week.

Table Transformer:

Use the following SQL query:

Code Block
languagesql
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'
Handy Carousel
Image Removed

Image Removed

Image Removed

Step 2. Configure Table2:

Table Filter:

Filter ColumnResolvedFilter TypeDropdownFilter ValuesReversed filter '-empty-'

Pivot Table:

Row LabelsResolvedColumn Labels-Calculated ColumnSummaryOperation TypeCount

Additional parameters:

  • Set the Date period aggregation option: Week.

Table Transformer:

Use the following SQL query:

Code Block
languagesql
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'
Handy Carousel

Image RemovedImage RemovedImage Removed

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

Chart from Table:

TypeTime AreaDates ColumnCreated, ResolvedValues ColumnCount

Image Removed

Visualizing the Story Points Performance

Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira 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.

Image Removed

Macro configuration:

Table Filter:

Filter ColumnTSprintFilter TypeVisualDropdownFilter ValuesImprovement, New FeatureReversed filter '-empty-'

Pivot Table:

Row LabelsSprintColumn LabelsStatusCalculated ColumnStory pointsOperation TypeSum

Chart from Table:

TypeColumnLabels ColumnSprintValues ColumnSum of story points Done, Sum of story points In Progress, Sum of story points To Do

Additional parameters:

  • Set the Show trendline option.
Handy Carousel

Image RemovedImage Removed

Calculating Velocity

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Chart from Table:

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

Additional parameters:

  • Set the Show data labels option: Percentage.

Visualizing of Completed Story Points by Teams

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Chart from Table:

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

Additional parameters:

  • Set the Show data labels option: Percentage.

Burnup Chart

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Chart from Table:

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

Additional parameters:

  • Set the Show data labels option: Percentage.

Building a Gantt Chart

Macro configuration:

Table Filter:

Filter ColumnStatusFilter TypeDropdownFilter ValuesReversed filter 'Done'

Pivot Table:

Row LabelsAssigneeColumn LabelsStatusCalculated ColumnKeyOperation TypeCount, Literal

Chart from Table:

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

Additional parameters:

Set the Show data labels option: Percentage.