Handy Cards |
---|
view | fixed |
---|
text-source | beginningexcerpt |
---|
image-source | first |
---|
content-type | all |
---|
sort | date |
---|
type | custom |
---|
| Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-AggregatingIssuesbyAssignee |
---|
|
1. Aggregating Issues by AssigneeImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-EvaluatingCalculatedPlannedandSpentTimePerAssignee |
---|
|
2. Evaluating Calculated Planned and Spent Time Per AssigneeImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingoftheAmountofPlannedandSpentTimeWithintheProject |
---|
|
3. Visualizing of the Amount of Planned and Spent Time Within the ProjectImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingtheIssuesDistributionbySprintsorReleases |
---|
|
4. Visualizing the Issues Distribution by Sprints or ReleasesImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-ComparisonofGraphsofOpenandResolvedTasks |
---|
|
5. Comparison of Graphs of Open and Resolved TasksImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingStoryPointsPerformance |
---|
|
6. Visualizing Story Points PerformanceImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-CalculatingVelocity |
---|
|
7. Calculating VelocityImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-VisualizingofCompletedStoryPointsbyTeams |
---|
|
8. Visualizing of Completed Story Points by TeamsImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-BuildingaBurnupChart |
---|
|
9. Building a Burnup ChartImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-BuildingaGanttChart |
---|
|
10. Building a Gantt ChartImage Removed |
Handy Cards Content |
---|
link | https://docs.stiltsoft.com/pages/viewpage.action?pageId=37356366#UsecasesJiramacro-CreatingaDashboardBasedonOneTable |
---|
|
11. Creating a Dashboard Based on One TableImage Removed |
text-length | 0 |
---|
type | child-pages |
---|
|
|
- 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.
- 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.
- 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.
- 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 configuration:
Table Filter:
Filter Column | Status |
---|
Filter Type | Dropdown |
---|
Filter Values | Reversed filter 'Done' |
---|
Pivot Table:
Row Labels | Assignee |
---|
Column Labels | Status |
---|
Calculated Column | Key |
---|
Operation Type | Count, 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:
- Insert the Jira Issues macro.
- Wrap the Jira Issues macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- Add a table with a single column 'Time Evaluation' and containing the Status macros.
- Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.
Handy Tip |
---|
|
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 Column | Status |
---|
Filter Type | Dropdown |
---|
Filter Values | Done |
---|
Pivot Table:
Row Labels | Assignee |
---|
Column Labels | - |
---|
Calculated Column | Original Estimate, Time Spent |
---|
Operation Type | Sum |
---|
Use the following SQL query:
Code Block |
---|
|
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 configuration:
Table Filter:
Filter Column | Status |
---|
Filter Type | Dropdown |
---|
Filter Values | Done |
---|
Pivot Table:
Row Labels | T |
---|
Column Labels | - |
---|
Calculated Column | Original Estimate, Time Spent |
---|
Operation Type | Sum |
---|
Chart from Table:
Type | Bar |
---|
Labels Column | T |
---|
Values Column | Sum of Original Estimate, Sum of Time Spent |
---|
Handy Carousel |
---|
Image RemovedImage Removed |
Visualizing the Issues Distribution by Sprints or Releases
Macro configuration:
Table Filter:
Filter Column | Status |
---|
Filter Type | Dropdown |
---|
Filter Values | Reversed filter 'Done' |
---|
Pivot Table:
Row Labels | Assignee |
---|
Column Labels | Status |
---|
Calculated Column | Key |
---|
Operation Type | Count, Literal |
---|
Chart from Table:
Type | Stacked column |
---|
Labels Column | Fix Version(s) |
---|
Values Column | Count 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 configuration:
Step 1. Configure Table1:
Pivot Table:
Row Labels | Created |
---|
Column Labels | - |
---|
Calculated Column | Summary |
---|
Operation Type | Count |
---|
Additional parameters:
- Set the Date period aggregation option: Week.
Use the following SQL query:
Code Block |
---|
|
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 RemovedImage Removed
Image Removed
Table Filter:
Filter Column | Resolved |
---|
Filter Type | Dropdown |
---|
Filter Values | Reversed filter '-empty-' |
---|
Pivot Table:
Row Labels | Resolved |
---|
Column Labels | - |
---|
Calculated Column | Summary |
---|
Operation Type | Count |
---|
Additional parameters:
- Set the Date period aggregation option: Week.
Use the following SQL query:
Code Block |
---|
|
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:
Type | Time Area |
---|
Dates Column | Created, Resolved |
---|
Values Column | Count |
---|
Image Removed
Visualizing Story Points Performance
Macro configuration:
Table Filter:
Filter Column | T | Sprint |
---|
Filter Type | Visual | Dropdown |
---|
Filter Values | Improvement, New Feature | Reversed filter: '-empty-' |
---|
Pivot Table:
Row Labels | Sprint |
---|
Column Labels | Status |
---|
Calculated Column | Story points |
---|
Operation Type | Sum |
---|
Chart from Table:
Type | Column |
---|
Labels Column | Sprint |
---|
Values Column | Sum 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
Macro configuration:
Table Filter:
Filter Column | Sprint |
---|
Filter Type | Dropdown |
---|
Filter Values | Reversed filter '-empty-' |
---|
Pivot Table:
Row Labels | Sprint |
---|
Column Labels | Status |
---|
Calculated Column | Story points |
---|
Operation Type | Sum |
---|
Use the following SQL query:
Code Block |
---|
|
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:
Type | Column |
---|
Labels Column | Story Points Committed |
---|
Values Column | Story Points Completed |
---|
Visualizing of Completed Story Points by Teams
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira Issues macro in the Table Filter macro.
- Add a table containing additional information for merging.
- Wrap the Table Filter macro and the table containing additional information in the Table Transformer macro.
- Wrap the Table Transformer macro in the Pivot Table macro.
- Wrap the Pivot Table macro in the Chart from Table macro.
Image Removed
Macro configuration:
Table Filter:
Filter Column | Status | Sprint |
---|
Filter Type | Dropdown | Dropdown |
---|
Filter Values | Done | Reversed filter: '-empty-' |
---|
Select the default Lookup tables preset and a column for matching the records.
Handy Tip |
---|
|
If the columns for matching have different labels in multiple tables, use the query from the example. |
Pivot Table:
Row Labels | Sprint |
---|
Column Labels | Team |
---|
Calculated Column | Story points |
---|
Operation Type | Sum |
---|
Chart from Table:
Type | Column |
---|
Labels Column | Sprint |
---|
Values Column | Sum of story points Team 1, Sum of story points Team 2, Sum of story points Team 3 |
---|
Handy Carousel |
---|
Image RemovedImage RemovedImage Removed
Macro configuration:
Step 1. Configure Table1:
Table Filter:
Filter Column | Status | Sprint |
---|
Filter Type | Dropdown | Dropdown |
---|
Filter Values | Done | ECS Sprint 3 |
---|
Pivot Table:
Row Labels | Resolved |
---|
Column Labels | - |
---|
Calculated Column | Story points |
---|
Operation Type | Sum |
---|
Use the following SQL query:
Code Block |
---|
|
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 |
Table Filter:
Filter Column | Sprint |
---|
Filter Type | Dropdown |
---|
Filter Values | ECS Sprint 3 |
---|
Pivot Table:
Row Labels | - |
---|
Column Labels | - |
---|
Calculated Column | Story points |
---|
Operation Type | Sum |
---|
Image Removed
Step 3. Wrap Table1 and Table2 in the Chart from Table macro.
Chart from Table:
Type | Time Line |
---|
Dates Column | Resolved |
---|
Values Column | Ideal, Actual |
---|
Image Removed
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira Issues macro in the Table Filter macro.
- Wrap the Table Filter macro in the Table Transformer macro.
- Add a table containing project milestones.
- Wrap the Table Filter macro and the table containing milestones in the Table Transformer macro.
- Wrap the Table Transformer macro and the table in the Chart from Table macro.
Handy Tip |
---|
|
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 |
---|
|
Panel |
---|
title | Find more use cases: |
---|
| |
|
Image Removed
Macro configuration:
Table Filter:
Filter Column | Issue type | Sprint |
---|
Filter Type | Visual | Dropdown |
---|
Filter Values | New feature, Improvement | ECS Sprint 2, ECS Sprint 3, ECS Sprint 4 |
---|
Use the following SQL query:
Code Block |
---|
|
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:
Type | Gantt |
---|
Labels Column | Sprint, Key, Milestone |
---|
Values Column | Created, Due, Completion ratio, Date |
---|
Handy Carousel |
---|
Image Removed Image Removed |
Creating a Dashboard Based on One Table
Image Removed
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
Create a user macro to hide the unnecessary information from the Jira Issues macro:
Image Removed
Code Block |
---|
|
## @noparams
<style>
.refresh-issues-bottom { display: none}
</style>
|