Versions Compared

Old Version 6

changes.mady.by.user Štěpán Vacek

Saved on

compared with

New Version 7

changes.mady.by.user Štěpán Vacek

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • AVG

  • COUNT

  • MAX

  • MIN

  • SUM

Definition

Code Block
breakoutModefull-width
languagegroovy
def groupByData = api.newMatrix()
             //Definition of result matrix (columns, rows).
             .withColumns('TextColumn1', 'TextColumn2', 'NumericColumn3', 'NumericColumn4')
             .withRows(entries)
             
             //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 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 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

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

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

  3. Property .withColumnAggregation defines that the values for subtotal rows 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 for the TextColumn1.

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

Info

Static import is required to use predefined enum with aggregation types.

Code Block
languagegroovy
 import static net.pricefx.server.dto.calculation.ResultMatrixGrouping.AggregateFunctionType.SUM

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

Code Block
languagegroovy
import net.pricefx.common.api.FieldFormatType

def productList = null

def filter = [

]

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

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

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