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

Comments (3)

  1. player says:

    Thanks, very clear and helpfull!

    May you also tell us how to pass parameters to Dynamics Reporting Services Report WebPart from another webpart e.g. AxGridView -> AxToolBar -> Ax Reporting Services Web Part. And this report, of course has, parameter like ‘PurchId’, that field presented in AxGridView.

  2. Mey M says:

    You can do it in two ways. The report webpart supports MOSS Filter consumer interface. So you have a MOSS Filter and connect it to the Report Web part, then it will take the parameter. The out of the box CEO and Sale&Marketing Executive MOSS role center pages uses this behavior.

    The second approach is passing it through query string.

    In the Ax report webpart , set the paramter selector to some unique string. For example, Report1 and you have a paramter defined in report , for ex CashInflowvsCashOutflow_Company, then in the query string you will pass

    http://<server>/sites/dynamicsax/enterprise portal/pagename.aspx?Report2.Parameters.CashInflowvsCashOutflow_Company=CEU