SAP business one - how to use CrossJoin with aggregation

[former_member807689]

I need to get the count of the Sales orders by date range and the total sum of Quantity property in DocumentLines related to exact Sales order. I can get the count of Sales order by date, but I can't get the total sum of Quantity for that Sales order in one query. For now I am using this Query:

So we want to merge this two queries and get the expected result as following:

{
"value": [
{
"@odata.id": null,
"CreationDate": "2022-05-12",
"Quantity":"200",
"count": 5
},
{
"@odata.id": null,
"CreationDate": "2022-05-19",
"Quantity":"300",
"count": 10
},
{
"@odata.id": null,
"Quantity":"400",
"CreationDate": "2022-05-26",
"count": 15
},
}

Does anyone familiar with this kind of issue.

Thanks in advance.

[ANKIT_CHAUHAN]

Hi paruyr,

There are some known issues when using Cross Join with aggregation in Service Layer.

Refer to SAP Note 3139209 for the same.

The issue is supposed to be fixed in Feature Package 2202 of SAP Business One Version 10.0. Be advised that the time-frame is subject to change without any further notice.

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[former_member807689]

Okay, Thanks for the comment...