Formula for "Balance"

Hi all experts,

How to do the formula for the balance? e.g. result show as below.

I need this for my customer statement

Dr Cr Balance

10 0 10

11 8 13

5 10 8

Thanks.

Danny

Edited by: Danny Gan on Oct 16, 2008 11:20 AM

SAP Managed Tags:
SAP Crystal Reports

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

Hi Danny,

If Cr and Dr are straight from the DB then create a formula

Numbervar bal;

bal:=DB.Cr-DB.Dr;

And place this formula into your report.

Hope this helps!

Regards,

Vinay

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Formula:

Numbervar bal;

bal:={JDT1.Debit}-{JDT1.Credit}

Formula u advised result is not really correct....

Debit Credit Balance

125 0 125

0 125 -125

Correct result

Debit Credit Balance

125 0 125

0 125 0

Morning,

hmm, if I have rightly understood, what you are trying to do is to subtract Cr from Dr each time and then showing it under balance?

So, what I would do is to create three formulas here. One to calculate the number of Dr, i.e. 10, 11, 5 and then create another formula which calculates Cr, i.e. 0,8,10 and then third one would be

{@formula1}-(@Formula2}

and then display it on to the report.

Do I make sense?

Regards

Jehanzeb

Dr Cr Balance

10 0 10

11 8 13

5 10 8

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi, Morning,

{@formula1}-(@Formula2}

How to get the @formula1 & @Formula2 ?

thanks for reply.

Hello,

are you getting Dr and Cr values? do you know how to calculate Cr and Dr values? This is something you have to find out because I don't know how your database is set up and what tables or fields are linked so I cannot really help you on how to get Dr and Cr values.

Saying that, I can try help you creating a formula of calculating Cr and Dr if you tell me how the whole report is laid out.

Many thanks

Regards

Jehanzeb

Hi,

It is customer statement which consist of Debit and Credit (straight amount from DB B1), Balance (is not in DB) so that i need to used a formula.

Thanks.

Regards,

Danny.

Yes Vinay is correct as I said earlier in my post two of those formulas you do not need as they were required to calculate Cr and Dr.

If Cr and Dr are direct fields then just take Cr from Dr and you get your result.

So in this case


Whileprintingrecords;
Numbervar x:=Cr Field;
Numbervar y:= Dr field;
Numbervar z:=0; 
z:=x-y;
z;

providing that Cr is subtracted from Dr.

If you have grouped your report by something then add this formula into the group header and suppress it.


Whileprintingrecords;
Global Numbervar x:=0;
Global Numbervar y:=0;

Regards

Jehanzeb

Hi Jehanzeb Navid , Thanks for reply.

I can't get the correct result as show as below.

Debit Credit Balance

125 0 125

0 125 -125

Correct result

Debit Credit Balance

125 0 125

0 125 0

Hello,

Have you tried my method?

Hi,

Yes, i tried.

yours method is showing negative...

Correct one is :-

Dr Cr Balance

125 0 125

0 125 0

Edited by: Danny Gan on Oct 16, 2008 2:12 PM

ok in normal circumstances if we take 125 out of 0 it will give us negative value. however in this case we have to implement a code which says if Dr =0 then display only Cr value.

So,


Whileprintingrecords;
Numbervar x:=Dr Field; //Debit filed
Numbervar y:= Cr field; // Credit Field
Numbervar z:=0;  //Balance field reset to 0
If x:=0 then //if debit field is 0
z:=x else // then set up the balance as only debit
z:=x-y; //Debit - Credit
z;  //show balance

Try this

Regards

Jehanzeb

If x:=0 then //if debit field is 0

Error message at above line : A boolean is required here

how am i going to correct it?

Thanks,

Danny.

Hi ,

Can you please try this

Whileprintingrecords;

numbervar X:= Cr_field;

numbervar Y:= Dr_Field;

numbervar Z;

if Y> 0 then

Z:= X-Y;

Regards,

Vinay

If x:=0 then //if debit field is 0

above line is shown an error message : "A boolean is required here"

How am i to edit / covert to boolean ?

Thanks.

Hi Danny,

Can you copy and paste your complete formula.

Regards,

Vinay

Dr Cr Balance <<<Ur method result is not really correct

125 0 0

0 125 -125

correct result is Dr Cr Balance

125 0 125

0 125 0

Edited by: Danny Gan on Oct 16, 2008 2:44 PM

Whileprintingrecords;

Numbervar x:=Dr Field;

Numbervar y:= Cr field;

Numbervar z:=0;

If X > 0 then

Z:= x - y;

It's not correct also:-

example which is correct one.....

Debit (LC) Credit (LC) Balance (LC)

4344 <Space> 4344

2002 <Space> 6346

1716 <Space> 8062

<Space> 4344 3718

1716 <Space> 5434

286 <Space> 5720

Edited by: Danny Gan on Oct 16, 2008 3:12 PM

Ok sorry for the delayed answer I went for lunch.

Right so we're having the same issue about if the value of debit becomes 0 then we have to use the debit value and should not subtract credit value from it

I made a mistake in my formula though.

Here is the correct formula


Whileprintingrecords;
Numbervar x:=Dr field
numbervar y:= Cr field
Numbervar z:=0;

if x=0 then
z:=x
else
z:=x-y;
z;

Please note: what I was doing earlier was assigning x to the value of 0 which was incorrect.

Please try now.

Regards

Jehanzeb

Hi,

It is also not all correct.

Correct e.g. as below:-

It's not correct also:-

example which is correct one.....

Debit (LC) Credit (LC) Balance (LC)

4344 <Space> 4344

2002 <Space> 6346 //will add up 1 row debit and 2nd row debit

1716 <Space> 8062

<Space> 4344 3718 // the 4th row balance will minus the Credit and get a new Balance which is 3718

1716 <Space> 5434

286 <Space> 5720

Thanks.

Danny

Danny the option with X=0, what happened? show me the incorrect results with correct one so that we know what is wrong and what needs correcting.

I did the same in my report and it worked so not sure where the problem lies.

Regards

Jehanzeb

Hi, Thanks for reply....

Result as below:

Debit Credit Balance (Correct) Balance (Yours Method)

10422 0 10422 10422

10692 0 21114 10692

0 10422 10692 0

10692 0 21384 10692

10692 0 32076 10692

0 10692 21384 0

0 21384 0 0

10692 0 10692 10692

Right if i have read this correctly, when the debit is not chosen the value is something else.

For example, in your example, When debit is 10422 and credit is 0 then it should show 10422 however when debit is 0 and credit is 10422 the balance is showing as 10692?? How can it be 10692?

There are other similar examples in the list.

Debit --- Credit --- Balance (Correct) -

Balance (Yours Method)

10422 -

0 -

10422 -

10422

10692 -

0 -

21114 -

10692

0 -

10422 -

10692 -

10692 -

0 -

21384 -

10692

10692 -

0 -

32076 -

10692

Could you please explain what exactly happens if

1: debit is 0 and credit has value?

2: debit has value and credit is 0?

3: when both are 0?

4: when both has values?

is there any external value or fields involved?

Regards

Jehanzeb

Debit --- Credit --- Balance (Correct)

1 10422 -

0 -

10422

2 10692 -

0 -

21114

3 0 -

10422 -

10692

4 10692 -

0 -

21384

5 10692 -

0 -

32076

e.g Debit is a Sales and Credit is Payment.

1 row - Sales is 10422 (Debit) ,and don't have payment so Credit is 0, so the customer owe me 10422

2.row - Sales is 10692 (Debit), and don't have payment so Credit is 0, so total owe me plus 1 row total is 21114

3.row - don't have Sales so Debit is 0, but have a payment so Credit 10422, Total owe me have been reduce, total become 10692. (21114-10422 = 10692)

4.row - Sales is 10692(Debit) ,and don't have payment so Credit is 0, so the customer owe me 21384 (Total up 10692+10692)

5.row - Sales is 10692 (Debit), and don't have payment so Credit is 0, so final total owe me 32076 (21384+10692)

Well Danny now it makes sense. You didn't mention about adding up the previous row totals into next row. So as I was wondering where the numbers are coming from even though credit being 0...:-))

Right so we have these issues,

1: The first initial start of the report should always be set to 0, so that the report shows the starting point of the debit, credit and balance.

That can be done by creating a formula "Reset" and place it into the Report header.

2: The 2nd line or the following line should "Add" the row total if it is Debit, however if it is credit then "Subtract" the total from the current row total.

So if we have a senario like

Row1

Debit = 1

Credit = 0

Balance=1

Row 2

Debit = 2

Credit = 0

Balance = 3

Row 3

Debit = 0

Credit = 1

Balance =2

Am I correct?

Regards

jehanzeb

Hi Jehanzeb,

sorry, forgot to tell you.

Yeah, You're correct. I have do many but can't get the correct balance. Do you have ideal for this formula?

Thanks.

Regards,

Danny.

Hello,

Try this


Whileprintingrecords;
Numbervar x:=Dr Field;
Numbervar y:= Cr field;
Numbervar z:=0;

If x>0 and y>0 then
z:=x-y
else if x=0 and y=0 then
z:=x+y;
z;

Regards

Jehanzeb

Hi, it is not correct also....

Below Result:-

Debit --- Credit --- Balance (Correct) -

Balance (Yours Method)

10422 -

0 -

10422 -

10692 -

0 -

21114 -

0 -

10422 -

10692 -

0 -

21384 -

10692 -

0 -

32076 -

Morning Danny, hope you had a great weekend.

Right can you try something and tell me what does it produces?

Put the below code in details section and suppress it


whileprintingrecords;
numbervar x;
x := x + {table.debit}-{table.credit};

Along with the code I gave you before


Whileprintingrecords;
Numbervar x:=Dr Field;
Numbervar y:= Cr field;
Numbervar z:=0;

If y>0 and x>0 then
z:=x-y
else if x=0 and y=0 then
z:=x+y;
z;

and show the results.

Regards

Jehanzeb

Hi, hope you had a great weekend too.

whileprintingrecords;

numbervar x;

x := x + {table.debit}-{table.credit};

Error message at above line : "A boolean is required here"

So which part i need to edit?

Thanks,

Regards,

Danny

I think x:= should be x=.

Try that

Regards

Tested, Result is all balance show 0

Regards,

Danny

Danny it is very strange that you are not getting what I am getting in my report.

I have tested the similar logic with my two Fields (QTY of right lens, left lens)

Here is the formula I used,


whileprintingrecords;
numbervar x;
x := x + Sum ({lab_rework.left_qty}, {lens_types.prod_group})-
Sum ({lab_rework.right_qty}, {lens_types.prod_group});

and it works.

You sure you create a formula of what I said to you and then placed it into the report. If you are using any other formulas, please remove them from the report for a while until the test shows the right results.

Alternatively post your Design view and the Preview view of the report here so I can see whts going on. You can always upload the screenshot on Photobucket or flikr and post a link here.

Regards

Jehanzeb

Hi Jehanzeb,

Yes, i followed the steps what you said, but no luck.

I already screenshot on flikr and post a link here. Please click below link.

http://www.flickr.com/photos/31682762@N08/

Thanks.

Regards,

Danny

Danny first problem,

You are suppressing the formula, I think you took the wrong idea when I said suppress. Instead of suppressing only one formula you are suppressing the whole row when the formula executes.

1: Copy the formula (from the suppression window)

2: Delete the formula from the suppression window and uncheck the suppression checkbox.

3: Create a new formula from the Field Explorer

4: Paste the copied formula there

5: Save the formula and drag and drop it to the details section

6: Now run the report.

Post the image then

Regards

Jehanzeb

Hi,

miss undertanding.....

I already change wat u said..

http://www.flickr.com/photos/31682762@N08/?saved=1

thanks.

Regards,

danny

OK lets make it simple, get rid of extra bits you have in the report. i.e. those DMI formula field and also get rid of my formula field which states

If X>0 and Y>0 etc....

Place only three fields, one debit, one credit and one x:=x+debit - credit.

Regards

Jehanzeb

Hi, like this?

Whileprintingrecords;

Numbervar x:={JDT1.Debit};

Numbervar y:= {JDT1.Credit};

Numbervar z:=0;

z:=x+y;

No like this


whileprintingrecords;
numbervar x;
x := x + {table.debit}-{table.credit};

Regards

Jehanzeb

Jehanzeb, you are really is a expert....Finally get the result which i want.....

Thanks a lot....

Regards,

Danny

Hahaha! It took ages to get there but we got there.

I am happy it has been resolved

Regards

Jehanzeb

Jehanzeb you are really a Expert...Finally i get the result the result i want....

Thanks a lot

Regards,

Danny

Ask a Question

Top Q&A Solution Author

User

Count