The Pivot Table control proposes several Automatic Application Features (AAF) allowing the user to benefit from all control capabilities.

The user can:

Versions 19 and laterresize the first column of the Pivot Table control with the mouse (column containing the row headers).

New in version 19resize the first column of the Pivot Table control with the mouse (column containing the row headers).

resize the first column of the Pivot Table control with the mouse (column containing the row headers).

collapse/expand the columns and the rows by clicking the "+" and "-" icons of headers.

display a popup menu allowing him to perform several actions on the rows and columns.

Versions 19 and laterdisplay a popup menu on the cells.

New in version 19display a popup menu on the cells.

display a popup menu on the cells.

Versions 20 and laterselect several rows, columns or cells via the UPPER and CTRL keys. This option is available only if the multiselection is allowed in the control. See Describing a Pivot Table control for more details.

New in version 20select several rows, columns or cells via the UPPER and CTRL keys. This option is available only if the multiselection is allowed in the control. See Describing a Pivot Table control for more details.

select several rows, columns or cells via the UPPER and CTRL keys. This option is available only if the multiselection is allowed in the control. See Describing a Pivot Table control for more details.

Versions 20 and laterperform a search in the cells of a Pivot table control by using the CTRL + F shortcut.

New in version 20perform a search in the cells of a Pivot table control by using the CTRL + F shortcut.

perform a search in the cells of a Pivot table control by using the CTRL + F shortcut.

The features of the popup menu for the rows and columns are as follows:

Versions 20 and laterNote: If the multi-selection of rows, columns and cells was enabled on the Pivot Table control, some operations are available for the selected rows, columns or cells only.

New in version 20Note: If the multi-selection of rows, columns and cells was enabled on the Pivot Table control, some operations are available for the selected rows, columns or cells only.

Note: If the multi-selection of rows, columns and cells was enabled on the Pivot Table control, some operations are available for the selected rows, columns or cells only.

The features of popup menu

Hiding the columns and the rows

Some pivot tables can contain an important number of rows and columns (if the pivot table displays several hierarchy levels for example).

To get a more concise display, you have the ability to:

hide the current column.

hide all the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to hide the month of February for all the years displayed in this table.

hide all the columns except for the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to display the month of February for each year and to hide all the other months.

This display mode is also available for the rows.

The hidden columns and/or rows can be redisplayed at any time ("Re-display all the hidden columns" for example).

Caution:

If a column or a row is not repeated, the only possible action is to hide the selected column or row.

The columns or rows are hidden but the pivot table is not recalculated.

Versions 20 and later

Deleting the columns and the rows

Some pivot tables can contain an important number of rows and columns (if the pivot table displays several hierarchy levels for example).

To get a more concise display, you have the ability to:

delete the current column.

delete all the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to delete the month of February for all the years displayed in this table.

delete all the columns except for the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to display the months of February for each year. All the other months will be deleted.

This display mode is also available for the rows.

During the deletion, the content of the pivot table is recalculated: the totals are updated.

The deletec columns and/or rows can be redisplayed at any time ("Re-display all the deleted columns" for example). During the re-display, the content of the pivot table is recalculated: the totals are updated.

Caution: If a column or a row is not repeated, the only possible action is to delete the selected column or row.

New in version 20

Deleting the columns and the rows

Some pivot tables can contain an important number of rows and columns (if the pivot table displays several hierarchy levels for example).

To get a more concise display, you have the ability to:

delete the current column.

delete all the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to delete the month of February for all the years displayed in this table.

delete all the columns except for the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to display the months of February for each year. All the other months will be deleted.

This display mode is also available for the rows.

During the deletion, the content of the pivot table is recalculated: the totals are updated.

The deletec columns and/or rows can be redisplayed at any time ("Re-display all the deleted columns" for example). During the re-display, the content of the pivot table is recalculated: the totals are updated.

Caution: If a column or a row is not repeated, the only possible action is to delete the selected column or row.

Deleting the columns and the rows

Some pivot tables can contain an important number of rows and columns (if the pivot table displays several hierarchy levels for example).

To get a more concise display, you have the ability to:

delete the current column.

delete all the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to delete the month of February for all the years displayed in this table.

delete all the columns except for the columns identified with the same information. For example, in a pivot table presenting calculations over several years, you have the ability to display the months of February for each year. All the other months will be deleted.

This display mode is also available for the rows.

During the deletion, the content of the pivot table is recalculated: the totals are updated.

The deletec columns and/or rows can be redisplayed at any time ("Re-display all the deleted columns" for example). During the re-display, the content of the pivot table is recalculated: the totals are updated.

Caution: If a column or a row is not repeated, the only possible action is to delete the selected column or row.

Collapsing/Expanding the columns or the rows

For a pivot table with an important hierarchy, you have the ability to:

collapse or expand all the columns and/or all the rows in a single action.

Versions 19 and laterexpand a level and all its sub-levels in a single action (in column or in row).

New in version 19expand a level and all its sub-levels in a single action (in column or in row).

expand a level and all its sub-levels in a single action (in column or in row).

For example:

If all the columns are expanded, the pivot table is displayed in details: all the intermediate results are visible, from the highest level to the lowest level.

If all the columns are collapsed, the pivot table has a synthetic display: it only presents the first-level calculations.

Versions 20 and later

Adding groupings: weekly, fortnightly, quarterly, ...

If the pivot table does not contain enough details, you have the ability to add columns in order to enhance the displayed statistics.

The popup menu of columns proposes the "Add" option. This option allows you to add groupings that are not displayed, for example: Half-year, Quarter, Fortnight, ...

Caution: this option rquires to recalculate the pivot table. If several operations must be performed, the recalculation of data can be postponed.

New in version 20

Adding groupings: weekly, fortnightly, quarterly, ...

If the pivot table does not contain enough details, you have the ability to add columns in order to enhance the displayed statistics.

The popup menu of columns proposes the "Add" option. This option allows you to add groupings that are not displayed, for example: Half-year, Quarter, Fortnight, ...

Caution: this option rquires to recalculate the pivot table. If several operations must be performed, the recalculation of data can be postponed.

Adding groupings: weekly, fortnightly, quarterly, ...

If the pivot table does not contain enough details, you have the ability to add columns in order to enhance the displayed statistics.

The popup menu of columns proposes the "Add" option. This option allows you to add groupings that are not displayed, for example: Half-year, Quarter, Fortnight, ...

Caution: this option rquires to recalculate the pivot table. If several operations must be performed, the recalculation of data can be postponed.

Versions 20 and later

Re-organize the headers of rows and columns

This option is used to modify the display of rows and columns in the Pivot Table control.

When this option is selected, the pivot table becomes editable.

You can:

make rows or columns visible or not. To make the elements visible, all you have to do is select the non-displayed elements found in the "Headers not displayed" area and move them to the requested location in the rows or columns. To make elements invisible, all you have to do is select the corresponding header and move it to the "Headers not displayed" area.

reverse the dimensions. All you have to do is select the requested header and move it by Drag and Drop to the requested position.

New in version 20

Re-organize the headers of rows and columns

This option is used to modify the display of rows and columns in the Pivot Table control.

When this option is selected, the pivot table becomes editable.

You can:

make rows or columns visible or not. To make the elements visible, all you have to do is select the non-displayed elements found in the "Headers not displayed" area and move them to the requested location in the rows or columns. To make elements invisible, all you have to do is select the corresponding header and move it to the "Headers not displayed" area.

reverse the dimensions. All you have to do is select the requested header and move it by Drag and Drop to the requested position.

Re-organize the headers of rows and columns

This option is used to modify the display of rows and columns in the Pivot Table control.

When this option is selected, the pivot table becomes editable.

You can:

make rows or columns visible or not. To make the elements visible, all you have to do is select the non-displayed elements found in the "Headers not displayed" area and move them to the requested location in the rows or columns. To make elements invisible, all you have to do is select the corresponding header and move it to the "Headers not displayed" area.

reverse the dimensions. All you have to do is select the requested header and move it by Drag and Drop to the requested position.

Versions 20 and later

Reversing the row and column headers

This option is used to make the data of the Pivot Table control turn round. All the columns become rows and all the rows become columns.

Note: To reverse the rows and the columns found in a Pivot Table control by programming, use AAFExecute (or ExecuteAAF) associated with the aafPvtReverseRowColumn constant.

New in version 20

Reversing the row and column headers

This option is used to make the data of the Pivot Table control turn round. All the columns become rows and all the rows become columns.

Note: To reverse the rows and the columns found in a Pivot Table control by programming, use AAFExecute (or ExecuteAAF) associated with the aafPvtReverseRowColumn constant.

Reversing the row and column headers

This option is used to make the data of the Pivot Table control turn round. All the columns become rows and all the rows become columns.

Note: To reverse the rows and the columns found in a Pivot Table control by programming, use AAFExecute (or ExecuteAAF) associated with the aafPvtReverseRowColumn constant.

Versions 22 and later

Compare the periods

This option is used to compare the data displayed in the Pivot Table control over 2 periods.

When selecting this option:

A window is displayed, allowing you to enter the comparison characteristics:

The validation of this window recalculates the displayed data, to only get the comparison data. For example:

To restore the standard display of Pivot Table control, select "Cancel the comparison of periods" from the popup menu of control.

Note: To start the comparison of periods by programming, use AAFExecute (or ExecuteAAF) associated with the aafComparePeriodPvt constant.

New in version 22

Compare the periods

This option is used to compare the data displayed in the Pivot Table control over 2 periods.

When selecting this option:

A window is displayed, allowing you to enter the comparison characteristics:

The validation of this window recalculates the displayed data, to only get the comparison data. For example:

To restore the standard display of Pivot Table control, select "Cancel the comparison of periods" from the popup menu of control.

Note: To start the comparison of periods by programming, use AAFExecute (or ExecuteAAF) associated with the aafComparePeriodPvt constant.

Compare the periods

This option is used to compare the data displayed in the Pivot Table control over 2 periods.

When selecting this option:

A window is displayed, allowing you to enter the comparison characteristics:

The validation of this window recalculates the displayed data, to only get the comparison data. For example:

To restore the standard display of Pivot Table control, select "Cancel the comparison of periods" from the popup menu of control.

Note: To start the comparison of periods by programming, use AAFExecute (or ExecuteAAF) associated with the aafComparePeriodPvt constant.

Exporting the table to Excel

This option is used to export the content of the pivot table to Excel. This option corresponds to PVTToExcel. All you have to do is specify the name of the XLS file to create. The created document can be directly opened. The content of created XLS document exactly corresponds to the data displayed in the pivot table. The hidden or collapsed columns will not be visible in the XLS file.

Loading/Saving the content of pivot table

To avoid recalculating a pivot table that was displayed beforehand, you have the ability to save the content of the pivot table in a file on disk in order to re-open it later to to send it to someone else. This option corresponds to PVTSave.

Caution: The created file can only be opened on the same kind of pivot table (with matching columns and rows).

Versions 19 and laterNote: The backup file can be encrypted by a password.

When saving the file, all you have to do is check the option "Encrypt with password" when typing the name of the backup file. In this case, during the validation, a new window asks for the password to use:

When loading the file, this same window is displayed if the file is encrypted. All you have to do is type the corresponding password.

The window for epassword input is available in English and in French.

New in version 19Note: The backup file can be encrypted by a password.

When saving the file, all you have to do is check the option "Encrypt with password" when typing the name of the backup file. In this case, during the validation, a new window asks for the password to use:

When loading the file, this same window is displayed if the file is encrypted. All you have to do is type the corresponding password.

The window for epassword input is available in English and in French.

Note: The backup file can be encrypted by a password.

When saving the file, all you have to do is check the option "Encrypt with password" when typing the name of the backup file. In this case, during the validation, a new window asks for the password to use:

When loading the file, this same window is displayed if the file is encrypted. All you have to do is type the corresponding password.

The window for epassword input is available in English and in French.

Restore the size and the initial groupings

This option is used to restore the default setting of pivot table.

Versions 19 and laterPopup menu of a cell: change the background colorThe popup menu of a cell allows the user to easily change the color of a cell found in the Pivot Table control. This allows you to easily identify the important elements in the pivot table.

Versions 20 and laterTip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

New in version 20Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

When saving the content of the pivot table (with GUI), the specified colors are saved.

New in version 19Popup menu of a cell: change the background colorThe popup menu of a cell allows the user to easily change the color of a cell found in the Pivot Table control. This allows you to easily identify the important elements in the pivot table.

Versions 20 and laterTip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

New in version 20Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

When saving the content of the pivot table (with GUI), the specified colors are saved.

Popup menu of a cell: change the background colorThe popup menu of a cell allows the user to easily change the color of a cell found in the Pivot Table control. This allows you to easily identify the important elements in the pivot table.

Versions 20 and laterTip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

New in version 20Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

Tip: The last color used is automatically proposed in the popup menu of the cell. Useful to apply the same color to several cells.

When saving the content of the pivot table (with GUI), the specified colors are saved.

Setting

Configuring the automatic menu in a Pivot Table control

The popup menu of a Pivot Table control can be disabled. To configure the display of the popup menu, you must:

Open the description window of the Pivot Table control.

Select the "GUI" tab.

Choose the requested menu in the "Popup Menu" combo box. You can:

"Display the menu of AAF (System)": In this case, the popup menu displayed will be the automatic menu of Pivot Table control (menu of AAFs). See Configuring the menu of AAFs for more details.

"Add a popup menu": If this option is checked, you will have the ability to select the custom popup menu to display.

Notes:

If both options are checked, the custom popup menu can be added before or after the menu of AAFs.

If no option is selected, no popup menu will be selected. The <Disabled> option will be displayed in the description window.

..PopupMenu can also be used to delete the popup menu specific to the AAFs and to restore the standard Windows menu for a specific control.