Query Code

[Former Member]

Hi Expert,

Its me again... below is my simple query. How can I make the 'balance' column to be the running balance of Debit less Credit column?

SELECT T0.[TransId], T0.[RefDate],T1.[ShortName], T1.[Debit], T1.[Credit], T1.[Debit]- T1.[Credit] as 'Balance' FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.[ShortName] = '10301' and T1.[ProfitCode] = [%0] and T0.[RefDate] between '[%1]' and '[%2]'

Thanks and looking forward for your feedback.

Regards,

ArlmiPJ

[Former Member · Accepted solution]

Hi

SELECT T0.TransId, T0.RefDate,T1.ShortName, T1.Debit, T1.Credit, T1.Debit- T1.Credit as 'Balance' ,
(Select Sum(Debit-Credit) from JDT1 M1 where M1.TransID <=T1.TransID
 and M1.Shortname=T1.Shortname 
and M1.ProfitCode=T1.ProfitCode) [Cumulative Balance]
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.ShortName = '10301' and T1.ProfitCode = %0 and T0.RefDate between '[%1]' and '[%2]'

[former_member541807]

hi,

actually you can achieve the same output by using the SAP Standard Reports. use account balance, how to do it? follow this..

Use this window to view the transactions posted to a G/L account or a business partner. According to the transactions, the balance of the account or the business partner is calculated.

To open the Account Balance Window for a G/L account, from SAP Business One Main Menu, choose  Financials  Chart of Accounts , select the account, and  (Link Arrow) in the Balance field.

regards,

Fidel

[Former Member]

Hi,

Thanks for your reply... yes I can do the report using standard reporting however I cannot break it down by profit center that's why I want to do it via query. if you notice in my query there is a profit center filter.

Thanks,

ArlmiPJ

[Former Member]

Hi

I already figure it out by profit center using SAP GL selection criteria.

Thanks,

ArlmiPJ