By utilizing the aggregation and grouping features, you can establish groups within the Result Matrix and subsequently perform aggregation operations on these groups, such as SUM, COUNT, etc.
| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Supported Aggregations
AVG
COUNT
MAX
MIN
SUM
...
| Code Block | ||
|---|---|---|
| ||
def groupByData = api.newMatrix()
// Definition of result matrix (columns, rows).
.withColumns('TextColumn1', 'TextColumn2', 'NumericColumn3', 'NumericColumn4')
.withRows(rows)
// Property `.withGroupBy` defines that the `TextColumn1` and `NumericColumn3` are used for group by (The order is important).
.withGroupBy(['TextColumn1', 'NumericColumn3'])
// Property `.withColumnAggregation` defines that the values for subtotal rows that will be calculated as SUM.
// Values are based on the group by definition (`NumericColumn3`). andThis aredefinition to be calculated as SUMis optional.
// This definition is optional. When not used, like e.g. for TextColumn1`TextColumn1`, there will be no aggregation calculated for the `TextColumn1`.*/
.withColumnAggregation('NumericColumn3', SUM)
// Property `.calculateGroupByData` is an end statement for group definition.
// This statement is only used together with `.withColumnAggregation`.
.calculateGroupByData() |
Description
...
Definition of result matrix (columns, rows).
...
Property .withGroupBy defines that the TextColumn1 and NumericColumn3 are used for the group by (The order is important).
...
( |
...
) |
...
...
Property .calculateGroupByData is an end statement for group definition. This statement is only used together with .withColumnAggregation.
| Info | |||||
|---|---|---|---|---|---|
Static import is required to use a predefined enum with aggregation types.
|
Examples
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 Block | ||
|---|---|---|
| ||
import net.pricefx.common.api.FieldFormatType
import static net.pricefx.server.dto.calculation.ResultMatrixGrouping.AggregateFunctionType
import static net.pricefx.server.dto.calculation.ResultMatrixGrouping.AggregateFunctionType.SUM
def listPriceItems = null
def filter = [
Filter.equal("name", "ListPrice"),
]
List<String> fields = ["ProductId", "ListPrice", "Currency"]
def listPriceItems = api.find("PX3", 0, api.getMaxFindResultsLimit(), "ProductId", fields, *filter)
def rows = listPriceItems
def columnFormats = [
"ProductId": FieldFormatType.TEXT,
"ListPrice": FieldFormatType.MONEY,
"Currency" : FieldFormatType.MONEY,
]
def resultMatrix = api.newMatrix()
.withEnableClientFilter(true)
.withColumnFormats(columnFormats)
.withRows(rows)
.withGroupBy(['Currency'])
.withColumnAggregation('ListPrice', SUM)
.calculateGroupByData()
return resultMatrix |
...