cancel
Showing results for 
Search instead for 
Did you mean: 

GL Query mothly wise

thobenjo1
Explorer
0 Kudos
152

Dear Experts ,

Can i get a GL Report like this, parameters for selection are project form to ,date range,gl accounts selection...please help

Accepted Solutions (0)

Answers (1)

Answers (1)

Pratik007
Explorer
0 Kudos

Dear Prakash,

Good day, kindly find the below query for your ready reference..

Select * from (

SELECT [Acctcode] as Acct#, [Acctname] as AcctName, ISNULL([1],0) as Jan, ISNULL([2],0) as Feb, ISNULL([3],0) as Mar, ISNULL([4],0) as Apr, ISNULL( [5],0) as May, ISNULL([6],0) as june, ISNULL([7],0) as July, ISNULL([8],0) as Aug, ISNULL([9],0) as Sept, ISNULL([10],0) as Oct, ISNULL([11],0) as Nov, ISNULL([12],0) as Dec

from

(SELECT T0.[FormatCode] as Acctcode, T0.[AcctName] as AcctName,sum(T1.[Debit]-T1.[Credit]) as Bal,month(T2.[RefDate]) as month FROM [dbo].[OACT] T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN OJDT T2 ON T1.TransId = T2.TransId WHERE T0.[ActType] = [%0] and T2.[RefDate] between [%1] and [%2] and year(T2.[RefDate]) = 2022 group by T0.[FormatCode], T0.[AcctName],T2.[RefDate],T1.[Debit],T1.[Credit]) s

Pivot

(sum(Bal) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

) KK Order By KK.Acct#

Now you have to set parameters for selection are project form to ,date range by yourself.

Thanks & regards,

Pratik Patel