You are here:

Pivot Table

Overview

Pivot Table is a tool for summarizing and showing your data in different slices for statistical insights. Using this tool, you can invoke the displaying of your data with various sorting, grouping, and filtering.

A Pivot Table filled with data is a multidimensional table showing the relation of different entities. For example, here is the simple table showing dependencies between a tip size, meal time, and a day of the week:

The table is flexible in configuration: you can add numeric and categorical dimensions and place them in rows and columns to get every angle of the statistical information.

The features of the Pivot Table include:

Heatmaps
TSV display
Charts of several types
Functions to process the data with

Note: The performance of the Pivot Table depends on the system and browser used. As a general rule, we recommend for the RAM dedicated to processing the Pivot Table alone to be more than 3 GB (in Google Chrome). If the Pivot Table has more than 5 row and 5 column groups, the memory used may be more than 7 GB.

Return to top

Pivot Table Controls

The upper-left corner of a table holds a set of controls. Those are:

1 - The Table View. The options are the following:

Table (as shown in the Overview section above);
Table Heatmap: The cells in the table will have a certain colour hue according to the values inside (the faintest for the smallest value, the most intense colour for the biggest value):

Table Col Heatmap: The highlighting with colour will be applied to the cells with the biggest values of each column:

Table Row Heatmap: The highlighting with colour will be applied to the biggest values of each row:

Exportable TSV: This view presents an array of Tab-separated values:

Grouped Column Chart:

Stacked Column Chart:

Grouped Bar Chart:

Stacked Bar Chart:

Line Chart;
Dot Chart:

Area Chart:

Scatter Chart:

Multiple Pie Chart:

2 - The Bucket: this area serves as a storage for all available categories that you can add to the table to slice the data. By default, when a certain JSON or CSV file is opened in the Pivot Table, the widget analyzes the file and takes the names for those categories from the column names.

3 - Aggregate Function: Here you can set a function to process the data. The functions to choose from are:

Count
Count Unique Values
List Unique Values
Sum
Integer Sum
Average
Median
Sample Variance
Sample Standard Deviation
Minimum
Maximum
First
Last
Sum Over Sum
Sum as Fraction of Total
Sum as Fraction of Rows
Sum as Fraction of Columns
Count as Fraction of Total
Count as Fraction of Rows
Count as Fraction of Columns

In some cases, the aggregation function will have a child aggregation function for more detailed output in the table.

4 - Sorting buttons: You can control the sorting in your table using those buttons.

5 - Columns Area: You can drag-and-drop the items here from the Bucket to add a dimension to be displayed in the table as a column.

6 - Rows Area: You can drag-and-drop the items here from the Bucket to add a dimension to be displayed in the table as a row.

7 - Table Fields: This is the area where the total value is displayed, as well as the row and column totals and separate values after you add the items to the Rows Area and Columns Area.

Note: You can also add one category to both Areas if needed. To invoke that kind of display, you must have that category already dropped into one of the Areas. Then, pick the item from that Area and drag-and-drop it to another Area. The category will now be displayed in both rows and columns.

Return to top

Adding a Pivot Table to The Form

For you to add a Pivot Table to your form, your project must contain:

an empty .4gl file
an empty .fm2 file
a file containing the data for the table (JSON or CSV file)

Step 1. Create the MAIN block in your .4gl file, and fill it with the following definitions:

DEFINE pt ui.PivotTable

DEFINE contentAsString STRING #Pivot Table accepts STRING data type

DEFINE content TEXT

Step 2. Next, within the MAIN block, invoke a window opening with the Pivot Table methods, create the menu actions, and specify the JSON file’s name:

OPEN WINDOW w WITH FORM 'pivot_table_ex'

LET pt = ui.PivotTable.ForName('pt1') #refer the Pivot Table widget by its ID (in this example, a default ID)

ON ACTION 'PIVOT_TABLE_UPDATED'

DISPLAY pt.getPivotTableConfig()

ON ACTION 'sample data JSON'

LOCATE content IN FILE "sample-data.json"

LET contentAsString = content

CALL pt.setPivotTableDataType('JSON') #set a format for data

CALL pt.setPivotTableData(contentAsString) #specify the data to use

ON ACTION 'exit'

EXIT MENU

Note: The form you specify at the beginning of Step 2 is a .fm2 file in your program, and the JSON file is the file with source data to be displayed in the table.

The action 'PIVOT TABLE UPDATED' invokes the display of the current table configuration when the table has the Columns and Rows areas filled.

To display a CSV file in the Pivot Table, use the following template:

ON ACTION 'people100 CSV'

LOCATE content IN FILE "csv/people-100.csv"

LET contentAsString = content

CALL pt.setPivotTableDataType('CSV')

CALL pt.setPivotTableData(contentAsString)

Step 3. Open the .fm2 file in the Form Builder and add a Root Container:

After you add the Root Container, the Form Object will expand and display the Root Container.

Step 4. Pick the PivotTable widget from the Containers area and drag-and-drop it into the Root Container:

After that, the form will display the Pivot Table’s basic layout:

The Pivot Table will be assigned a default identifier pt1. Make sure that you use this exact identifier in the .4gl code.

The XML code of the .fm2 after the aforementioned steps file will look as follows:

<form.rootContainer>

<GridPanel.gridColumnDefinitions>

</GridPanel.gridColumnDefinitions>

<GridPanel.gridRowDefinitions>

</GridPanel.gridRowDefinitions>

</GridPanel>

</form.rootContainer>

</form>

Return to top