Running Total Puzzle

[ACDC]

I have a mental block, can't fathom this one out ......wondering which is the best and most efficient way of maintaining a running total on a range of like objects. Each object in a given sequence e.g. Date or Timestamp sequence, adds to the previous object in the sequence and updates its own Balance attribute

So imagine an Order with OrderLineItems. As each line time is added the OrderLineItem.Balance attribute gets updated with the sum of all the previous objects in the sequence. The last item in the grid will end with the Order.OrderLineItem.Balance equalling the sum of all the items. If anyone of the items is edited, and its value changes , all items in the sequence have to then recalculate.

Also if the there are thousands of records, an object rule that updates this balance is going to have to recalculate all the objects resulting in all the rules of the object firing every time an edit takes place. So if any item is edited all items have to recalculate - thinking of overhead here. In a simple Order with OrderLines it won’t matter.

Any ideas on how to achieve this in the most efficient way will be most appreciated

here is an example pic

[Jaymer]

def. a stored procedure

[BLOMASKY]

What flavor of SQL are you using? If MSSQL, I know the answer. I am not that familar with other dialects

bruce

[BLOMASKY]

and, it can be done with Jasper. Just add a variable that keeps adding the amount to itself and print on the detail row.

Bruce

[customaware]

Try This......

acdc.bsv.zip

Demo BSV

(41.2 KiB) Downloaded 403 times

[ACDC]

What flavor of SQL are you using? If MSSQL, I know the answer. I am not that familar with other dialects

bruce

I am using MySQL , I read somewhere that MSSQL has a specific function for this in the DB. I am curios to know more about the stored procedure and the function , or will it just be a stored procedure / trigger of some sort -- Considering moving to MSSQL

[ACDC]

and, it can be done with Jasper. Just add a variable that keeps adding the amount to itself and print on the detail row.

Bruce

When you say Jasper, you mean Report writer or Jasper server? I Ideally i want to store this in the DB. Using the report writer seems more efficient in some cases

[customaware]

Did you try the demo above ACDC?

[ACDC]

Try This......

acdc.bsv.zip

Thank you for your sample Mark, I will try this later today

[customaware]

Try This......

acdc.bsv.zip

Thank you for your sample Mark, I will try this later today

I use this methodology a LOT now.... demonstrate the amazing power of the While Semantic when coupled with Non-Persistent Business Objects.

[ACDC]

I use this methodology a LOT now.... demonstrate the amazing power of the While Semantic when coupled with Non-Persistent Business Objects.

This is elegant, never used the While option before but now see its power. Thanks once again , I can see other use cases as well for this methodology

[Jaymer]

https://www.awareim.com/forum/viewtopic.php?f=4&t=11426