- Features of the context menu
- Delete columns and rows
- Collapse/Expand columns or rows
- Add groupings: weekly, fortnightly, half-yearly, etc.
- Rearrange headers of rows and columns
- Transpose rows and columns
- Compare date ranges
- Export the pivot table to Excel
- Load/Save the content of the pivot table
- Restore the initial size and groups
- Cell context menu: change background color
WINDEV
WEBDEV
WINDEV Mobile
Others
Discover all the possibilities of pivot tables
The Pivot Table control and its context menu offer many possibilites.
You can:
- resize the first column of the pivot table 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.
- perform a search in the cells of a pivot table by pressing Ctrl + F.
- use the Page Up and Page Down keys to scroll through the data displayed in the pivot table. You can also use the Home and End keys or the Ctrl + Home and Ctrl + End key combinations.
The context menu of rows and columns includes the following options:
- Remove or redisplay multiple columns
- Collapse or expand all columns,
- Expand a level and all its sub-levels,
- Add groups (week/month/quarter),
- Rearrange headers of rows and columns,
- Transpose rows and columns,
- Compare date ranges
- Copy one or more rows,
- Export the contents of the pivot table to an Excel document,
- Load/Save the content of the pivot table,
- Restore the initial size and groups,
- Print the control content.
A cell-specific context menu includes the following options:
Delete 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 rows.
When deleted, the contents of the dynamic array are recalculated: totals are updated.
The deleted columns and/or rows can be redisplayed at any time (via the "Redisplay all deleted columns" option, for example). On redisplay, the contents of the dynamic array are recalculated: totals are updated..
Caution: If a column or row is not repeated, the only possible action is to delete the selected column or row.
Collapse/Expand columns or rows
For a Pivot Table with many elements and subelements, you can:
- collapse or expand all the columns and/or rows in a single action.
- expand a level and all its sublevels in a single action (in columns or rows).
For example:
- If all the columns are unfolded, the crosstab is very detailed: all the intermediate results are visible, from the highest to the lowest level..
- If all the columns are collapsed, the crosstab is synthetic: it shows only the first-level calculations..
Add groupings: weekly, fortnightly, half-yearly, etc.
If the pivot table does not contain enough details, you can add columns to enhance statistics.
The context menu of the columns includes the "Granularity" option. This option can be used to add groupings that are not displayed, e.g. Semester, Quarter, Fortnight, etc.
Warning: this option requires the dynamic array to be recalculated.. If several operations must be performed, the data calculation can be deferred.
Rearrange headers of rows and columns
This option changes the display mode of rows and columns in the pivot table.
When this option is selected, the pivot table becomes editable.
You can:
- show or hide rows or columns.
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". - invert dimensions. Simply drag and drop the header onto the desired position.
Transpose rows and columns
This option rearranges the data of the pivot table. Columns switch to rows and vice versa.Compare date ranges
This option is used to compare the data displayed in the pivot table 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.
- Evolution between years.
- When this window is validated, the data is recalculated to show only the data in the comparison. 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 %.
Export the pivot table to Excel
This option is used to export the content of the pivot table to Excel. All you have to do is specify the name of the XLS file to create. The created document can be directly opened. The new XLS document contains the data of the pivot table. The hidden or collapsed columns will not be visible in the XLS file.
Load/Save the content of the pivot table
To avoid recalculating a pivot table 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.
The created file can be opened again later.
Please note: The file created can only be opened on a crosstab of the same type (columns and rows must match).
Note: The backup file can be password-encrypted..
- When saving the file, simply check "Encrypt with password" when typing the name of the backup file. In this case, a new window will prompt you to enter the password to be used:
- When you load the encrypted file, the same window will appear again. Simply specify the corresponding password.
Restore the initial size and groups
This option is used to restore the default settings of the pivot table.
Cell context menu: change background color
Users can easily change the color of a cell in a pivot table via the context menu. This makes it easier to identify important elements in the pivot table.
Tip: The last color used is automatically proposed in the cell's context menu.. This feature is useful to apply the same color to multiple cells.
When saving the content of the pivot table (with UI), the specified colors are saved.