By utilizing the aggregation and grouping features, you can establish groups within the Result Matrix and subsequently perform aggregation operations on these groups. Such , such as SUM, COUNT, etc.

Supported Aggregations

...

1. Definition of result matrix (columns, rows).

2. Property .withGroupBy defines that the TextColumn1 and NumericColumn3 are used for the group by (The order is important).

3. Property .withColumnAggregation defines that the values for subtotal rows that will be calculated as SUM. Values are based on the group by definition (NumericColumn3) and are to be calculated as SUM. This definition is optional. When not used, like e.g. for TextColumn1, there will be no aggregation calculated for the TextColumn1.

4. Property .calculateGroupByData is an end statement for group definition. This statement is only used together with .withColumnAggregation.

 import static net.pricefx.server.dto.calculation.ResultMatrixGrouping.AggregateFunctionType.SUM

...

Grouping Example

In the following example, we retrieve the data from the Products table and group them by the Business Unit, Product Group, and Size columns.

Code

import net.pricefx.common.api.FieldFormatType

def productList = null

List<String> fields = ["ProductId", "label", "ProductGroup", "currency", "BusinessUnit", "ProductClass", "Size", "ProductLifeCycle" ]

productList = api.find("P", 0, api.getMaxFindResultsLimit(), "ProductId", fields)

def columnFormats = [
        "ProductId": FieldFormatType.TEXT,
        "label": FieldFormatType.TEXT,
        "ProductGroup": FieldFormatType.TEXT,
        "currency": FieldFormatType.MONEY_EUR,
        "BusinessUnit": FieldFormatType.TEXT,
        "ProductClass": FieldFormatType.TEXT,
        "Size": FieldFormatType.TEXT,
        "ProductLifeCycle": FieldFormatType.TEXT,
]

def rows = productList

def resultMatrix = api.newMatrix()
        .withColumnFormats(columnFormats)
        .withRows(rows)
        .withGroupBy(['BusinessUnit'])
        .withGroupBy(['ProductGroup'])
        .withGroupBy(['Size'])

return resultMatrix

...

Aggregation Example

In the following example, we retrieve the data from the Product Extensions table. We apply group by Currency column and then aggregate SUM on ListPrice column.

...