aquariansp
asked on
Formula fields addition
I have the following issue in Crystal Reports 10:
we are doing a crystal report with the following format:
Account Description Beginning Bal Current Balance Ending Balance
The user chooses a period..let us say 200404.
The logic is as follows:
Beginning Balance contains the sum of all account amounts for whole of 2003 ie., 200301 to 200312 plus all account amounts till april 2004 ie., till 200403.
current Balance contains all account amounts for the current period ie., for 200404 ie., only for the month of april.
Ending Balance is sum of Beginning Balance and Ending Balance.
This should be for all account numbers.
the these account numbers are grouped by ranges as total expenses,total revenues,total income etc.
The problem is all the account amounts are stored in a field called BAMOUNT. This stores both positive and negative values ( for debit and credit values).
How do we get this right:
Format:
Account Description Beginning Bal Current Balance Ending Balance
11001 xxxxx 2500 3000 5500
12001 xxxxx 3400 3400 6800
Total Liability 5900 6400 12300
like the above format we are having lot of groups.
we have done some logic which checks the input period and picks up amounts for each account number from the previous period and upto the current period, ignoring the sign and placing it right.
same way for current balance also.
but when we are trying to add beginning balance to current balance to get the ending balance we are getting erroneous results.
all three of them are placed in group footer with detailed section supressed.
please give me a solution for this ASAP.
so how do i get the whole thing right.
expecting a detailed response.
we are doing a crystal report with the following format:
Account Description Beginning Bal Current Balance Ending Balance
The user chooses a period..let us say 200404.
The logic is as follows:
Beginning Balance contains the sum of all account amounts for whole of 2003 ie., 200301 to 200312 plus all account amounts till april 2004 ie., till 200403.
current Balance contains all account amounts for the current period ie., for 200404 ie., only for the month of april.
Ending Balance is sum of Beginning Balance and Ending Balance.
This should be for all account numbers.
the these account numbers are grouped by ranges as total expenses,total revenues,total income etc.
The problem is all the account amounts are stored in a field called BAMOUNT. This stores both positive and negative values ( for debit and credit values).
How do we get this right:
Format:
Account Description Beginning Bal Current Balance Ending Balance
11001 xxxxx 2500 3000 5500
12001 xxxxx 3400 3400 6800
Total Liability 5900 6400 12300
like the above format we are having lot of groups.
we have done some logic which checks the input period and picks up amounts for each account number from the previous period and upto the current period, ignoring the sign and placing it right.
same way for current balance also.
but when we are trying to add beginning balance to current balance to get the ending balance we are getting erroneous results.
all three of them are placed in group footer with detailed section supressed.
please give me a solution for this ASAP.
so how do i get the whole thing right.
expecting a detailed response.
ASKER
The formulae we are using are:
BegBal
if {T99_DETALJ.POST_PERIODE} = {?Period} then
0
else
abs({T99_DETALJ.BELOP});
Credit1
if {T99_DETALJ.POST_PERIODE}= {?Period} then
if {T99_DETALJ.BELOP}<0 then
{T99_DETALJ.BELOP}
else
0;
Cur_Period
abs(Sum ({@Credit1}, {T99_DETALJ.KONTONR}))+Sum ({@Debit1}, {T99_DETALJ.KONTONR})
CurPer
if {T99_DETALJ.POST_PERIODE} = {?Period} then
abs({T99_DETALJ.BELOP})
else
0;
Debit1
if {T99_DETALJ.POST_PERIODE}= {?Period} then
if {T99_DETALJ.BELOP}>0 then
{T99_DETALJ.BELOP}
else
0;
EndBal
Sum ({@Debit1}, {T99_DETALJ.KONTONR})+abs( Sum ({@Credit1}, {T99_DETALJ.KONTONR}))+Sum ({@BegBal}, {T99_DETALJ.KONTONR})
From
stringvar yea;
yea:=totext(tonumber(left( {?period}, 4)),0000,' ');
yea:=totext(tonumber(yea)- 1,0000,'') ;
yea:=yea&'01';
yea;
LedgerId
if {T99_DETALJ.BILAGSART}='0' then
'ACTUAL'
RecEndBal
whileprintingrecords;
abs({@Credit1})+ {@Debit1}+{@BegBal}
TitlePeriod
stringvar yea1;
stringvar mon1;
mon1:=right({?period},2);
yea1:=Mid ({?period},3 ,2 );
mon1&'-'&yea1;
to
{?Period}
RECORD Selection formula
{t99_detalj.post_periode}> = {@from} and {t99_detalj.post_periode}< ={@to} and {T99_DETALJ.SELSKAPSKODE}= {?CompanyI d}
Hope this helps in telling us why we are not able to add up beginning balance and current period amounts to get proper ending balance values.
~aquariansp
BegBal
if {T99_DETALJ.POST_PERIODE} = {?Period} then
0
else
abs({T99_DETALJ.BELOP});
Credit1
if {T99_DETALJ.POST_PERIODE}=
if {T99_DETALJ.BELOP}<0 then
{T99_DETALJ.BELOP}
else
0;
Cur_Period
abs(Sum ({@Credit1}, {T99_DETALJ.KONTONR}))+Sum
CurPer
if {T99_DETALJ.POST_PERIODE} = {?Period} then
abs({T99_DETALJ.BELOP})
else
0;
Debit1
if {T99_DETALJ.POST_PERIODE}=
if {T99_DETALJ.BELOP}>0 then
{T99_DETALJ.BELOP}
else
0;
EndBal
Sum ({@Debit1}, {T99_DETALJ.KONTONR})+abs(
From
stringvar yea;
yea:=totext(tonumber(left(
yea:=totext(tonumber(yea)-
yea:=yea&'01';
yea;
LedgerId
if {T99_DETALJ.BILAGSART}='0'
'ACTUAL'
RecEndBal
whileprintingrecords;
abs({@Credit1})+ {@Debit1}+{@BegBal}
TitlePeriod
stringvar yea1;
stringvar mon1;
mon1:=right({?period},2);
yea1:=Mid ({?period},3 ,2 );
mon1&'-'&yea1;
to
{?Period}
RECORD Selection formula
{t99_detalj.post_periode}>
Hope this helps in telling us why we are not able to add up beginning balance and current period amounts to get proper ending balance values.
~aquariansp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suspect I may have solved this witht he formula correction
mlmcc (11985296)
mlmcc
mlmcc (11985296)
mlmcc
mlmcc