Opening balance in gl report

[Former Member]

Hi all,

         I am creating a customized  gl report. I want to add opening balance field in the report. I have credit and debit balances. How can i do that in crystal report. Please share some ideas.

Regards,

akram ali khan

[former_member184146]

Hi,

     use the below query as a sub report using Command

SELECT

J1.ShortName,sum(case when j1.Debit<>'0' then debit else  (Credit*-1) end) as OB

FROM

JDT1 J1

INNER JOIN OJDT J ON J1.TransId=J.TransId

WHERE

j.RefDate< '20150101'

GROUP BY

J1.ShortName

put the parameter based on main query start date paramater.

Regards,

Manish

[Former Member]

Dear Manish/Nrupal,

                              I have two fields credit and debit only . i dont have a field opening balance , i need to add a custom field.

Regards,

akram

[former_member184146]

Hi,

     which columns you want in report , post a screen shot of your report.

Regards,

Manish

[Former Member]

Hi Akram,

Are you sure there isn't some sort of balance field in the database? I understand what you are looking for but find it hard to believe that a software that includes accounting it doesn't a GL account balance - be it for the beginning of the year or each month.

If there really is no balance field whatsoever, this is going to be a slow report to generate as it will be pulling all transactions for each GL account in the report in order to calculate the opening balance - and that is only for the opening balance.

I would look into having a custom table, if possible, that stores the balance by GL account by month (and year). Whether the table is manually maintained or by a script is up to you.

If going by the debit/credit transactions is really the only option, you need to do a total by GL account from Jan. 1 to the starting date to get the opening balance. I don't know how the dr/cr values are stored in the db (i.e. are credits showing as a negative?), but that needs to be considered. If credits are negative, you can simply add the transactions up; otherwise there is probably a field to indicate it's a credit balance account and then the value should be multiplied by -1.

What type of financial report is this for - trial balance, balance sheet, income statement or something else? Depending on the type of report, you may also need to consider how the balance is displayed (ex. revenue is a credit balance but not displayed as a negative on income statements but would be on a trial balance).

For more help, as Manish indicated, having an idea of what the report looks like would help.

Hope that helps to get you started.

Heather

[Former Member]

Dear All,

                    I am working on a customized g/l report. Yes system is not saving opening balance . it is storing only debit and credit balances. Please find attached screen shot.

Please help me out on this issue

regards,

akram

[former_member226669]

Hi Akram,

You can create a Formula and use it in your report.

In Field Explorer, Right click on the Formula -> New give name and click OK.

Add the logic for Opening Balance and save it.

Thanks,

Nrupal

[Former Member]

Hi all,

        I need to display opening balance in the following way...

opening balance as on january 1 2014 -  0.000

opening balance as on august 1 2014 -  4225.500

Now when i run the report from august to dec 2014, report should show the opening balance as 4225.500.

[former_member226669]

Hi Akram,

Is Opening Balance a field in your database?

If you are using 2 different parameters for Start date and End date  then you can use this formula:

"opening balance as on " & Monthname(Month({?Start Date parameter})) & " 1 " & ToText(Year({?Start Date parameter})) & " - " & ToText({Opening Balance});

Thanks,

Nrupal