Create a new Query for account balance

[Former Member]

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

[Johan_H · Accepted solution]

Hi Norman,

As camall 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

[former_member615101]

SELECT 
   T0."AcctCode", T0."AcctName", 
   CASE WHEN T1."TransType" = 13  THEN 'IN - A/R Invoice'
        WHEN T1."TransType" = 14  THEN 'CN - A/R Credit Memo'
        WHEN T1."TransType" = 15  THEN 'DN - Delivery'
        WHEN T1."TransType" = 16  THEN 'RE - Returns'
        WHEN T1."TransType" = 162 THEN 'MR - Inventory Valuation'
        WHEN T1."TransType" = 18  THEN 'PU - A/P Invoice'
        WHEN T1."TransType" = 19  THEN 'PC - A/P Credit Memo'
        WHEN T1."TransType" = 20  THEN 'PD - Goods Receipt'
        WHEN T1."TransType" = 202 THEN 'PW - Production Order'
        WHEN T1."TransType" = 21  THEN 'PR - Goods Return'
        WHEN T1."TransType" = 24  THEN 'RC - Incoming Payment'
        WHEN T1."TransType" = 30  THEN 'JE - Journal Entry'
        WHEN T1."TransType" = 46  THEN 'PS - Outgoing/Vendor Payment'
        WHEN T1."TransType" = 58  THEN 'ST - Stock Update'
        WHEN T1."TransType" = 59  THEN 'SI - Goods Receipt'
        WHEN T1."TransType" = 60  THEN 'SO - Goods Issue'
        WHEN T1."TransType" = 67  THEN 'IM - Inventory Transfers'
        ELSE T1."TransType" 
   END as "TransType", 
   T1."LineMemo", 
   CASE WHEN T1."Credit" > 0 THEN T2."Comments" 
        WHEN T1."Debit"  > 0 THEN T3."Comments"
   END "Remarks", 
   T1."TransId", T1."BaseRef", T1."Debit", T1."Credit"
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
LEFT JOIN OVPM T2 ON T1."TransId" = T2."TransId"
LEFT JOIN ORCT T3 ON T1."TransId" = T3."TransId"  
WHERE 
   T0."Postable" = 'Y'
   AND T0."AcctCode" = '[%0]'
   AND (T1."RefDate" BETWEEN '[%1]' AND '[%2]')

[former_member419030]

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.

[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 🙂