Examples

Creating PivotTable with Data from Entity

In this example, we will create a PivotTable that retrieves data from entities, so the dataContainer attribute should be defined.

Creating Entity

First, create the following enumerations:

Sex with the values: MALE, FEMALE
Day with the values: SUN, MON, TUE, WED, THU, FRI, SAT
Time with the values: DINNER, LUNCH

Let’s create the Tip entity class with the following attributes:

totalBill of the type Double
tip of the Double type
sex of the Sex enum
smoker of the Boolean type
day of the Day enum
time of the Time enum
size of the Integer type

Tip entity creation is now complete.

Screen XML Descriptor

Create a new blank pivot-sample-screen screen and replace the code in the XML descriptor with the following:

<window xmlns="http://jmix.io/schema/ui/window"
        xmlns:pivot="http://jmix.io/schema/ui/pivot-table">
    <data>
        <collection id="tipsDc" class="pivottable.ex1.entity.TipInfo"/>
    </data>
    <layout>
        <pivot:pivotTable id="tipsPivotTable"
                          dataContainer="tipsDc">
            <pivot:properties>
                <pivot:property name="totalBill"/>
                <pivot:property name="tip"/>
                <pivot:property name="sex"/>
                <pivot:property name="smoker"/>
                <pivot:property name="day"/>
                <pivot:property name="time"/>
                <pivot:property name="size"/>
            </pivot:properties>
            <pivot:aggregation mode="SUM_OVER_SUM">
                <pivot:property name="tip"/>
                <pivot:property name="totalBill"/>
            </pivot:aggregation>
            <pivot:rows>
                <pivot:row value="sex"/>
                <pivot:row value="smoker"/>
            </pivot:rows>
            <pivot:columns>
                <pivot:column value="day"/>
                <pivot:column value="time"/>
            </pivot:columns>
            <pivot:sortersFunction>
                function(attr){
                    if(attr=="Day"){
                        return $.pivotUtilities.sortAs(["Mon","Tue","Wed","Thu","Fri","Sat","Sun"]);
                    }
                }
            </pivot:sortersFunction>
        </pivot:pivotTable>
    </layout>
</window>

Screen Controller

Open the PivotSampleScreen screen controller and replace its content with the following code:

@UiController("sample_PivotSampleScreen")
@UiDescriptor("pivot-sample-screen.xml")
public class PivotSampleScreen extends Screen {
    @Autowired
    private CollectionContainer<TipInfo> tipsDc;

    @Subscribe
    protected void onInit(InitEvent event) {
        List<TipInfo> items = new ArrayList<>();
        items.add(tips(16.99, 1.01, Sex.FEMALE, false, Day.FRIDAY, Time.DINNER, 2));
        items.add(tips(10.34, 1.66, Sex.FEMALE, true, Day.THURSDAY, Time.LUNCH, 3));
        items.add(tips(21.01, 3.5, Sex.MALE, true, Day.FRIDAY, Time.LUNCH, 3));
        items.add(tips(23.68, 3.31, Sex.FEMALE, false, Day.MONDAY, Time.DINNER, 2));
        items.add(tips(24.59, 3.61, Sex.MALE, false, Day.TUESDAY, Time.LUNCH, 4));
        tipsDc.setItems(items);
    }

    private TipInfo tips(double totalBill, double tip, Sex sex, Boolean smoker, Day day, Time time, int size) {
        TipInfo tips = new TipInfo();
        tips.setTotalBill(totalBill);
        tips.setTip(tip);
        tips.setSex(sex);
        tips.setSmoker(smoker);
        tips.setDay(day);
        tips.setTime(time);
        tips.setSize(size);
        return tips;
    }

}

Result

Now let’s see how the created screen looks in the application.

Custom Aggregator and Derived Properties

This example of the pivotTable differs from the example above in the custom aggregator and derived properties added in the screen controller.

Screen XML Descriptor

In this example, we move the sorter function to the screen controller, so XML descriptor can look like this:

<pivot:pivotTable id="tipsCustomAggregatorPivotTable"
                  dataContainer="tipsDc">
    <pivot:properties>
        <pivot:property name="totalBill"/>
        <pivot:property name="tip"/>
        <pivot:property name="sex"/>
        <pivot:property name="smoker"/>
        <pivot:property name="day"/>
        <pivot:property name="time"/>
        <pivot:property name="size"/>
    </pivot:properties>
    <pivot:aggregation mode="SUM_OVER_SUM" custom="true">
        <pivot:property name="tip"/>
        <pivot:property name="totalBill"/>
    </pivot:aggregation>
    <pivot:rows>
        <pivot:row value="sex"/>
        <pivot:row value="smoker"/>
    </pivot:rows>
    <pivot:columns>
        <pivot:column value="day"/>
        <pivot:column value="time"/>
    </pivot:columns>
</pivot:pivotTable>

Screen Controller

Sorting and aggregation functions can be set either in the XML descriptor or in the screen controller. In this example, JavaScript functions are passed as parameters to the JsFunction class constructor.

Derived properties can be defined in the screen controller as well.

@Autowired
private PivotTable tipsCustomAggregatorPivotTable;

@Subscribe
protected void onInit(InitEvent event) {
    // ...
    tipsCustomAggregatorPivotTable.setSortersFunction(
            new JsFunction("function(attr){if(attr == \"Day\"){return $.pivotUtilities.sortAs([\"Mon\",\"Tue\",\"Wed\",\"Thu\",\"Fri\",\"Sat\",\"Sun\"]);}}"));

    tipsCustomAggregatorPivotTable.getAggregation().setFunction(
            new JsFunction("$.pivotUtilities.aggregators[\"Sum\"]([\"Tip\"])"));

    DerivedProperties derivedProperties = new DerivedProperties();
    derivedProperties.addAttribute("Smokes",
            new JsFunction("function(record) {return record.Smoker == \"Yes\" ? \"True\" : \"False\";}"));
    tipsCustomAggregatorPivotTable.setDerivedProperties(derivedProperties);
}

Result

Now let’s see how the created screen looks in the application.

Editable PivotTable

LIVE DEMO

In this example, we make the pivotTable editable and set the default aggregation function. In the editable mode, we can change the appearance (chart type) and the table content (rows and columns) directly in the UI.

Let’s see how the editable pivot table looks in the application.

Was this page helpful?

Thank you for your feedback

Something went wrong