I am trying to build a Trial Balance in Crystal report using the followings SQL Query as under :
SELECT T1.[TransId], T1.[Line_ID], T1.[Account], T0.[AcctName], T0.[GroupMask], T1.[Debit], T1.[Credit], T1.[FinncPriod], T1.[RefDate] FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
To a very much extent I am able to extract the correct data for the financial period. Now I also want a column of Opening balance to be shown in this report.
For example if I run the report for monthly basis the following should be shown :
Opening Balance Debit Credit Closing Balance
hence if suppose I run the report from 1st August 2009 to 31st August 2009 my report is generating wrong balances as closing balance of 31st June 2009 is not reflected in the report but the debit and credit balance is showing correct for the said period.
Do I need to create some formula in Crystal Report or is there a way to modify the above SQL query.
Any early response would be appreciated.