SAP Business One Service Layer: Aggregation

In this blog, I would like to share a functionality released in Service Layer since SAP Business One 9.1, version for SAP HANA PL12. It is about the oData aggregation, which is an important extension to the original oData protocol.

Aggregation behavior is triggered using the query option $apply. Any aggregate expression that specifies an aggregation method MUST define an alias for the resulting aggregated value. Aggregate expressions define the alias using the as keyword, followed by a Simple Identifier. The alias will introduce a dynamic property in the aggregated result set. The introduced dynamic property is added to the type containing the original expression.

Currently, the supported aggregation methods include sum, avg, min, max, count and distinctcount.

1. sum

The standard aggregation method sum can be applied to numeric values to return the sum of the non-null values, or null if there are no non-null values.

For example, to sum the DocRate of the Orders, send a request like:

GET /b1s/v1/Orders?$apply=aggregate(DocRate with sum as TotalDocRate)

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(TotalDocRate)”,

“value”: [

{

“odata.id”: null,

“TotalDocRate”: 4

}

]

}

The equivalent SQL on HANA is:

SELECT SUM(T0."DocRate") AS "TotalDocRate" FROM "ORDR" T0

2 average

The standard aggregation method average can be applied to numeric values to return the sum of the non-null values divided by the count of the non-null values, or null if there are no non-null values.

For example, to calculate the average VatSum of the Orders, send a request like:

GET /b1s/v1/Orders?$apply=aggregate(VatSum with average as AvgVatSum )

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(AvgVatSum)”,

“value”: [

{

“odata.id”: null,

“AvgVatSum”: 1.7

}

]

}

The equivalent SQL on HANA is:

SELECT AVG(T0."VatSum") AS "AvgVatSum" FROM "ORDR" T0

3 max

The standard aggregation method max can be applied to values with a totally ordered domain to return the largest of the non-null values, or null if there are no non-null values. The result property will have the same type as the input property.

For example, to get the maximum DocEntry of the Orders, send a request like:

GET /b1s/v1/Orders?$apply=aggregate(DocEntry with max as MaxDocEntry)

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(MaxDocEntry)”,

“value”: [

{

“odata.id”: null,

“MaxDocEntry”: 6

}

]

}

The equivalent SQL on SAP HANA is:

SELECT MAX(T0."DocEntry") AS "MaxDocEntry" FROM "ORDR" T0

4 min

The standard aggregation method min can be applied to values with a totally ordered domain to return the smallest of the non-null values, or null if there are no non-null values. The result property will have the same type as the input property.

For example, to get the minimum DocEntry of the Orders, send a request like:

GET/b1s/v1/Orders?$apply=aggregate(DocEntry with min as MinDocEntry)

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(MinDocEntry)”,

“value”: [

{

“odata.id”: null,

“MinDocEntry”: 2

}

]

}

The equivalent SQL on SAP HANA is:

SELECT MIN(T0."DocEntry") AS "MinDocEntry" FROM "ORDR" T0

5 countdistinct

The aggregation method countdistinct counts the distinct values, omitting any null values.

For example, to count the distinct CardCode of the Orders, send a request like:

GET /b1s/v1/Orders?$apply=aggregate(CardCode with countdistinct as CountDistinctCardCode )

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(CountDistinctCardCode)”,

“value”: [

{

“odata.id”: null,

“CountDistinctCardCode”: “2”

}

]

}

The equivalent SQL on SAP HANA is:

SELECT COUNT(DISTINCT T0."CardCode") AS "CountDistinctCardCode" FROM "ORDR" T0

6 count

The value of the virtual property $count is the number of instances in the input set. It MUST always specify an alias and MUST NOT specify an aggregation method.

For example, to count the number of Orders, send a request like:

GET /b1s/v1/Orders?$apply=aggregate($count as OrdersCount)

On success, the response is like:

{

“odata.metadata”: “$metadata#Orders(OrdersCount)”,

“value”: [

{

“odata.id”: null,

“OrdersCount”: 4

}

]

}

The equivalent SQL on SAP HANA is:

SELECT COUNT(T0."DocEntry") AS "OrdersCount" FROM "ORDR" T0

[Note] Aggregation combined with filter also works, as illustrated in the following examples:

Filter with built-in function

• URL: /b1s/v1/Orders?$apply=aggregate($count as OrdersCount)&$filter = contains(JournalMemo,’bp001′)
• SQL: SELECT COUNT(T0.”DocEntry”) AS “OrdersCount” FROM “ORDR” T0 WHERE T0.”JrnlMemo” Like ‘bp001’

Filter with compare operator

• URL: /b1s/v1/Orders?$apply=aggregate(DocEntry with min as MinDocEntry)&$filter = DocNum ge 3
• SQL: SELECT MIN(T0.”DocEntry”) AS “MinDocEntry” FROM “ORDR” T0 WHERE T0.”DocNum” >= 3

Filter the enumeration property by value

• URL: /b1s/v1/Orders?$apply=aggregate($count as OrdersCount)&$filter = DocType eq ‘I’
• SQL: SELECT COUNT(T0.”DocEntry”) AS “OrdersCount” FROM “ORDR” T0 WHERE T0.”DocType” = ‘I’

Filter the enumeration property by name

• URL: /b1s/v1/Orders?$apply=aggregate($count as OrdersCount)&$filter = DocType eq ‘dDocument_Items’
• SQL: SELECT COUNT(T0.”DocEntry”) AS “OrdersCount” FROM “ORDR” T0 WHERE T0.”DocType” = ‘I’

The above introduction is just a simple aggregation. In the subsequent patches, Service Layer is planned to enhance the aggregation by integrating it with other query options.