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
настройки языка
Filter Column | Status |
---|---|
Filter Type | Dropdown |
Filter Values | Reversed filter 'Done' |
Row Labels | Assignee |
---|---|
Column Labels | Status |
Calculated Column | Key |
Operation Type | Count, Literal |
If you don't need to have colorful statuses in the Time Evaluation column, don't add this single column table. |
Filter Column | Status |
---|---|
Filter Type | Dropdown |
Filter Values | Done |
Row Labels | Assignee |
---|---|
Column Labels | - |
Calculated Column | Original Estimate, Time Spent |
Operation Type | Sum |
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 |
Filter Column | Status |
---|---|
Filter Type | Dropdown |
Filter Values | Done |
Row Labels | T |
---|---|
Column Labels | - |
Calculated Column | Original Estimate, Time Spent |
Operation Type | Sum |
Type | Bar |
---|---|
Labels Column | T |
Values Column | Sum of Original Estimate, Sum of Time Spent |
Filter Column | Status |
---|---|
Filter Type | Dropdown |
Filter Values | Reversed filter 'Done' |
Row Labels | Assignee |
---|---|
Column Labels | Status |
Calculated Column | Key |
Operation Type | Count, Literal |
Type | Stacked column |
---|---|
Labels Column | Fix Version(s) |
Values Column | Count Improvement, Count New Feature |
Step 1. Configure Table1:
Step 2. Configure Table2:
Step 3. Wrap Table1 and Table2 in the Chart from Table macro.
Step 1. Configure Table1:
Row Labels | Created |
---|---|
Column Labels | - |
Calculated Column | Summary |
Operation Type | Count |
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' |
Filter Column | Resolved |
---|---|
Filter Type | Dropdown |
Filter Values | Reversed filter '-empty-' |
Row Labels | Resolved |
---|---|
Column Labels | - |
Calculated Column | Summary |
Operation Type | Count |
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' |
Type | Time Area |
---|---|
Dates Column | Created, Resolved |
Values Column | Count |
Filter Column | T | Sprint |
---|---|---|
Filter Type | Visual | Dropdown |
Filter Values | Improvement, New Feature | Reversed filter: '-empty-' |
Row Labels | Sprint |
---|---|
Column Labels | Status |
Calculated Column | Story points |
Operation Type | Sum |
Type | Column |
---|---|
Labels Column | Sprint |
Values Column | Sum of story points Done, Sum of story points In Progress, Sum of story points To Do |
Filter Column | Sprint |
---|---|
Filter Type | Dropdown |
Filter Values | Reversed filter '-empty-' |
Row Labels | Sprint |
---|---|
Column Labels | Status |
Calculated Column | Story points |
Operation Type | Sum |
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' |
Type | Column |
---|---|
Labels Column | Story Points Committed |
Values Column | Story Points Completed |
You can include any table from your Confluence instance using the Table Excerpt and Table Excerpt Include macros. |
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.
If the columns for matching have different labels in multiple tables, use the query from the example. |
Row Labels | Sprint |
---|---|
Column Labels | Team |
Calculated Column | Story points |
Operation Type | Sum |
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 |
Step 1. Configure Table1:
Step 2. Configure Table2:
Step 3. Wrap Table1 and Table2 in the Chart from Table macro.
Step 1. Configure Table1:
Filter Column | Status | Sprint |
---|---|---|
Filter Type | Dropdown | Dropdown |
Filter Values | Done | ECS Sprint 3 |
Row Labels | Resolved |
---|---|
Column Labels | - |
Calculated Column | Story points |
Operation Type | Sum |
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' |
Filter Column | Sprint |
---|---|
Filter Type | Dropdown |
Filter Values | ECS Sprint 3 |
Row Labels | - |
---|---|
Column Labels | - |
Calculated Column | Story points |
Operation Type | Sum |
Type | Time Line |
---|---|
Dates Column | Resolved |
Values Column | Ideal, Actual |
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. |
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:
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* |
Type | Gantt |
---|---|
Labels Column | Sprint, Key, Milestone |
Values Column | Created, Due, Completion ratio, Date |
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:
Create a user macro to hide the unnecessary information from the Jira Issues macro:
## @noparams <style> .refresh-issues-bottom { display: none} </style> |