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:
- Insert the Jira Issues macro.
- Wrap the Jira macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
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.
| Handy Carousel |
|---|
|
Evaluating Calculated Planned and Spent Time Per Assignee
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira 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. |
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 |
Table Transformer:
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 |
|---|
|
Visualizing of the Amount of Planned and Spent Time Within the Project
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- Wrap the Pivot Table macro in the Chart from Table macro.
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 |
|---|
|
Visualizing the Issues Distribution by Sprints or Releases
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- Wrap the Pivot Table macro in the Chart from Table macro.
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 |
|---|
|
Comparison of Graphs of Open and Resolved Tasks
Macro combination (the chart is based on two transformed pivot tables):
Step 1. Configure Table1:
- Insert the Jira Issues macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.
Step 2. Configure Table2:
- Insert the Jira Issues macro.
- Wrap the Jira macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- 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 Labels | Created |
|---|---|
| Column Labels | - |
| Calculated Column | Summary |
| Operation Type | Count |
Additional parameters:
- Set the Date period aggregation option: Week.
Table Transformer:
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 |
|---|
|
Step 2. Configure Table2:
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.
Table Transformer:
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' |
| Handy Carousel |
|---|
|
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 |
Visualizing the Story Points Performance
Macro combination:
- Insert the Jira Issues macro.
- Wrap the Jira macro in the Table Filter macro.
- Wrap the Table Filter macro in the Pivot Table macro.
- Wrap the Pivot Table macro in the Chart from Table macro.
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 |
|---|
|
