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)
Net UserReport WriterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
1
James0628Commented:
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
1
Net UserReport WriterAuthor 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
0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

mlmccCommented:
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
0
Net UserReport WriterAuthor 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
0
mlmccCommented:
Is this for a single account?

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

mlmcc
0
Net UserReport WriterAuthor 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
0
mlmccCommented:
To do that you will need the initial balances in a separate table associated with the cash fund id

mlmcc
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Net UserReport WriterAuthor Commented:
Thanks for your advice. I think I need to create a view for the cash fund id with initial balances.

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

mlmcc
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.