|
|
|
|
|
|
|
|
- Overview
- The features of popup menu
- Deleting the columns and the rows
- Collapsing/Expanding the columns or the rows
- Adding groupings: weekly, fortnightly, quarterly, ...
- Reorganize headers of rows and columns
- Reversing the row and column headers
- Compare date ranges
- Export the Pivot Table control to Excel
- Loading/Saving the content of pivot table
- Restore the size and the initial groupings
- Setting
- 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 Pivot Table control with the mouse (column containing the row headers).
- collapse/expand columns and rows by clicking the header icons "+" and "-".
- display a popup menu allowing him to perform several actions on the rows and columns.
- display a popup menu on the cells.
- select several rows, columns or cells via the Shift 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.
- perform a search in the cells of a Pivot Table control by pressing Ctrl + F.
The features of popup 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 groupings (week/month/quarter),
- Reorganize headers of rows and columns,
- Rotate 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 size and the initial groupings,
- Print the control content.
The features of popup 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. The features of popup menu 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.
- 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.
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 requires the recalculation of the pivot table. If several operations must be performed, the recalculation of data can be postponed. Reorganize 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 desired 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. Simply select the header, drag it and drop it at the desired position.
Reversing the row and column headers This option is used to make the data of the Pivot Table control turn around. All the columns become rows and all the rows become columns.
Remark: To reverse the rows and columns in a Pivot Table control by programming, use AAFExecute (or ExecuteAAF) with the aafPvtReverseRowColumn constant. Compare 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: - A window appears, allowing you to enter the characteristics of the comparison:
- Reference date range.
- Previous date range for comparison.
 New in version 25Evolution between years.
- The validation of this window recalculates the displayed data, to only get the comparison data. For example:
New in version 25If the evolution between years must be shown, it will appear in green or red, depending on whether it is positive or negative. The popup menu of the data offers the following two options to improve the comparison: - Show evolutions.
- Show evolutions in %.
To restore the standard Pivot Table control, select "Cancel date range comparison" in the popup menu. Remark: To compare date ranges by programming, use AAFExecute (or ExecuteAAF) with the aafCompareDateRangePvt constant. Export 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 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. 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, 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 password input window 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. Popup menu of a cell: change the background color
The 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.
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. 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 "UI" tab.
- Choose the desired 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.
Remarks: - 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 is also used to delete the popup menu specific to AAFs and to restore the standard Windows menu for a specific control.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|