Aggregation in EP Datasets
Often times, you might want to display some aggregated values in a simple grid interface in a web page. Enterprise Portal provides a simple and efficient way of doing this. Here is a sample that I created ( with help from Ahmad, Sella and others) for a partner who has similar requirements to display aggregated values in EP grid.
There are two ways to go about it. You can change the query of the dataset which has tables simply joined and change it to group by and aggregate fields through code or You can build AxQuery in AOT which has the group by and aggregate fields defined and then use this Ax Query in DataSet (Drag-n-Drop the Query to the DataSet or create a view pointing to the Query and refer this view through the table property)
First approach: Changing the query through code
1. Create a DataSet with two table ( CustTable and SalesTable) that are inner joined in AOT
2. Override the init method of CustTable and SalesTable data source and write the below code
3. Create a User control and point to the fields that are used in aggregation in the GridView and use it in the page
Second approach
(1) Create a new Query in AOT
a. Add the datasource
b. Add the Group By column
c. Add the Fields ( the group by column and the aggregated values for other fields)
(2) Create a new View in AOT
a. Set the Query property to the query created above
(3) Create a new DataSet in aOT
a. Add the datasource and set the Table property to the View created about
You could also directly drag-n-drop the Query to Data Source node of DataSet in AOT without creating a view
