In this blog, I would like to share another new functionality released for Service Layer since SAP Business One 9.2, version for SAP HANA PL03. It is about the oData grouping, which is an important extension to the original oData protocol.
Grouping behavior is triggered using the query option apply and the groupby keyword. This keyword specifies the grouping properties, a comma-separated list of one or more single-valued property paths that is enclosed in parentheses. The same property path SHOULD NOT appear more than once; redundant property paths MAY be considered valid, but MUST NOT alter the meaning of the request.
1 Simple group
Simply enclose the group properties with parentheses. For example, to group the orders by CardCode, DocEntry, send a request like:
GET /b1s/v1/Orders?$apply=groupby((CardCode, DocEntry))
Or
/b1s/v1/Orders?$apply=groupby((Orders/CardCode, Orders/DocEntry))
On success, the response is like:
{
"odata.metadata": "$metadata#Orders(CardCode, DocEntry)",
"value": [
{
"odata.id": null,
"CardCode": "c001",
"DocEntry": 2
},
{
"odata.id": null,
"CardCode": "c002",
"DocEntry": 3
},
{
"odata.id": null,
"CardCode": "c001",
"DocEntry": 5
},
{
"odata.id": null,
"CardCode": "c001",
"DocEntry": 6
}
]
}
The equivalent SQL on HANA is:
SELECT T0."CardCode", T0."DocEntry" FROM "ORDR" T0 GROUP BY T0."CardCode", T0."DocEntry"
2 Group with aggregation method
SL also supports to combine grouping with aggregation. For example, to aggregate the DocNum property on grouping CardCode, send a request like:
GET /b1s/v1/Orders?$apply=groupby((CardCode), aggregate(DocNum with sum as TotalDocNum))
On success, the response is like:
{
"odata.metadata": "$metadata#Orders(CardCode,TotalDocNum)",
"value": [
{
"odata.id": null,
"CardCode": "c001",
"TotalDocNum": 8
},
{
"odata.id": null,
"CardCode": "c002",
"TotalDocNum": 2
}
]
}
The equivalent SQL on HANA is:
SELECT T0."CardCode", SUM(T0."DocNum") AS "TotalDocNum" FROM "ORDR" T0 GROUP BY T0."CardCode"
3 Group with aggregation method and filter
SL allows to filter before grouping. These two operations are separated by a forward slash to express that they are consecutively applied. For example, to filter before grouping with aggregation method, send a request like:
GET /b1s/v1/Orders?$apply=filter(Orders/CardCode eq 'c001')/groupby((CardCode), aggregate(DocNum with sum as TotalDocNum))
On success, the response is like:
{
"odata.metadata": "$metadata#Orders(CardCode,TotalDocNum)",
"value": [
{
"odata.id": null,
"CardCode": "c001",
"TotalDocNum": 8
}
]
}
The equivalent SQL on HANA is:
SELECT T0."CardCode", SUM(T0."DocNum") AS "TotalDocNum" FROM "ORDR" T0 WHERE T0."CardCode" = 'c001' GROUP BY T0."CardCode"
[Note] The filter option can also be specified as below, which is functionally equivalent.
GET /b1s/v1/Orders?$apply=groupby((CardCode), aggregate(DocNum with sum as TotalDocNum))&$filter=(Orders/CardCode ne 'c001')
As you know, SAP HANA has excellent performance on the aggregation and grouping operation. Being able to expose this via Service Layer provides a convenient way for customers to better leverage this advantage of SAP HANA.
