How to do aggregation in dynamics 365 (common data service) web api?

(0) Share

Report

Posted on by Tim Robinson

Hi,

Is there any way to aggregate data (e.g. return average order value across all orders for a given customer) using the common data services web api, or do I have to retrieve each individual record and perform the aggregation in my code?

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Alejandro Serdan Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi!

Here is the information to do the aggregation:

Aggregate and Grouping results

docs.microsoft.com/.../query-data-web-api

Regards.

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,985 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

You could create rollup field to get the aggregate value and use this field in webapi to show wherever you want.

Please see below link to check how to setup rolup field in Dynamics CRM.

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-rollup-fields

If found helpful, Please mark my answer verified.

Was this reply helpful? Yes No
Verified answer

cloflyMao 25,210 on at

Like (0)

Report
Copy link

Link copied!
Hi Tim,

You can aggregate data using Web API.

You can refer to:

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api#aggregate-and-grouping-results

Here are steps that follow your example (return average order value across all orders for a given customer).

1.This is all orders.

You can directly calculate average of all orders is 35354.3333. And the average is 20065.5 when the given customer named Jim Glynn(sample).

2.using Web API.

(1) calculate all orders.

salesorders?$apply=aggregate(totalamount with average as averagevalue)

(2) calculate orders that given customer named Jim Glynn(sample).

salesorders?$apply=aggregate(totalamount with average as averagevalue) &$filter=_customerid_value eq '491a2f1a-628e-ea11-a811-000d3a35bad3'

Regards,

Clofly

Was this reply helpful? Yes No
Tim Robinson 37 on at

Like (0)

Report
Copy link

Link copied!

Thanks - I was looking under "Function" and "Query Function" in the web api reference which is why I didn't find it!

Was this reply helpful? Yes No