Avatar of Net User
Net User
 asked on

How to carry forward closing balance in crystal report

Hi everyone,
       I' m creating a "Sale Forwarding Report" using crystal report. I want to carry forward closing balance.This is closing balance formula in my report ;
closingBalance=(openingBalance+saleAmt)-(incomingPayment+discountAmt+advanceOpening+debt_Balance).
This report is printed look like:
No.     Content.    Cashfund-1......Cashfund-6
1.    openingBalance 50000.   ......12000
2.     saleAmt.              8500.     .......40000
3.      1+2.                     58500.   .....52000
4.    incomingPayment 3000 ... 20000
5.    discountAmt.        1500.   ....5000
6.   advanceOpening.   7500.  .....10000
7. debt-Balance.           -1500. ......-7500
8.     4+5+6+7.               10500 .......27500
9.  closingBalance(3-8). 48000.......24500
This is example data for january and I want to use january's closing balance as opening balance for february. In this report, I created two parameters - {?FromDate} and {?ToDate} .I want to pass these two parameters for only one month (for each month). How can I do this?

The following attachment file is testing for my report.

Kind Regards,
ZMTCrystal report formula (testing)
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
Mike McCracken

Are you asking can you run the report for January then in a separate run use the data from the January report to set the starting balances for February?

There is no way to do that in Crystal except by exporting the report data Excel and use that as an input to the next month's report.

mlmcc
James0628

Like mlmcc, I'm not sure what you're trying to do.  If Jan and Feb are included in the same report (so you're calculating the closing balance on the last record for Jan, and then using that figure on the next record (the first for Feb)), that shouldn't be a problem.  But if you're running separate reports for Jan and Feb, that's very different (as mlmcc described).

 I'm also not sure what you meant by "I want to pass these two parameters for only one month (for each month)".  Pass them from where to where?  And if you're only interested in one month, why have 2 parameters?

 Also, I don't think that the loop in your formula will do what you want it to do.  I could be wrong, but I'm guessing that you're trying to loop through the months and calculate a figure for each month?  If so, the problem is that whenever that formula is evaluated, the loop will be executed using only the values from the current record.  So, for example, if that formula was in a detail section, then when the first record was printed, the first x elements of the closing array would be filled using the SaleAmt, Payment, etc. values in the first record.  When the second record was printed, all of the values in the closing array from the first record would be replaced, using the SaleAmt, Payment, etc. values in the second record.  And so on.

 James
Net User

ASKER
Hi,
Thanks for all answer. Yes, I want to mean as 'mlmcc' described. The reason for passing two parameters is that I used "convert(date,{?FDate})" function for the field 'RefDate' in my query. The data type for the field 'RefDate' is datetime. How should I do this?
 

Kind Regards,
ZMT
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike McCracken

Are you building a Command as the data source?

If the parameter is setup up as a date or datetime then there is no need to do a conversion.

mlmcc
Net User

ASKER
Thanks. I am a fresh report writer. Yes,  I'm building a command as the data source. In my command, there are Items,cashfund,sale amiunt,incoming payment,discount amount,advance opening and debt-balance fields. Now, I' m trying to input 'opening balance' by manually as a parameter. Is it possible? Please.

Kind regards,
ZMT
Mike McCracken

Is this for a single account?

Crystal isn't intended to be used to update a database.

mlmcc
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Net User

ASKER
No.Not single account.This report works based on cashfund. And each cashfund has different 'opening balance'. I would like to solve this problem. Please.
Thanks,
ZMT
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Net User

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

That would work.  You can then join it to the query on the fund id.

mlmcc