Totals Calculation for Inquiry

Hi Everyone,

Today want to give you an idea of better architecture for inquiry with some aggregated calculations.

Problem Statenment

Let assume that you have an inquiry when you need to calculate totals together on the filter together with printing details like described here:

Than how can you do that? One of the obvious way may be to create a dataview and iterate thought ever record to calculate total:

public PXFilter<InquiryFilter> Filter;
public PXSelect<Batch> Records;
public IEnumerable records()
{
    decimal docsTotal = 0m;
    foreach(Batch row in Records.Select())
    {
        docsTotal += res.ControlTotal;
        yield return row;
    }
    //...
}

However it is not really good idea to iterate thought all records if you need to get a total of records selected, as you have to ignore paging and have to select all records from DB. That might be really slow.

Solution

Much better approach is to calculate totals in separate select with aggregation and than select just needed records with paging as usual.

public PXFilter<InquiryFilter> Filter;
public PXSelect<Batch> Records;
public IEnumerable filter()
{
    Batch row = PXSelect<Batch, 
        Aggregate<GroupBy<Batch.batchType, 
        Sum<Batch.controlTotal>>>>>.Select();
    decimal docsTotal = row.ControlTotal;
    //...
}

So with this approach you have 2 selects to the database, but you don’t need to select all the records just to calculate totals. Usually it is much faster.

Hope it helps and Have a nice development!