Solved

How can I add subtotal to the bottom of the data entry or inquiry grids?

  • 2 March 2022
  • 8 replies
  • 614 views

Userlevel 7
Badge +9

When people work on grids or view, they would like to see the subtotals at the bottom of the grids. I know we can add a calculated field to the headr of data entry screens and using “SumCalc” update that field from the grids but it is not really handy and neat specially when you have many numeric columns that you would like to sumerize. We can see many of these screens in construction module. 

What I would like to do is to 1) add a subtotal to the bottom of the grids 2) freez this subtotal row at the bottom of the grid to prevent from hiding. I know there is a property in Acumatica grids for “Footer” that in “Summary Type” we can type of aggregate function we want to use but for some reason I couldn’t make it work on a sample grid I’m testing. 

I see Acumatica has used this functionality in “Balalnces” tab of “Projects” as shown in the below snip but I see the “Footer Summary Type” is not set at all for this grid.

Has anyone added something like “Expense Total” from the above snip to any grid that can help me replicate the same?

icon

Best answer by akshit 16 May 2022, 11:53

View original

8 replies

Badge +11

Curious as well

 

Userlevel 6
Badge +5

That grid is processed as a view delegate, the totals you see are calculated and returned as rows(inside the graph). Additionally there is some manual styling occurring in the code behind file to make it bold. Essentially that is just another row of data though. They generate those total rows with a negative record id and use that to apply the styling.

 

The specific methods on the graph creating those rows are CreateHeader and CreateTotal.

Userlevel 7
Badge +9

@markusray17 Thank you for the explanation but still I have not received my two questions answer.

@Naveen B any thought? is there anyway without going deep into the Graphs and manupulating them get a total rows for the grids and lock it at the bottom?

Userlevel 7
Badge

Hi @aaghaei were you ever able to resolve your issue? Thank you!

Userlevel 7
Badge +9

Hi @Chris Hackett

Not realy. There are a lots of grids that we need total and the suggested method by Mark requires a lots of time and almost modifyind every single graph. I believe Acumatica needs to add a built-in feature to make subtotals accessible enable/disable for the grids right from screen not even from customization..

 

Thank you for the follow up. 

Userlevel 7
Badge +17

Hi @aaghaei   Hope you have tried this as well?

 

https://asiablog.acumatica.com/2019/06/calculating-grid-totals-on-a-form.html

Userlevel 7
Badge +9

Thanks @Naveen Boga, I have something similar in quite a few of my customizations but what I’m looking for is different and mre comprehensive. For example I have a grid with 35 columns that I need total for all of them. adding a summary field for all of them on the header makes the screen disply messy and also needs quite bit of coding in the back-end. This is not the only grid I need total. Almost in all grids users need a total for their grids. for example Cost and revenue budget, AR & AP listings and agings, times … and almost every single grid. Acumatica will need to work on something for this ever increasing problem. Acumatica Grid Control has Header & Footer control but I’m unsure how we can make it work.

Hi @aaghaei
I recently came through a similar project to have totals as a separate row on various different project screens/tabs. 
You will have to extend your base graph and add/modify the underlying view for the grid. For instance : Cost Budget tab on Project Screen. This already shows total when grouped by task but i wanted it visible directly, without being grouped. Also it loses cost projection data when grouped by task…
Pretty sure the concept will work on any screen/tab. 

There is a CalculateSumTotal function/method within PX.Objects.Common.Extensions which can be used to get the totals for all the columns in any DAC. I added the existing back end code to my extended graph, created a new list of the line data returned in the same view and did sum/total using CalculateSumTotal. 
 

 public IEnumerable costBudget()
{
var selectCostBudget = new PXSelect<PMCostBudget, Where<PMCostBudget.projectID, Equal<Current<PMProject.contractID>>, And<PMCostBudget.type, Equal<GL.AccountType.expense>,
And<Where<Current<CostBudgetFilter.projectTaskID>, IsNull, Or<Current<CostBudgetFilter.projectTaskID>, Equal<PMCostBudget.projectTaskID>>>>>>,
OrderBy<Asc<PMCostBudget.projectID, Asc<PMCostBudget.projectTaskID, Asc<PMCostBudget.inventoryID, Asc<PMCostBudget.costCodeID, Asc<PMCostBudget.accountGroupID>>>>>>>(Base);

PXDelegateResult delResult = new PXDelegateResult();
delResult.Capacity = 202;
delResult.IsResultFiltered = false;
delResult.IsResultSorted = true;
delResult.IsResultTruncated = false;

if (IsCostGroupByTask() && !IsCopyPaste)
{
var list = new List<PMCostBudget>(selectCostBudget.Select().RowCast<PMCostBudget>());

delResult.AddRange(AggregateBudget<PMCostBudget>(list));
}
else
{
var view = new PXView(Base, false, selectCostBudget.View.BqlSelect);
var startRow = PXView.StartRow;
int totalRows = 0;

var resultset = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
PXView.StartRow = 0;
delResult.AddRange(resultset.RowCast<PMCostBudget>());

// Addition/modification - create a new list for the DAC and set primary keys as null MOD BEGIN

List<PMCostBudget> result = new List<PMCostBudget>(selectCostBudget.Select().RowCast<PMCostBudget>());

PMCostBudget total = result.CalculateSumTotal(selectCostBudget.Cache);
total.ProjectID = Project.Current.ContractID;
total.ProjectTaskID = null;
total.AccountGroupID = null;
total.CostCodeID = null;
total.InventoryID = null;
total.Description = "TOTAL";
total.UOM = null;
total.Qty = null;
total.CuryUnitRate = null;
total.CuryUnitPrice = null;

delResult.Add(total);
//Add total as new row MOD END

}

return delResult;
}

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved