Displaying PivotTable - CUBA Platform. Displaying Charts And Maps

3. Displaying PivotTable

PivotTable is a table component with drag-and-drop functionality that enables turning a data set into a summary table and manipulate it using 2D drag-and-drop UI. It is fully available via the CUBA Studio components library.

PivotTable is based on the external JavaScript library - https://github.com/nicolaskruchten/pivottable. You can find more examples of PivotTable on its author’s website: http://nicolas.kruchten.com/pivottable/examples/.

XML-name of the component: pivotTable

The component is implemented for the Web Client only.

An example of component definition in an XML-descriptor of a screen:

<chart:pivotTable id="tipsPivotTable"
                  datasource="tipsDs"
                  renderer="HEATMAP">
    <chart:properties>
        <chart:property name="row"/>
        <chart:property name="totalBill"/>
        <chart:property name="tip"/>
        <chart:property name="sex"/>
        <chart:property name="smoker"/>
        <chart:property name="day"/>
        <chart:property name="time"/>
        <chart:property name="size"/>
    </chart:properties>
    <chart:aggregation mode="SUM_OVER_SUM">
        <chart:property name="tip"/>
        <chart:property name="totalBill"/>
    </chart:aggregation>
    <chart:rows>
        <chart:row value="sex"/>
        <chart:row value="smoker"/>
    </chart:rows>
    <chart:columns>
        <chart:column value="day"/>
        <chart:column value="time"/>
    </chart:columns>
    <chart:sortersFunction>
        function(attr){
            if(attr=="Day"){
                return $.pivotUtilities.sortAs(["Mon","Tue","Wed","Thu","Fri","Sat","Sun"]);
            }
        }
    </chart:sortersFunction>
</chart:pivotTable>

pivotTable elements

properties - a key-value map with the set of properties to be used in the pivotTable, where the key is the name of an attribute from the datasource, and the value is its localized caption.

derivedProperties - can be used to add new attributes to the original data set, derived from the existing ones. This element is a key-value map, where the key is the name of a generated attribute, and the value is a JavaScript function that generates this attribute.
- the enclosed derivedProperty elements should have the caption attribute defined, as the caption’s value will be used as the key.
- the function element is used as the value for the derivedProperty.

hiddenProperties - the list of attributes that should not be displayed in the UI. Its value can be either a properties key or a generated attribute’s name (i.e. the derivedProperties key). Applicable for editable pivotTable only.

hiddenFromAggregations - an array of attributes to omit from the aggregation arguments dropdowns.

hiddenFromDragDrop - a list of attributes to omit from the drag-and-drop portion of the UI.

columns - the list of attributes to be used as table columns. Its value can be either a properties key or a generated attribute’s name.

columnOrder - the order in which column data is provided to the renderer.

rows - the list of attributes to be used as table rows. Its value can be either a properties key or a generated attribute’s name.

rowOrder - the order in which row data is provided to the renderer.

exclusions - a key-value map where the key is the names of attributes (either a properties key or a generated attribute’s name), and the value is the list of these attributes' values to be excluded from rendering. Only for editable pivotTable.

inclusions - a key-value map where the key is the names of attributes (either a properties key or a generated attribute’s name), and the value is the list of these attributes' values to be rendered. Only for editable pivotTable.

filterFunction - JavaScript function that will be used for filtration.

renderers - defines the collection of rendering functions that should be displayed in the list of available renderers in the UI.
- default attribute enables setting one of predefined renderers. The selected renderer will be used as default when the component is loaded.
- enclosed renderer element enables setting one of predefined renderers using its type attribute: AREA_CHART, BAR_CHART, COL_HEATMAP, HEATMAP, HORIZONTAL_BAR_CHART, HORIZONTAL_STACKED_BAR_CHART, LINE_CHART, ROW_HEATMAP, SCATTER_CHART, STACKED_BAR_CHART, TABLE_BAR_CHART, TABLE, TREEMAP, TSV_EXPORT.
Only for editable pivotTable.

rendererOptions - defines the renderers' options. Actually only two renderer types can be customized:
- all kinds of heatmap. The cell colours can be set by the Javascript code.
- all kinds of charts. Options can be used to set the chart’s size.

sortersFunction - JavaScript function that will be used for rows and columns captions sorting.
Aggregation elements of pivotTable
aggregation - sets up a function which will aggregate results per cell.

aggregation attributes:

mode attribute enables setting one of predefined aggregation functions.

caption is a localized value to be displayed in the UI.

custom - if true, the mode value is ignored in favor of the javaScript code from the enclosed function element.

aggregation elements:

function - contains JavaScript code of an aggregation function.

property - the list of attributes to be used as input parameters of the aggregation function. Its value can be either a properties key or a generated attribute’s name. Only for non-editable pivotTable.

For example:

<chart:aggregation mode="SUM_OVER_SUM" custom="true"> <chart:property name="tip"/> <chart:property name="Total Bill"/> </chart:aggregation>

aggregationProperties - defines the list of attributes that should be displayed in the dropdown lists of aggregators. Its value can be either a properties key or a generated attribute’s name. Only for editable pivotTable.

<chart:aggregationProperties> <chart:property name="tip"/> <chart:property name="totalBill"/> </chart:aggregationProperties>

aggregations - defines the collection of aggregators that should be displayed in the dropdown list of available aggregators in the UI.

aggregations attributes:

default attribute enables setting one of predefined aggregation functions. The selected function will be used as default when the component is loaded.

enclosed aggregation element is used in the same way as aggregation except for the enclosed property element. Only for editable pivotTable.

For example:

<chart:aggregations default="COUNT"> <chart:aggregation caption="Count"/> <chart:aggregation mode="SUM_OVER_SUM"/> </chart:aggregations>

pivotTable attributes

datasource - sets a data source defined in the dsContext section of the screen XML descriptor. The collectionDatasource type is required.

editable - if true, the elements for manipulation with data will be displayed in the UI, otherwise only the data will be displayed.

menuLimit - maximum number of values displayed in the double-click menu. If the number of records is exceeded, the corresponding message appears. Only for editable pivotTable.

renderer - enables setting one of the predefined data renderers. Only for non-editable pivotTable.

autoSortUnusedProperties - defines if unused attributes should be sorted in the UI. Only for editable pivotTable.

unusedPropertiesVertical - defines if unused attributes should be displayed vertically (if true) or horizontally (if false, or by default). If set to a number, then if the attributes names' combined length in characters exceeds this number, the attributes will be shown vertically.

Listeners of pivotTable:

addCellClickListener - adds a listener to the PivotTable cell click events. The CellClickEvent is fired only for table renderers (TABLE, HEATMAP, TABLE_BAR_CHART, COL_HEATMAP, ROW_HEATMAP).
```
tipsPivotTableUI.addCellClickListener(event -> {
    showNotification("Value: " + event.getValue() + ",\n"
        + "Filters applied: " + event.getFilters());
});
```

addRefreshListener - adds a listener to the PivotTable refresh events. The RefreshEvent is fired only for editable PivotTable.
```
tipsPivotTableUI.addRefreshListener(event -> {
    showNotification("Row order :" + event.getRowOrder() + ",\n"
        + "Inclusions: " + event.getInclusions());
});
```
The following values are available from the RefreshEvent: aggregation, aggregationProperties, columns, columnOrder, exclusions, inclusions, renderer, rowOrder, rows.

Attributes of pivotTable: autoSortUnusedProperties - datasource - editable - height - menuLimit - renderer - unusedPropertiesVertical - width
Elements of pivotTable: aggregation - aggregationProperties - aggregations - columnOrder - columns - derivedProperties - exclusions - filterFunction - hiddenFromAggregations - hiddenFromDragDrop - hiddenProperties - inclusions - properties - rendererOptions - renderers - rowOrder - rows - sortersFunction
API: addCellClickListener - addRefreshListener