We help IT Professionals succeed at work.

How to carry forward closing balance in crystal report

Net User
Net User asked
on
369 Views
Last Modified: 2018-02-08
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

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
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
CERTIFIED EXPERT

Commented:
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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Net UserReport Writer
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc