Calculating Grid Totals on an Inquiry Form

One of the common requirements that one may have for the inquiry form is to have Totals calculated for some column in the grid.

It may seem a simple task, but there are some unexpected obstacles that make it hard to implement the feature properly.

Straightforward Solution

We can implement the requirement with the help of a filter delegate.

Assume that we have the filter view and the grid view.

public PXFilter<FilterDAC> Filter;
public PXSelectReadonly<ResultsDAC> Records;

Add a field for accumulated result to the filter DAC.

public partial class FilterDAC: IBqlTable
{
...
    public abstract class total : PX.Data.IBqlField { }
 
    [PXDecimal()]
    [PXDefault(TypeCode.Decimal, "0.0")]
    [PXUIField(DisplayName = "Total", Enabled = false)]
    public virtual decimal? Total { get; set; }
...
}

In the filter delegate get records from the grid view. For example:

protected virtual IEnumerable filter()
{
        FilterDAC filter = Filter.Current;
        if(filter != null)        
   {
      int startRow=0;
      int totalRows=0;
            filter.Total = 0; //reset total to zero
 
            foreach(ResultDAC row in Records.Select())
            {
                filter.Total += row.Amount;
            }
        }
    yield return filter;
}

This code works, but it has several issues:

• It does not consider user defined filter for the grid
• Filter is not refreshed when something changed in the grid
• It executes select that may return significant amount of rows and therefore affects performance in a bad way

Implementing User-defined Filters

To solve the first issue, we can extract user-defined filters from grid view and send them to the select we use to calculate totals. Example:

protected virtual IEnumerable filter()
{
   FilterDAC filter = Filter.Current;
   if(filter != null)
   {
            filter.Total = 0; //reset total to zero 
            foreach(ResultDAC row in
                    Records.View.Select(new[] { filter }, //pass filter context to the grid view delegate
                    null,
                    null,
                    null,
                    null,
                    Records.View.GetExternalFilters(), //Get grid user filters
                    ref startRow,
                    0, //get all records without regard to paging
                    ref totalRows))
            {
                filter.Total+= row.Amount;
            }
        }

    yield return filter;
}

Once this implemented, the second issue becomes vividly visible. After applying grid filter, it is required to change something in the filter to refresh it. Otherwise Total value stays the same.

Implementing Filter Refresh

To refresh the header after filter was applied to the grid, one can use Repaint Controls behavior defined in aspx:

The property is located in the Grid -> Callback Commands ->Refresh. One can specify controls that should be refreshed after grid refresh here.

<px:PXGrid ...>
<CallbackCommands>
<Refresh RepaintControlsIDs="form" />
</CallbackCommands>

Moving Totals Calculations to SQL Server

In Acumatica ERP, standard grid selects have built-in optimization: the system selects only those records that are visible on the current page. However, to calculate totals we have to select all records. That may have a negative performance impact. Better approach here is to get already aggregated data from the SQL server. To do that you add aggregation to the exiting BQL select. This will allow you to calculate totals on SQL level keeping amount of records retrieved from itl as low as possible.

protected virtual IEnumerable filter()
{
   FilterDAC filter = Filter.Current;
   if(filter != null)
   {
      int startRow=0;
      int totalRows=0; 
      var cmd = Records.View.BqlSelect.AggregateNew<Aggregate<Sum<ResultDAC.Amount>>>();// add aggregation to the existing BQL
      foreach (ResultDAC row in new PXView(this, false, cmd).Select(
                    new[] { filter }, //pass filter context to the grid view delegate
                    null,
                    null,
                    null,
                    null,
                    Records.View.GetExternalFilters(), //Get grid user filters
                    ref startRow,
                    0, 
                    ref totalRows))
        {
          filter.Total = row.Amount;

        }      
     }

    yield return filter;
}