- Features of the context menu
- Deleting columns and rows
- Collapsing/Expanding columns or rows
- Adding groups: weekly, fortnightly, quarterly, ...
- Reorganizing the headers of rows and columns
- Reversing the row and column headers
- Comparing date ranges
- Exporting the Pivot Table control to Excel
- Loading/Saving the content of the pivot table
- Restoring the initial size and groups
- Saving the size and groups
- Configuring the automatic menu in a Pivot Table control
Automatic features of Pivot Table controls
The Pivot Table control offers several Automatic Application Features (AAF) allowing the user to take advantage of all its capabilities.
The user can:
- resize the first column of the Pivot Table control with the mouse (column containing the row headers).
- collapse/expand columns and rows by clicking the header icons "+" and "-".
- open a context menu to perform several actions on rows and columns.
- open a context menu for cells.
- select several rows, columns or cells via the Shift and Ctrl keys. This option is available only if multiple selection is allowed in the control. For more details, see Description of a Pivot Table control.
- perform a search in the cells of a Pivot Table control by pressing Ctrl + F.
The features of context menu for the rows and columns are as follows:
- Delete/Redisplay one or more columns
- Collapse/Expand the columns,
- Expand a level and all its sub-levels,
- Add groups (week/month/quarter),
- Reorganize headers of rows and columns,
- Reverse rows and columns,
- Compare date ranges,
- Copy one or more rows,
- Export the contents of the Pivot Table control to an Excel document,
- Load/Save the content of the Pivot Table control,
- Restore the initial size and groups,
- Print the control content.
The features of context menu for the cells are as follows:
Remark: If the Pivot Table control allows for multiple selection, certain operations are available only for the selected rows, columns or cells.
Features of the context menu
Deleting columns and rows
Some pivot tables can contain a great number of rows and columns (if the control contains several hierarchy levels, for example).
To get a more concise display, you can:
- 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 columns or rows
For a Pivot Table with many elements and sub-elements, you can:
- collapse or expand all the columns and/or rows in a single action.
- expand a level and all its sub-levels in a single action (in column or in row).
- 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.
Adding groups: 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 context menu of columns includes the "Add" option. This option allows you to add groups that are not displayed, for example: Half-year, Quarter, Fortnight, ...
Caution: this option requires the recalculation of the pivot table. If several operations must be performed, the recalculation of data can be postponed.
Reorganizing 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.
- make rows or columns visible or not.
To show elements, simply select the hidden elements in "Headers not displayed" and move them to the desired location in the rows or columns.
To hide elements, simply select the corresponding header and move it to "Headers not displayed".
- reverse the dimensions. Simply select the header, drag it and drop it at the desired position.
Reversing the row and column headersThis option turns the data of the Pivot Table control around. All the columns become rows and all the rows become columns.
: To reverse the rows and the columns found in a Pivot Table control through programming, use AAFExecute
associated with the aafPvtReverseRowColumn
Comparing date ranges
This option is used to compare the data displayed in the Pivot Table control over 2 date ranges.
When this option is selected:
To restore the standard Pivot Table control, select "Cancel date range comparison" in the context menu.
- A window appears, allowing you to enter the characteristics of the comparison:
- Reference date range.
- Previous date range for comparison.
- Evolution between years.
- The validation of this window recalculates the displayed data, to only get the comparison data. For example:
If the evolution between years must be shown, it will appear in green or red, depending on whether it is positive or negative. The context menu of the data includes the following two options to improve the comparison:
- Show evolutions.
- Show evolutions in %.
: To compare date ranges through programming, use AAFExecute
with the aafCompareDateRangePvt
Exporting the Pivot Table control 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 the pivot table
To avoid recalculating a Pivot Table control displayed previously, the content of the control can be saved to a file on disk. This will allow users to open it later or send it to someone else. This option corresponds to PVTSave
The created file can be re-opened later (equivalent to PVTLoad
Caution: The created file can only be opened on the same kind of pivot table (with matching columns and rows).
Remark: The backup file can be encrypted by a password.
- When saving the file, simply check "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 password input window is available in English and in French.
Restoring the initial size and groups
This option is used to restore the default settings of the pivot table.
Saving the size and groups
This option saves the last settings of the pivot table. Thus, the next time the window is opened, these settings will be automatically applied.
The user no longer has to configure the control each time it is displayed.
Context menu of a cell: changing the background color
The context menu of a cell allows the user to easily change the color of a cell in the Pivot Table control. This makes it easier to identify important elements in the Pivot Table.
Tip: The last color used is automatically proposed in the context menu of the cell. Useful to apply the same color to several cells.
When saving the content of the pivot table (with UI), the specified colors are saved.
Configuring the automatic menu in a Pivot Table control
The context menu of a Pivot Table control can be disabled. To configure the display of the context menu, you must:
- Open the description window of the Pivot Table control.
- Select the "UI" tab.
- Choose the desired menu in the "Popup menu" combo box. You can:
- "Display the menu of AAF (System)": In this case, the automatic menu of the Pivot Table control will be used (AAF menu). For more details on how to configure this menu, see Configuring the AAF menu.
- "Add a context menu": If this option is checked, you can select the custom context menu to be displayed.
- If both options are checked, the custom context menu can be added before or after the AAF menu.
- If no option is selected, no context menu will be selected. The <Disabled> option will be displayed in the description window.
- The PopupMenu property is also used to remove the AAF-specific context menu and restore the standard Windows menu for a given control.
Click [Add] to post a comment