Each flexYgrid panel contains a built-in pivot table function that lets you build summarized reports of the data in your current grid panel. Using the cell values of your choice as source values, you can select and apply operators to customize the way these values will be displayed in the table. All data that is shown, and not expressly filtered out of your current flexYgrid panel, will be used in your pivot table.
The resulting pivot table grid will display aggregation of your data arranged according to the row and column information you have set.
|Note ||You can find more information about pivot tables, including a practical use case, in this Ytria tech lab article.|
Create Pivot Table
The Create Pivot Table command is found in both the toolbar and right-click menu.
Ctrl + Shift +P
|Create Pivot Table: This function will launch the Pivot Table Setup dialog where you can select the labels for the columns and rows, as well as set the values to use in building your pivot table. You can also select from different formatting options.|
Pivot Table Row and Column Structure
In the example pivot table shown above, an entire server has been analyzed to find the ODS versions of each database. The following characteristics are shown:
- Row Labels show the 'Database Path' and 'Database Filename'. These have a background color of yellow.
- Column Labels are the 'ODS Version'. These column headers contain much of the same functionality as normal flexYgrid column headers.
- Values that are shown are Physical Size and a Count Total for the ODS Versions found. These are the cell values with the white background.
- Subtotals are shown in turquoise. The delimitation of a subtotal is determined by the end of a specific set of data. In the example above, each 'Database Path' is considered a set.
- Totals for Rows and Columns are shown in orange. This will tally totals across all rows and columns in the pivot table.
- Grand totals have a background color of red.
The Pivot Table Setup Dialog
The Pivot Table Setup dialog is where you will select which columns to use as Column Labels, which columns to use a Row Labels, and the columns that will define which values to focus on when building your pivot table.
This is a list of currently available columns (properties) that can be used as Column and Row Labels, as well as to define the Values the pivot table will focus on.
'Show hidden columns': Use this option to include ALL columns in the 'Available Columns' list. By default, only the columns that are set as 'Visible' in your current flexYgrid panel will be shown. This setting can be saved as part of your Default pivot table configuration.
Filter, Sort, and Search functions are available for this list, as are some right-click menu options.
|Note ||Clicking the left/right arrows moves highlighted columns to and from selection boxes.|
To add columns: Select one or more columns and use the button.
To remove columns: Select one or more columns and use the button.
Label Selection Windows
These windows show the currently selected columns (properties) that have been selected to label Columns and Rows, as well as be the focus Values the will make up the pivot table.
Various formatting options are available uniformly throughout the selection windows, as are some Grid Tools & Options.
All selectable variables within these windows can be saved as part of your Default pivot table configuration.
white letters; the only way to create a space when published - why?!
Columns to use as Column Labels
This window shows the columns (properties) that are currently selected to be used as Column Labels in your pivot table.
Standard header formatting options are available.
The formatting on Column Labels takes place before any calculations in the pivot table and thus, in the case of number values, can affect the way that data is aggregated.
Columns to use as Row Labels
This window shows the columns (properties) that are currently selected to be used as Row Labels in your pivot table.
Standard header formatting options are available.
The formatting on Row Labels takes place before any calculations in the pivot table and thus, in the case of number values, can affect the way that data is aggregated.
Columns to use as Values
This window shows the currently selected columns (properties) that your pivot table will use as Values.
You can apply a variety of formatting options, as well as Unique Index references to your Values.
Standard filter and search options are also available.
- Show Totals: Displays Sub Total for Rows and Columns, as well as the Grand Total.
- Exclude from result any information filtered in the grid: If checked, this excludes information filtered from the original flexYgrid. If unchecked, the pivot table will be built from the entire unfiltered grid data.
Pivot Table Colors
You may select the colors you wish to use as background cell colors for the corresponding cell types here. These can saved as part of your default pivot table configuration.
Setup and Build
- Setup: Here you can select to save the current configuration as the default pivot table configuration you may also load the previously saved configuration (e.g. this will function as an "undo" feature by reverting your current pivot setup configuration to the default). You may also clear the current pivot setup configuration and when doing so you will have the choice to delete the saved default configuration file as well.
|Note ||You can set a default configuration for the pivot table builder in every flexYgrid panel. You must have a saved default configuration in order to load a default configuration.|
- Build: This launches the pivot table build.
- Cancel: This cancels your current pivot table session, and will clear all current modifications to the configuration, reverting to your default settings.