This version of the app's documentation is outdated. Please find the information you're looking for here:How to use Pivot Table macro |
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:
|
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:
There exist three ways to insert the Pivot Table macro on the page.
Macro Insertion | Instructions | Supported Data | ||
---|---|---|---|---|
Inserting the macro in the page view mode
|
|
| ||
Inserting the macro through the action icon on the editor pane |
|
| ||
Manual entry of the {Pivot Table} query on the page |
|
| ||
Selection of the Pivot Table macro in the Select Macro form |
|
|
Before inserting the table into the Pivot Table macro ensure the following things:
|
On the Confluence page opened in the edit mode, click the Pivot table macro.
Click Edit.
In the Edit Pivot table Macro form, define parameters of the macro, as follows:
Creation of multidimensional pivot tables is available since Table Filter and Charts 3.8.0. |
Option | Supported Data Formats | Allowed Values | Description |
---|---|---|---|
Default Sorting | All | First column (by default) | Select the appropriate sorting order for the columns of your pivot table. |
Cell Value Delimiter | All | Empty (by default) | Specify the correct cell value delimiter used in the cells of the source table (if applicable). |
Decimal separator | Numbers only | Point (by default) | Select the appropriate decimal separator used in numerical values of the source table. |
Thousands separator | Numbers only | Empty (by default) | Select the thousands separator for improving look of numerical values. |
Decimal places | Numbers only | Empty (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 format | Dates only | Last used date format (by default) | Specify the correct date format so the macro can properly treat the dates during aggregation. |
Date period aggregation | Dates only |
| Select the appropriate time period for data aggregation. |
Number range aggregation |
|
| 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:
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:
|
Show data labels as | Numbers only |
| Select the appropriate view to display data values. |
Literal value delimiter |
|
| Specify the value delimiter for aggregated results after using the literal operation. |
Set worklog settings | Worklogs only | Click the button to define the following parameters:
| |
Hide totals | All | Disabled (by default) | Select this option to hide the columns and rows showing total values in the pivot table. |
Show the source table | All | Disabled (by default) | Select this option to show the source table under the pivot table. |
Hide control panel | All | Disabled (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:
|
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, |
-
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:
To save new parameters in the macro body:
To export the pivot table to PDF or Word:
To export the pivot table to CSV:
For the details on CSV export, please see this page.
To show the source table:
To save changes in the pivot table:
To hide the control panel:
To show the control panel:
Starting from 3.9.0 version, the Pivot Table macro supports summarization of worklogs and time intervals.
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).
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. |