Page 1 of 1

AcctViewer

PostPosted: Fri Aug 10, 2012 5:07 pm
by nmicoud
Hi,

I would like to add a "Progressive balance" column (not sure of translation...) in AcctViewer.

Here is an example :

DR CR Pr. balance
Line 1 100 0 100
Line 2 50 0 150
Line 3 0 80 70
[...]

It is equals to progressive balance of previous line + current line debit - current line credit.

Is it possible to calculate this inside AcctViewer ?

Regards,

Nicolas

Re: AcctViewer

PostPosted: Fri Aug 10, 2012 7:49 pm
by tbayen
To calculate such a "progressive balance" in SQL you need a recursive approach. Use the "WITH RECURSIVE" clause in a view. There is an example at http://www.postgresql.org/docs/8.4/stat ... -with.html

Re: AcctViewer

PostPosted: Fri Aug 10, 2012 10:06 pm
by nmicoud
Hi Thomas,
thanks for the link, will have a look ; hoping i could find equivalence for oracle

Re: AcctViewer

PostPosted: Fri Aug 10, 2012 11:54 pm
by tbayen
Sorry - I did not think about using other databases than postgres. :-o

This link seems to address the problem. It is in german but there is not much text between the example code. You should be able to understand and run it: http://martinpreiss.blogspot.com/2010/0 ... hnung.html

Re: AcctViewer

PostPosted: Sat Aug 11, 2012 6:06 am
by globalqss
Usually that's called a running total.

Unless the request is to be used all the time - I would simply export to excel (little button on bottom-right of AcctViewer) and create the formula there.

Regards,

Carlos Ruiz

Re: AcctViewer

PostPosted: Sat Aug 11, 2012 7:14 pm
by nmicoud
@Thomas : thanks for the updated link, will have a look
@Carlos : thanks for the correct term ; exporting to excel is not an option ; they don't want to do a lot of clicks to obtain them. If if is too slow, will try to use Jasper

Those running totals are not used elsewhere ?

Nicolas

Re: AcctViewer

PostPosted: Sun Aug 12, 2012 2:20 am
by globalqss
Nicolas, running total is implemented on "Statement of Accounts" - that's done using a temporary table and filling it with java.

Regards,

Carlos Ruiz

Re: AcctViewer

PostPosted: Sun Aug 12, 2012 7:31 pm
by nmicoud
I did not know that, thanks for the tip.

Regards,

Nicolas

Re: AcctViewer

PostPosted: Sat Oct 13, 2012 9:03 am
by red1
Sometime in 2004 during Compiere times, in the Financial Statements module, I did play around with something sort of 'Accumulative Periods' where you set the control to '-1' for up to last period and '0' to up to present period. Wonder if this is similar to your case.