We have this this layout using Grid
ProductGroupA RolledUpQty
Product1 Qty1 StatusA
Product2 Qty2 StatusB
Product3 Qty3 StatusA
Product4 Qty4 StatusB
Product Group shows in parent band, all products rows show in child band, using parent-child table relationship.
Right now RolledupQty is sum of Qty1, Qty2, Qty3 and Qty4 i.e. qty for all products in that product group. I am using .NET Calculated column capability to roll up the value for all child rows.
I will need to change my implementation because user wants to be able to filter rows using Status and the RolledUpqty to reflect that.
So, for example, if I filter for StatusA, then RolledUpQty should show sum of Qty1 and Qty3 only i.e. Product2 and Product 4got excluded from the total. Conversely, if I filter on StatusB, RolledUpQty should show sum of Qty2 and Qty4 i.e. Product1 and Product3 got excluded.
In short, the RolledUpQty is qty total of only rows that are visible after filter. What is the best way to achieve this?
Hello Vern,
Thank you for posting in our forum.
You may achieve such behavior with UltraCalcManager. By using the calc manager you can set a formula to calculate the value of the cells in a column of the grid. What you need to do is to add UltraCalcManager to your form in the designer. Then in IntializeLayout event handler of your grid you need to set the grid’s cals manager. You can use code like this:
e.Layout.Grid.CalcManager = this.ultraCalcManager1;
And the final step is to set a formula for the "Rolled Up Quantity" column like this:
e.Layout.Bands[0].Columns["Rolled Up Quantity"].Formula = string.Format("sum ([{0}])", e.Layout.Bands[1].Columns["Quantity"].FormulaAbsoluteName);
More about UltraCalsManager you may find by following the next link http://help.infragistics.com/Doc/WinForms/current/CLR4.0/?page=WinCalcManager_Understanding_WinCalcManager.html. Here you can find and CalcManager Formula and Reference Guide http://help.infragistics.com/Help/Doc/WinForms/2015.1/CLR4.0/html/WinCalcManager_Formula_and_Reference_Guide.html.
In the attached sample solution I have implemented UltraGrid with UltraCalcManager which calculates the values of the "Rolled Up Quantity" column.
Please check my sample and let me know if you need any additional information.
Thank you for using Infragistics Controls.
Milko, thanks for your help.
I gave you example of Status A and B. So when rows of only one status are visible, the rolled up number would reflect total just for those? In other words, would UltraCalcManager automatically recalc if some row is hidden by the filter?
Secomd. is there any capability in UltraCalcManager to exclude certain kinds of rows (row with certain fieldvalue)?
Thank you for your feedback.
Here is the answer to your questions:
Would UltraCalcManager automatically recalc if some row is hidden by the filter – yes. This is how UltraCalcManager works by default. Please try the sample from my post above.
Is there any capability in UltraCalcManager to exclude certain kinds of rows (row with certain fieldvalue) – UltraCalcManager supports many functions like IF, SUM, MAX and so on. However there is no SUMIF or some similar function you can use. If you need to filter the summed values you can calculate them by iterating all child rows. To calculate the values when the filter changes you can make your calculation in AfterRowFilterChanged. Please check attached sample project where I have implemented this.
Please let me know if you need any additional information.