Running Total Puzzle

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
ACDC
Posts: 1158
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Running Total Puzzle

Post by 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
Attachments
Running Total.jpg
Running Total.jpg (64.38 KiB) Viewed 5817 times
Jaymer
Posts: 2528
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Running Total Puzzle

Post by Jaymer »

def. a stored procedure
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
BLOMASKY
Posts: 1490
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Running Total Puzzle

Post by BLOMASKY »

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

bruce
BLOMASKY
Posts: 1490
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Running Total Puzzle

Post by 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
Posts: 2431
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Running Total Puzzle

Post by customaware »

Try This......
acdc.bsv.zip
Demo BSV
(41.2 KiB) Downloaded 402 times
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
ACDC
Posts: 1158
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Running Total Puzzle

Post by ACDC »

BLOMASKY wrote: Tue Jul 27, 2021 12:53 am 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
Last edited by ACDC on Wed Jul 28, 2021 11:09 am, edited 1 time in total.
ACDC
Posts: 1158
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Running Total Puzzle

Post by ACDC »

BLOMASKY wrote: Tue Jul 27, 2021 12:54 am 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
Posts: 2431
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Running Total Puzzle

Post by customaware »

Did you try the demo above ACDC?
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
ACDC
Posts: 1158
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Running Total Puzzle

Post by ACDC »

eagles9999 wrote: Tue Jul 27, 2021 3:06 am Try This......

acdc.bsv.zip
Thank you for your sample Mark, I will try this later today
customaware
Posts: 2431
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Running Total Puzzle

Post by customaware »

ACDC wrote: Wed Jul 28, 2021 11:15 am
eagles9999 wrote: Tue Jul 27, 2021 3:06 am 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.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
ACDC
Posts: 1158
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Running Total Puzzle

Post by 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
Posts: 2528
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Running Total Puzzle

Post by Jaymer »

Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply