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 Insertion | Instructions | Supported Data |
---|
Inserting the macro through the action icon on the editor pane | - Switch Confluence page to the edit mode.
- Position the mouse pointer within the table.
- On the editor pane, click the Create pivot table icon.
| - Manually created and copied tables
|
Manual entry of the {Pivot Table} query on the page | - Switch Confluence page to the edit mode.
- Position the mouse pointer in the appropriate place on the page.
- Start entering {Pivot Table}.
- From the prompted list of macro choices, select Pivot Table.
| - Manually created and copied tables
- Macros outputting table data
|
Selection of the Pivot Table macro in the Select Macro form | - Switch Confluence page to the edit mode.
- Position the mouse pointer in the appropriate place on the page.
- On the editor pane, click Insert.
- In the Select Macro form, locate the Pivot Table macro and insert it on the page.
- Place the table within the macro.
| - Manually created and copied tables
- Macros outputting table data
|
Before inserting the table into the Pivot Table macro ensure the following things: - The table contains the repeated labels that will be used for data summarization.
- 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.
|
Configuring Pivot table macro
- 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:
- Row Labels - select the column with labels, which the data will be summarized for.
- Calculated Column - select the column you want to perform calculations for.
- Operation Type - select one or multiple operations you want to perform on summarized data.
- Decimal separator - select the correct decimal character.
- Thousands separator - select the correct thousands separator (optional, for better data presentation).
- Decimal places - enter the number of decimal places to show in the pivot table.
- Show the source table - select this option to show the source table under the pivot table.
- Hide control panel - select this option to hide the control panel in the page view mode.
- Click Save.
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 is comprised of two columns:
- The column with labels that data was summarized for.
- The column with calculated values depending on the selected operation.
The Total row shows the calculated value throughout the pivot table.
To the right side of your pivot table, you can locate the Pivot Table Settings sidebar. It allows you to alternate the following parameters of Pivot table macro:
- Row Labels - change the column with labels for another one.
- Calculated Column - change the column for data calculations.
- Operation Type - change the operation type for another one or select additional
To save new parameters in the macro body, click the Cogwheel icon and select Save settings.
To export the pivot table, click the Cogwheel icon and select Export to PDF or Export to Word.
Creating Two-Dimensional Pivot Table
Starting from version 3.6.0 Table Filter and Charts add-on allows you to create two-dimensional pivot tables and select multiple calculated columns.
- Place the table within the Pivot Table macro.
- Adjust the settings of the macro if needed.
- Save the page with the macro.
- In the Row Labels box, select two table columns for data aggregation or summarization.
- Select one or multiple table columns for data calculcation.
- Save changes in the macro body.
Removing Pivot Table macro for Tables
- Switch Confluence page to the edit mode.
- Select the Pivot Table macro with the table or macro outputting the table.
- Click Unwrap.