Murphy1992
asked on
Crystal Reports - Account opening balance calculation
Pervasive database connected via ODBC to Crystal Reports 2011
There is probably an obvious answer to this question but it is eluding me.
The database doesnt hold opening and closing values for each of our Chart of Accounts. It calcaluates each time a period is requested.
For example:
Period 1/7/2012 - 31/12/12 requested
It calculates the opening balance for all transactions from the start of the database (2003 we started using) up to and including 30/6/12.
(1/7/2003 - 30/6/12) Account - Bank - Opening balance: 6561.00
It then lists all transactions for the nominated period adding and subtracting to create a closing balance.
Any thoughts on the best way to have crystal automatically calculate the opening balance?
I am think maybe a group calcuation - 1/7/2003 (start of database) less parameter period ?Start Date - 1 day...... followed by an account grouping.
Subesquent transactions will then for each account can then be applied in detail section.
Appreciate thoughts.
There is probably an obvious answer to this question but it is eluding me.
The database doesnt hold opening and closing values for each of our Chart of Accounts. It calcaluates each time a period is requested.
For example:
Period 1/7/2012 - 31/12/12 requested
It calculates the opening balance for all transactions from the start of the database (2003 we started using) up to and including 30/6/12.
(1/7/2003 - 30/6/12) Account - Bank - Opening balance: 6561.00
It then lists all transactions for the nominated period adding and subtracting to create a closing balance.
Any thoughts on the best way to have crystal automatically calculate the opening balance?
I am think maybe a group calcuation - 1/7/2003 (start of database) less parameter period ?Start Date - 1 day...... followed by an account grouping.
Subesquent transactions will then for each account can then be applied in detail section.
Appreciate thoughts.
vasto
Is this report for one account or for multiple ?
The only way you can do this is to select all records with dates before the end of the period and calculate the balance.
There is one way that might be a bit faster and easier is to put a sub report in the report header (single account at a time) or in the account group header (potential for multiple accounts in 1 run)
The sub report would select all the records for the account tat are less than the start date and calculate the opening balnce to display it and pass it back to the main report for further calculation. I like this because you don't have to worry about suppressing all the records you don't want to display that are required to calculate the opening balance.
mlmcc
There is one way that might be a bit faster and easier is to put a sub report in the report header (single account at a time) or in the account group header (potential for multiple accounts in 1 run)
The sub report would select all the records for the account tat are less than the start date and calculate the opening balnce to display it and pass it back to the main report for further calculation. I like this because you don't have to worry about suppressing all the records you don't want to display that are required to calculate the opening balance.
mlmcc
ASKER
Report is for multiple accounts.
I like the idea of subreport.
How do I pass the calcuated opening balance back to the main report?
This process would also fix the Summary Sum function for closing balance which currently calcuates on the whole data set rather than just opening + - transactions for nominated period.
Can you point me to any sample crystal reports?
Thanks
I like the idea of subreport.
How do I pass the calcuated opening balance back to the main report?
This process would also fix the Summary Sum function for closing balance which currently calcuates on the whole data set rather than just opening + - transactions for nominated period.
Can you point me to any sample crystal reports?
Thanks
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.