Pivot Table & Pivot Chart Gadget

On this page

https://youtu.be/UVAWCp7lI38

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.

image-20240402-120550.png
Multi-dimensional pivot table showing the number of issues by assignee and type over the years.
image-20240402-121919.png
Two-dimensional pivot table with heat-map.

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 2 tabs:

  • Data tab - contains the required settings of the gadget, such as title or data source

  • More Settings tab - contains the optional settings

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

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:

  • A Saved filter containing the issues that you want to track. Make sure that the specified filter is shared with the users who will also visualize this gadget.

  • A JQL query that returns the issues you want to be processed 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 [≣]. Only one field of this type can be selected at a time.

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.

Output configuration

This is the place where you can configure the pivot table or pivot chart.

1.png Allows you to select the type of the output: pivot table or pivot chart.

2.png Configure the columns of the pivot table. You can place the necessary fields here using the drag & drop functionality.

3.png Configure the rows of the pivot table. You can place the necessary fields here using the drag & drop functionality.

4.png The calculation method/formula. For example, to display the number of issues in table, select Count. To display the sum of an issue field (like Story Points), select Sum and then the select field.

5.png A sample output of the gadget created using dummy data.

6.png Unused fields.

Note

If you drag & drop a field between the columns area 2.png and the rows area 3.png or vice versa, it will not be moved. Instead, it will be displayed on both places. This behavior is by design, because the gadget allows you to use a field in both rows and columns areas. To remove it from the columns area or from the rows area, just drag & drop it into the unused fields area 6.png.

 

More Settings tab

On this tab you can configure the optional settings of the gadget.

 

Setting

Description

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:

  • all values - the average will be calculated as the sum of cells divided by the number of cells

  • non-zero values - the average will be calculated as the sum of cells divided by the number of non-zero cells

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.

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.