Solved

Formula fields addition

Posted on 2004-09-05
7
451 Views
Last Modified: 2008-02-20
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.
0
Comment
Question by:aquariansp
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 11984045
What formulas are you using?

mlmcc
0
 

Author Comment

by:aquariansp
ID: 11984259
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}={?CompanyId}

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
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 11985296
2 formulas need to be fixed

Credit1

if {T99_DETALJ.POST_PERIODE}={?Period} then
    if {T99_DETALJ.BELOP}<0 then
        {T99_DETALJ.BELOP}
    else
       0
else
  0

Debit1

if {T99_DETALJ.POST_PERIODE}={?Period} then
    if {T99_DETALJ.BELOP}>0 then
        {T99_DETALJ.BELOP}
    else
       0
else
  0


In this formula
From

stringvar yea;
yea:=totext(tonumber(left({?period},4)),0000,'');
yea:=totext(tonumber(yea)-1,0000,'');
yea:=yea&'01';
yea;

From

stringvar yea;
yea:=totext(tonumber(left({?period},4)),0000,'');
yea:=totext(tonumber(yea)-1,0000,'');  'What is this line doing?
yea:=yea&'01';
yea;


mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12196583
I suspect I may have solved this witht he formula correction
mlmcc (11985296)

mlmcc
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question