Aggregate

  

I have below entities

 I want aggregate to show records in table as given below

 

Calculation of Total Cost is as : Sum of each items (quantity * uniteprice) for respective Request.

How we can achieve this by using Aggregate joining Request,Items,Department entities. 

Hi Rohan,

  1. Create the aggregate
  2. Add the 3 entities to it, based on proper foreign key conditions the join conditions will automatically be added
  3. Do a group by on Request.Name, Request.email and Department.Name
  4. Create a new aggregate attribute Cost having the formula Items.quantity * Items.unitprice
  5. Do a Sum on the newly created attribute.

Regards,

Daniel

Hello Rohan


You need Join the entities (in source tab)

then add a calculation field that multiplay quantity by unitprice. 



Regards


Daniël Kuhlmann wrote:

Hi Rohan,

  1. Create the aggregate
  2. Add the 3 entities to it, based on proper foreign key conditions the join conditions will automatically be added
  3. Do a group by on Request.Name, Request.email and Department.Name
  4. Create a new aggregate attribute Cost having the formula Items.quantity * Items.unitprice
  5. Do a Sum on the newly created attribute.

Regards,

Daniel


Is it an optimized solution?
Instead of grouping multiple columns, Can we have any other better way to do this?

Hi Amol,

OutSystems will optimize your aggregates. 

Alternatively you could use advanced SQL but with what you want you still will need to join 3 entities and do a group by and a sum. So in the end it is the same.

The actual grouping and sum is performed by the underlying database management system (MS SQL Server or Oracle). 

I suggest you try and see if performance is fine for you.

Then only think about optimizing it if OutSystems report "SLOW SQL" issues.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Rohan,

  1. Create the aggregate
  2. Add the 3 entities to it, based on proper foreign key conditions the join conditions will automatically be added
  3. Do a group by on Request.Name, Request.email and Department.Name
  4. Create a new aggregate attribute Cost having the formula Items.quantity * Items.unitprice
  5. Do a Sum on the newly created attribute.

Regards,

Daniel

About 4th Point, We need this in a single list to show in data table because we have sorting functionality on it.

We have created a group by on item quantity but it is giving multiple results.



Do exactly what Daniel says and you get what you want, see attached. Obviously grouping by item quantity will not work for what you want.


To add sort use a dynamic sort with the group names