4.3. Using ShowPivotAction

ShowPivotAction is a special action that enables exporting data from the components that extend ListComponent, such as Table, Tree, and DataGrid, to a pivot table. It provides a simple means of BI analysis without any additional application components.

The action should be created programmatically in the screen controller and used, for example, in a button:

ShowPivotAction showPivotAction = new ShowPivotAction(tipsGroupTable);
exportButton.setAction(showPivotAction);

ShowPivotAction has two modes for export: all rows and selected rows. If no rows are selected, all rows will be exported by default without confirmation.

The editable PivotTable component will be shown in a new tab. By default, all the attributes included in the component’s data container view will be displayed, except the following:

  • attributes of Collection type;

  • attributes of byte array type;

  • UUID attributes;

  • annotated with @SystemLevel.

If you want to exclude certain attributes or include only a part of them, it can be done with the following fluent API methods:

  • withIncludedProperties(), for example:

    ShowPivotAction showPivotAction = new ShowPivotAction(tipsGroupTable)
        .withIncludedProperties(Arrays.asList("sex", "smoker", "day", "totalBill"));

  • withExcludedProperties(), for example:

    ShowPivotAction showPivotAction = new ShowPivotAction(tipsGroupTable)
        .withExcludedProperties(Arrays.asList("sex", "smoker"));

These methods take the list of properties names, all incorrect properties names will be ignored.

You can change the default configuration of the pivot table using the withNativeJson() method that receives a JSON String. Note that the localized property names should be used:

ShowPivotAction showPivotAction = new ShowPivotAction(tipsGroupTable)
        .withNativeJson("{" +
                " \"cols\": [\"Time\", \"Day\"]," +
                " \"rows\": [\"Sex\", \"Smoker\"]," +
                " \"editable\": false," +
                " \"renderer\": \"heatmap\"," +
                " \"aggregation\": {" +
                " \"id\": \"d8fc3fdf-730d-c94f-a0c8-72a9ce3dcb3a\"," +
                " \"mode\": \"count\"," +
                " \"properties\": [\"Sex\"]" +
                " }" +
                " }");

Below is the JSON structure for a non-editable pivot table:

{
        "cols": ["localized property", "localized property"],
        "rows": ["localized property"],
        "editable": false,
        "renderer": "heatmap",
        "aggregation": {
                "id": "d8fc3fdf-730d-c94f-a0c8-72a9ce3dcb3a",
                "mode": "sumOverSum",
                "properties": ["localized property", "localized property"]
        }
}

And here’s the JSON structure for an editable pivot table:

{
        "cols": ["localized property"],
        "rows": ["localized property"],
        "editable": true,
        "renderers": {
                "selectedRenderer": "barChart"
        },
        "autoSortUnusedProperties": true,
        "aggregationProperties": ["localized property", "localized property"],
        "aggregations": {
                "selectedAggregation": "count",
                "aggregations": [{
                        "id": "647780f0-c6d0-6ade-a63a-542b5c8cdbd5",
                        "mode": "count",
                        "caption": "Count"
                }, {
                        "id": "c2663238-2654-67f0-2dec-add6962d867c",
                        "mode": "sumOverSum"
                }]
        }
}

The displayed pivot data can be easily exported to Excel (if the current renderer is supported). The corresponding button will be displayed by default in the opened tab.