Skip to Content
author's profile photo Former Member
Former Member

Which SQL table holds opening balance?

Dear all,

I want to use crystal report to draw financial information directly from SQL database.

However, I don't know which SQL table stores the open/ending balance of account item

of a given period.

Any one can help?

Norman.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 21, 2008 at 08:10 AM

    You logic is correct. Just one thing need to be clear that this sum may be negative for later periods. You have to include all debits and credits.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2008 at 05:20 PM

    Hi

    Your trans type is -2 for Opening balance and it is in OJDT table

    Run query like this

    SELECT * FROM OJDT T0 where TransType=-2

    You will see the all opening balance

    Thank you

    Bishal

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2008 at 04:19 PM

    Dear Norman,

    All transaction JEs are in the OJDT and JDT1 table. However, you can only find initial opening balance transactions. OB for any specific periods other than the initial periods have to be calculated.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Dear Gordon,

      So how is the calculation? Below is my guess. Please comment.

      For example, opening balance of account 1234 is $100DB.

      If I want to find opening balance of account 1234 as at period 2.

      Summing up all journal entries in period 1 to find the total debit/credit.(for example : $150DB)

      And add up the two ($100DB + $150DB) to get $250DB.

      This is the opening balance of account 1234 as at period 2.

      For opening balance as at period 3, sum up all journal entries in period 1 and 2.

      Add the result to opening balance.

      Please comment.

      I wonder if SDK DI has any COM object to get the result directly.

      Thanks.

      Norman.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.