Pivot Table & Pivot Chart Gadget
- Danut M [StonikByte]
- David Claudia
- Liana Boboescu [StonikByte]
On this page
Overview
This gadget allows you to generate and display pivot tables or pivot charts based on issues in your Jira instance using drag & drop functionality, as you do it with tools like Excel, for example.
A pivot table is a data summarization tool used in data analysis and business intelligence. It enables you to organize and group data from Jira in a more readable and manageable format. Essentially, pivot tables turn extensive data into concise reports by performing operations like summing, averaging, or counting table data based on columnar fields. You can use this tool to create and display multi-dimensional tables on your Jira dashboards.
A pivot chart is a graphical representation of the data in a pivot table. It allows you to visualize the summarized data, making it easier to understand and identify patterns, trends, and comparisons.
The gadget allows you to configure multi-dimensional pivot tables, simple or heat-map, as in the examples below.
The gadget allows you to choose from various types of pivot charts: column charts, line charts, bar charts, stack area charts or pie charts.
Configuration
The settings of this gadget are divided in 3 tabs:
Data tab - contains the required settings of the gadget, such as title or data source
More Settings tab - contains the optional settings
Colors tab - lets you choose the base color of the heatmap table
Follow the steps below to configure the gadget as Pivot Table or Pivot Chart:
Add the Pivot Table & Pivot Chart gadget to your Jira dashboard.
Enter a suggestive Gadget title.
At Data Source, select the Filter or enter the JQL that returns the issues that serve as input data for the pivot table or pivot chart. If you want the sub-tasks from the selected filter or JQL to be included, check Count also the sub-tasks and their estimate.
At Fields, select the issue fields that you need in your pivot table or pivot chart.
The table from the Output configuration section allows you to configure the pivot table or pivot chart.
In the top-right cell, select the type of the output data: pivot table or pivot chart.
If you want to display the results as a regular pivot table, select Table. Or, select Table Heatmap to display the results as a heatmap-capable table where the values of interest are highlighted in shades of red.
If you want to display as a pivot chart, select the appropriate option for the type of chart you want.
By using drag & drop, place the issue fields that you want to be displayed on the columns and the rows of the table.
Choose the calculation criteria. You can choose between various options. For example, to display the number of issues in table, select Count.
If you want to display the sum of an issue field (like Story Points), select Sum and then the select field; make sure the field that you want to sum-up was added to the Fields option above.
A preview of the gadget results is displayed based on the configuration you made and using some randomly generated dummy data.
On the More Settings tab, you can choose the display format for the fields that you selected for being used in the Pivot Table or Pivot Chart.
For example, for date fields like Created or Updated you can choose a format like “MM/dd/yyyy” or “yyyy”, while for time fields like Time Spent or Original Estimate you may choose to display the result in “Hours” or “Calendar Days (24h)”.
Make any adjustments as necessary and when you are done, click Save. The gadget will now display the pivot table or pivot chart that you configured by using real Jira data.
Data tab
On this tab you can configure the required settings of the gadget.
Setting | Description |
|---|---|
Gadget Title | Choose what to display in the title bar of the gadget. |
Data Source | Select the type of Data Source used by the gadget:
|
Include the sub-tasks | Check this option if you want the sub-tasks from the selected filter or JQL to be included in the calculation. |
Fields | Select the issue fields you need to generate the pivot table or pivot chart. The multi-value fields such as Labels, Fix Version/s or Components are marked with the symbol If the Sprint field is selected, only the most recent sprint is considered for issues that have gone through multiple sprints. The Parent Epic (on Cloud only) and Epic Link fields can be used to display the parent epics. The epics are displayed in form of key: summary (for example, DEMO-1: My epic summary). See more examples in section Epics Progress. Also, on Cloud you can display statistics at the initiative or theme level if you have these additional hierarchy levels added on top of epics. For this, use Parent of Epic (for issues with hierarchy level 2 issues) or Parent L3 (for issues with hierarchy level 3). See more examples in section Progress of the Initiatives or Themes. You can also select field specific to Jira Service Management projects such as Satisfaction or Sentiment. |
Output configuration | This is the place where you can configure the pivot table or pivot chart. Note If you drag & drop a field between the columns area
|
More Settings tab
On this tab you can configure the optional settings of the gadget.
Setting | Description |
|---|---|
Fields formatting mode | For the fields that you selected on the Data tab for being used in the Pivot Table or Pivot Chart you may specify the format to be used for displaying their values. For example, for date fields like Created or Updated you can choose a format like “MM/dd/yyyy” or “yyyy”, while for time fields like Time Spent or Original Estimate you may choose to display the result in “Hours” or “Calendar Days (24h)”. Choose “Q yyyy“ or “yyyy-Q“ format to display the quarter of the year. |
Display the 'Totals' row | Check this option if you want the pivot table to display the 'Totals' row. It takes effect only if the gadget is configured as Table, Table Heatmap, Table Col Heatmap or Table Row Heatmap. |
Display the 'Totals' column | Check this option if you want the pivot table to display the 'Totals' column. It takes effect only if the gadget is configured as Table, Table Heatmap, Table Col Heatmap or Table Row Heatmap. |
Display the 'Average' column | Check this option if you want the pivot table to display the 'Average' column. It takes effect only if the gadget is configured as Table, Table Heatmap, Table Col Heatmap or Table Row Heatmap. You can choose to Calculate the average based on:
|
Legend display | This setting only applies to pivot charts. Indicates whether the chart legend should be displayed or not and where exactly. |
Refresh interval | Choose how often the gadget will refresh automatically. |
Colors tab
On this tab you can choose the base color for the heatmap table.
Setting | Description |
|---|---|
Heatmap base color | Let’s you change the hue used to generate the color palette used by heatmaps. This option only takes effect if the gadget output is set to Table Heatmap, Table Col Heatmap, or Table Row Heatmap. The gadget also automatically changes the text color based on the background color for optimal visibility. |
More Examples
Here are more examples of using this gadget.
Epics Progress
There are various ways for displaying the progress of your epics.
Epics by No. of Tasks in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) under the epic. Make sure the filter doesn't return the epics themselves. Then configure the gadget to calculate the Count as in the example below.
Epics Progress By Percentage of Tasks in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) under the epic. Make sure the filter doesn't return the epics themselves. Then configure the gadget to calculate the Count as Fraction of Rows as in the example below.
Epics by Sum of Story Points in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) under the epic. Make sure the filter doesn't return the epics themselves. Then configure the gadget to calculate the Sum of Story Points as in the example below.
Epics by Percent of Story Points in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) under the epic. Make sure the filter doesn't return the epics themselves. Then configure the gadget to calculate the Sum As Fraction of Rows for Story Points field as in the example below.
Progress of the Initiatives or Themes
If you have initiatives or themes defined in your Jira Cloud instance as issue types on top of epics, you can easily track their progress. This gadget support stats by two levels above the epic; all you have to do us to use one of these fields:
Parent of Epic - for Hierarchy Level 2 issues (one level above epic)
Parent L3 - for Hierarchy Level 3 issues (two levels above epic)
Initiatives or Themes by No. of Tasks in each Status Category split by issue type
To achieve this, configure the gadget with a filter or JQL that returns the issues (stories, tasks, bugs, etc) under the initiative or theme. This is an example of JQL that returns all the issues from an initiative.
key IN portfolioChildIssuesOf("DEMO-11")Make sure the filter doesn't return the initiatives or themes themselves. Then configure the gadget to calculate the Count as in the example below.
Progress of Initiatives or Themes By Percentage of Tasks in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns the issues (stories, tasks, bugs, etc) under the initiative or theme. This is an example of JQL that returns all the issues from an initiative.
key IN portfolioChildIssuesOf("DEMO-11")Make sure the filter doesn't return the initiatives or themes themselves. Then configure the gadget to calculate the Count as Fraction of Rows as in the example below.
Projects Completion
There are various ways for displaying the progress your projects.
Projects by No. of Tasks in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) from the projects that you want to track. Then configure the gadget to calculate the Count as in the example below.
Projects by Percentage of Tasks in each Status Category
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) from the projects that you want to track. Then configure the gadget to calculate the Count as Fraction of Rows as in the example below.
Sprint Velocity by Team Member
To achieve this, configure the gadget with a filter or JQL that returns issues (stories, tasks, bugs, etc) from the sprints that you want to track. Then configure the gadget to calculate the Sum of Story Points in the example below.
On the More Settings tab, choose to display the ‘Average’ column. If you want the gadget output in form of a chart, choose Stacked Column Chart or Line Chart instead of Table.
Related blog articles
The following blog articles provide more information on how to use this gadget.