How to carry forward closing balance in crystal report

Net User
Net User used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
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 UserReport Writer

Author

Commented:
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 UserReport Writer

Author

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is this for a single account?

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

mlmcc
Net UserReport Writer

Author

Commented:
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
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
To do that you will need the initial balances in a separate table associated with the cash fund id

mlmcc
Net UserReport Writer
Commented:
Thanks for your advice. I think I need to create a view for the cash fund id with initial balances.

ZMT
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial