Skip to Content
avatar image
Former Member

Create a new Query for account balance

Hello,

I have a problem with COA account balance.
My Cumulative Balance (FC) displays data ****
so I decided to create a new query using Query Generator similar to account balance.

and I want to ask, where I can get OB (Opening Balance) to calculate Cumulative Balance (LC), Cumulative Balance (SC) and Cumulative Balance (FC)

is it possible?

Thanks for the time and cooperation

screenshot-1.jpg (236.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 13 at 08:36 AM

    Hi Norman,

    As mll md mentioned, you see the stars because the mechanism of the system report's column total is really only a sum of all figures in the column. As the FC column can hold many different currencies, it cannot show a reliable total.

    So you are on the right track. You need to write a query. There are two things to note:

    1. The opening balance you see in the system report, is not a value stored anywhere, it is a calculated number. That means that you will have to "double" your query. In pseudo sql code you will have to do this:

    SELECT SUM(SomeValue) AS OpeningBalance 
    FROM SomeTable 
    WHERE DateField Between @BeginningOfTimes AND @TheDayBeforeYourStartingDateParameter
    UNION ALL
    SELECT SUM(SomeValue) AS Balance 
    FROM SomeTable 
    WHERE DateField Between @YourStartingDateParameter AND @YourEndingDateParameter

    2. As the field values are in different currencies, you will have to be careful to include the ORTT table, and convert each sum to a common currency (probably system currency?), before you add them together. So again in pseudo sql code:

    SELECT SUM(SomeValue * RateForTheGivenCurrency) AS Balance

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 12 at 08:20 AM

    Dear Norman,

    it display ***** because the balance involved more than two currency.

    I think you cannot query direct from any table, but have to create mathematical formula to calculate the cumulative balance.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Dear mll md,

      thanks for the answer.
      sorry, but i can't understand where i can create mathematical formula on SAP?
      should i create a new Query?

      thank you :)