(OUTDATED) Table Filter and Charts for Confluence

Space shortcuts

Page tree

Versions Compared

Old Version 8

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-sourcebeginningexcerpt
image-sourcefirst
content-typeall
sortdate
typecustom
Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-AggregatingIssuesbyAssignee

1. Aggregating Issues by Assignee

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-EvaluatingCalculatedPlannedandSpentTimePerAssignee

2. Evaluating Calculated Planned and Spent Time Per Assignee

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingoftheAmountofPlannedandSpentTimeWithintheProject

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

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingtheIssuesDistributionbySprintsorReleases

4. Visualizing the Issues Distribution by Sprints or Releases

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-ComparisonofGraphsofOpenandResolvedTasks

5. Comparison of Graphs of Open and Resolved Tasks

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingStoryPointsPerformance

6. Visualizing Story Points Performance

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-CalculatingVelocity

7. Calculating Velocity

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingofCompletedStoryPointsbyTeams

8. Visualizing of Completed Story Points by Teams

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-BuildingaBurnupChart

9. Building a Burnup Chart

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-BuildingaGanttChart

10. Building a Gantt Chart

Image Removed

Handy Cards Content
linkhttps://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-CreatingaDashboardBasedonOneTable

11. Creating a Dashboard Based on One Table

Image Removed

text-length0
typechild-pages




Before you start

  1. Before you can use this 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 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 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 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 CarouselImage 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 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 RemovedImage Removed

Calculating Velocity

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 and the table containing statuses in the Table Transformer macro.
  5. Wrap the Table Transformer macro in the Chart from Table macro.

Image Removed

Macro configuration:

Table Filter:

Filter ColumnSprintFilter TypeDropdownFilter ValuesReversed filter '-empty-'

Pivot Table:

Row LabelsSprintColumn LabelsStatusCalculated ColumnStory pointsOperation TypeSum

Table Transformer:

Use the following SQL query:

Code Block
languagesql
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:

TypeColumnLabels ColumnStory Points CommittedValues ColumnStory Points Completed
Handy Carousel

Image RemovedImage RemovedImage Removed

Image Removed

Image Removed

Visualizing of Completed Story Points by Teams

Macro combination:

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

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

Image Removed

Macro configuration:

Table Filter:

Filter ColumnStatusSprintFilter TypeDropdownDropdownFilter ValuesDoneReversed filter: '-empty-'

Table Transformer:

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

Handy Tip
id16714769186053924

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

Pivot Table:

Row LabelsSprintColumn LabelsTeamCalculated ColumnStory pointsOperation TypeSum

Chart from Table:

TypeColumnLabels ColumnSprintValues ColumnSum of story points Team 1, Sum of story points Team 2, Sum of story points Team 3 Handy Carousel

Image RemovedImage RemovedImage Removed

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

Image Removed

Macro configuration:

Step 1. Configure Table1:

Table Filter:

Filter ColumnStatusSprintFilter TypeDropdownDropdownFilter ValuesDoneECS Sprint 3

Pivot Table:

Row LabelsResolvedColumn Labels-Calculated ColumnStory pointsOperation TypeSum

Table Transformer:

Use the following SQL query:

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

Image RemovedImage Removed

Step 2. Configure Table2:

Table Filter:

Filter ColumnSprintFilter TypeDropdownFilter ValuesECS Sprint 3

Pivot Table:

Row Labels-Column Labels-Calculated ColumnStory pointsOperation TypeSum

Image Removed

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

Chart from Table:

TypeTime LineDates ColumnResolvedValues ColumnIdeal, Actual

Image Removed

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.
Handy Tip
id3565172410870956

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.

Handy Tip
id6985709991800795

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.





Excerpt
hiddentrue


Panel
titleFind more use cases:

Table of Contents
maxLevel2

Image Removed

Macro configuration:

Table Filter:

Filter ColumnIssue typeSprintFilter TypeVisualDropdownFilter ValuesNew feature, ImprovementECS Sprint 2, ECS Sprint 3, ECS Sprint 4

Table Transformer:

Use the following SQL query:

Code Block
languagesql
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:

TypeGanttLabels ColumnSprint, Key, MilestoneValues ColumnCreated, Due, Completion ratio, Date
Handy Carousel

Image Removed

Image Removed

Creating a Dashboard Based on One Table

Image Removed

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.

Image Removed

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
Handy Carousel

Image RemovedImage RemovedImage RemovedImage RemovedImage RemovedImage RemovedImage RemovedImage Removed

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

Image Removed

Code Block
languagexml
## @noparams <style> .refresh-issues-bottom { display: none} </style>