Enterprise Resource Planning Blogs by SAP
Get insights and updates about cloud ERP and RISE with SAP, SAP S/4HANA and SAP S/4HANA Cloud, and more enterprise management capabilities with SAP blog posts.
cancel
Showing results for 
Search instead for 
Did you mean: 
andy_bai
Employee
Employee
0 Kudos


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.