Tell us how you use Jira Service Desk and get a Starbucks gift card for this! Share your experience to help us design a new solution for efficient communication with customers!
Skip to end of metadata
Go to start of metadata


Managing Pivot Tables

Table Filter and Charts for Confluence add-on allows you to summarize your table data and produce its aggregated view in the form of a pivot table. You can select the column containing labels for summarization or aggregation and set the columns containing number values for calculation.

Additionally, you can select the appropriate operation type (sum, count, minimal, maximal and average). While adjusting the macro parameters you can set the decimal and thousands separator, as well as specify the number of decimal places. You can modify settings of your pivot table and save modifications in the macro body while viewing the page.

The Pivot Table macro also supports:

  • statuses
  • user mentions
  • task lists (states of check boxes)
  • icons and emoticons

 

Starting from version 3.0.0 Table Filter add-on allows you to generate pivot tables from the data tables. A pivot table is such a kind of a table that allows you to get the summarized and aggregated data values from the master table.

The Pivot table macro allows you to perform the following operations:

  • Count - counts the number of values pertaining to one label in the master table.
  • Max - determines the maximal value per each label.
  • Min - determines the minimal value per each label.
  • Average - calculates the average values per each label.
  • Sum - calculates the sum of values per each label.

Inserting Pivot table macro

There exist three ways to insert the Pivot Table macro on the page.

Macro InsertionInstructionsSupported Data

Inserting the macro in the page view mode

You can disable the inline macro insertion on a global or individual basis.

  1. Open a page with a table.
  2. Hove over the table.
  3. Click the Create a pivot table icon on the panel that appears to the right side of the table.

This option is available in Confluence Server only.

  1. Manually created and copied tables
  2. Macros outputting table data
Inserting the macro through the action icon on the editor pane
  1. Switch Confluence page to the edit mode.
  2. Position the mouse pointer within the table.

  1. On the editor pane, click the Create pivot table icon.

This option is available in Confluence Server only.

  1. Manually created and copied tables
Manual entry of the {Pivot Table} query on the page
  1. Switch Confluence page to the edit mode.
  2. Position the mouse pointer in the appropriate place on the page.
  3. Start entering {Pivot Table}.
  4. From the prompted list of macro choices, select Pivot Table.

  1. Manually created and copied tables
  2. Macros outputting table data
Selection of the Pivot Table macro in the Select Macro form
  1. Switch Confluence page to the edit mode.
  2. Position the mouse pointer in the appropriate place on the page.
  3. On the editor pane, click Insert.
  4. In the Select Macro form, locate the Pivot Table macro and insert it on the page.
  5. Place the table within the macro.
  1. Manually created and copied tables
  2. Macros outputting table data

Before inserting the table into the Pivot Table macro ensure the following things:

  1. The table contains the repeated labels that will be used for data summarization.
  2. Columns should have data of the same type. If the column has data of different types, all the data different from numbers are skipped during processing.

Selecting Data Series

  1. On the Confluence page opened in the edit mode, click the Pivot table macro.

  2. Click Edit.

  3. In the Edit Pivot table Macro form, define parameters of the macro, as follows:

    • Row labels - select one or multiple table columns that will be positioned horizontally in the pivot table.
    • Column labels - select one or multiple table columns that will be positioned vertically in the pivot table.
    • Calculated Column - select one or multiple columns that will be calculated or aggregated according to the selected operation type.
    • Operation Type - select one or multiple operations you want to perform on the calculated data in the pivot table. The following operations are available:
      • Count - counts number of entries in the selected column.
      • Sum - sums the number values in the selected column.
      • Average - counts the average values over the values in the selected column.
      • Min - finds the minimal value in the selected column.
      • Max - finds the maximal value in the selected column.
      • Literal - outputs the list of unique values matching the selected criteria.
  4. Click Save.
  5. Save the page.

Creation of multidimensional pivot tables is available since Table Filter and Charts 3.8.0.

 

 

 

 

Configuring parameters of the macro

  1. On the Confluence page opened in the edit mode, click the Pivot table macro.
  2. Click Edit.
  3. Switch to the Options tab and adjust the parameters of the pivot table.
  4. Click Save.
OptionSupported Data FormatsAllowed ValuesDescription
Default SortingAllFirst column (by default)Select the appropriate sorting order for the columns of your pivot table.
Cell Value DelimiterAllEmpty (by default)Specify the correct cell value delimiter used in the cells of the source table (if applicable).
Decimal separatorNumbers onlyPoint (by default)Select the appropriate decimal separator used in numerical values of the source table.
Thousands separatorNumbers onlyEmpty (by default)Select the thousands separator for improving look of numerical values.
Decimal placesNumbers onlyEmpty (by default)Enter the number of decimal places to show in the pivot table. This option is usually needs to be activated for the Average operation.
Date formatDates onlyLast used date format (by default)Specify the correct date format so the macro can properly treat the dates during aggregation.
Date period aggregationDates only
  • None
  • Hour
  • Day (default)
  • Week
  • Month
  • Quarter
  • Year
Select the appropriate time period for data aggregation.
Number range aggregation
  • Numbers
  • Time intervals
  • Worklogs
  • [100;250;500]
  • (100;250;500)
  • 10
  • e1
Define the range of numbers, worklog periods, or time intervals against which numeric values will be aggregated. The range for aggregation can be specified in the following formats:
  • Inclusive when threshold values are included into the range: [100;250;500]. So number ranges will fall into 0-100, 101-250, 251-500, and 500+.
  • Exclusive when threshold values are excluded from the range and join the next range: (100;250;500). So number ranges will fall into 0-99, 100-249, 250-499, and 500+.

If you use a single number (for example, '10'), the Pivot Table will split number ranges, as follows: 1-9, 10-19, 20-29, and so on.

If you use the exponents:

  • 'e1' will split number ranges, as follows: 10, 100, 1000, 10 000, and so on.
  • 'e2' will split number ranges, as follows: 100, 10 000, 1 000 000, and so on.
Show data labels asNumbers only
  • Number (default)
  • Percentage
  • Number (Percentage)
  • Percentage (Number)
Select the appropriate view to display data values.
Literal value delimiter
  • Text values
  • Numbers
  • Statuses
  • Icons
  • Date stamps
  • None (default)
  • Comma
  • Semicolon
  • Space
  • Line break
  • Bullet list
  • Numbered list
  • Any custom character
Specify the value delimiter for aggregated results after using the literal operation.
Set worklog settingsWorklogs only
Click the button to define the following parameters:
  • Days in week - define the number of business days in one week.
  • Hours in day - define the number of business hours in one day.
  • Time format - select the appropriate worklog time format (where 'm' stands for minute, 'h' for hours, 'd' for day, and 'w' for week).
Hide totalsAllDisabled (by default)Select this option to hide the columns and rows showing total values in the pivot table.
Show the source tableAllDisabled (by default)Select this option to show the source table under the pivot table.
Hide control panelAllDisabled (by default)Select this option to hide the control panel in the page view mode.

Please consider the following while configuring parameters of the Pivot Table macro:

  • Default sorting is available only in the pivot tables with one column selected as row labels.
  • The incorrectly specified decimal separator may cause the incorrect calculation of numerical values.
  • Selection of the thousands separator is optional as it only improves look of numerical values without affecting the original values in the source table.
  • You may need to limit the decimal places when using the Average operation type.

From 3.8.2 version, the Pivot Table macro supports icons for summarization and aggregation. You can use this feature when working with the JIRA Issues macro,

-

 

 

Using the Pivot table

Once you have defined settings of the Pivot table macro and saved the page, you get a pivot table with summarized data.

The generated pivot table may be compromised of two or more columns. The number of columns in the final pivot table depends mainly on the number of columns selected in the row and column labels, and calculated columns. 

You can quickly update the data series used for calculation as well as update sets of columns selected in row and column labels.

If you deal with the multidimensional pivot tables, you can collapse specific horizontal and vertical levels of the pivot table by clicking the collapse   icon. To expand the collapsed level of the table, click  icon. Starting from 3.10.0, the add-on preserves the collapsed/expanded state for each level of a pivot table after your manipulations on its data.

The Total row shows the calculated value throughout the pivot table and for specific levels of the pivot table.

To update the data series for summarization or aggregation:

  1. To the right side of your pivot table, locate the Pivot Table control panel.
  2. Update one or multiple parameters, as follows:
    • Row labels - update the set of table columns that will be positioned horizontally in the pivot table.
    • Column labels - update the set of table columns that will be positioned vertically in the pivot table.
    • Calculated Column - select one or multiple columns that will be calculated or aggregated according to the selected operation type.
    • Operation Type - select one or multiple operations you want to perform on the calculated data in the pivot table.

To save new parameters in the macro body:

  1. Click the Cogwheel  icon.
  2. Select Save settings.

To export the pivot table to PDF or Word:

  1. Click the Cogwheel  icon.
  2. Select Export to PDF or Export to Word correspondingly.

To export the pivot table to CSV:

  1. Click the Cogwheel  icon.
  2. Select Save changes

For the details on CSV export, please see this page.

To show the source table:

  1. Click the Cogwheel  icon.
  2. Select Show source table.

To save changes in the pivot table:

  1. Click the Cogwheel  icon.
  2. Select Save changes.

To hide the control panel:

  1. Click the Cogwheel  icon.
  2. Select Hide control panel.

To show the control panel:

  1. To the right side of the table locate the Cogwheel  icon and click it.

 

 

Generating a Pivot Table on Worklogs or Time Intervals

Starting from 3.9.0 version, the Pivot Table macro supports summarization of worklogs and time intervals.

  1. Insert the Pivot Table macro on the page.
  2. Place the table with time intervals or the JIRA issue macro with worklogs into the Pivot Table macro.
  3. Save the page.
  4. Define parameters of the Pivot Table macro on the control panel:
    • Row Labels - select the appropriate table column for aggregation.
    • Calculated Column - select the column with worklogs or time intervals for calculation.
    • Operation Type - select the required operation types for execution on worklogs or time intervals.
  5. Save the changes.

 

 

Generating a Pivot Table on Delimited Values in Table Cells

  1. Open the table with multiple values in each table cell.
  2. Place the table into the Pivot Table macro.
  3. Select the Pivot Table macro and click Edit.
  4. Switch to the Options tab.
  5. Locate the Cell Value Delimiter box and enter the delimiter used to separate multiple data values in cells.
  6. Save the macro and the page.
  7. Define parameters of the Pivot Table macro on the control panel:
    • Row Labels - select the table column with cells containing multiple delimited values.
    • Calculated Column - select the column for calculation.
    • Operation Type - select the Count operation type.
  8. Save the changes.

 

 

Aggregating Data against Time Periods

When you deal with specific dates, sometimes you may need to aggregate this data against the larger time periods (such as weeks, months, quarters, or years).

  1. Open the table data for aggregation.
  2. Place the table into the Pivot Table macro.
  3. Select the Pivot Table macro and click Edit.
  4. Switch to the Options tab.
  5. Locate the Date format box and enter the correct date format used in your source table.
  6. Locate the Date period aggregation option and select the time period for aggregation.
  7. Save the macro and the page.
  8. Define parameters of the Pivot Table macro on the control panel:
    • Row Labels - select the table column with dates for aggregation.
    • Calculated Column - select the column for calculation.
    • Operation Type - select the appropriate operation type.
  9. Save the changes.



Using the Literal operation for grouping values

  1. Open the table data for aggregation.
  2. Place the table into the Pivot Table macro.
  3. Save the macro and the page.
  4. Define parameters of the Pivot Table macro on the control panel:
    • Row labels - select the table column with labels (text values) for aggregation.
    • Column labels - select the table column with labels (text values) for aggregation.
    • Calculated Column - select the column with labels (text values, icons, emoticons, statuses) for aggregation.
    • Operation Type - select 'Literal' as an operation type.
  5. Save the changes.







Removing Pivot Table macro for Tables

  1. Switch Confluence page to the edit mode.
  2. Select the Pivot Table macro with the table or macro outputting the table.
  3. Click Unwrap.
     

 

 

Copying a table into Pivot Table macro

  1. Switch Confluence page to the edit mode.
  2. Position the mouse pointer into any cell of the table you want to copy.
  3. Click the Copy Table icon on the editor pane.
  4. Paste the table into the Pivot Table macro on the same or on a new page.

You can also use the Table Excerpt and Table Excerpt Include macros for building multiple pivot tables on the basis of a single source table.



Setting worklog settings


Please ensure that the worklog parameters set in the macro are identical to parameters configured in Atlassian JIRA.


  1. Switch the page to edit mode.
  2. Select the macro and click Edit.
  3. Switch to the Filtration tab.
  4. Click the Set worklog settings button.
  5. In the opened form, define the worklog parameters, as follows:
    1. Days in week - enter the number of business days in a week.
    2. Hours in day - enter the number of working hours in one business day.
    3. Time format - select the used time format in worklog values.
    4. Week shortening - enter the shortening for the week time unit if you use any language different from English.
    5. Day shortening - enter the shortening for the day time unit if you use any language different from English.
    6. Hour shortening - enter the shortening for the hour time unit if you use any language different from English.
    7. Minute shortening - enter the shortening for the minute time unit if you use any language different from English.
  6. Click Save.
  7. Save the macro and the page.

Worklog time units in different languages are denoted with different shortenings. Please adjust them according to your JIRA localization settings.

  • No labels