GL Account report month wise(pivot)

[former_member209725]

Dear Experts,

My client needed a report for Revenue and Expense Accounts as under for  financial year from 01-04-2013 to 31-03-14. I tried in system but  there is no such type of  report in standard system.

Account code               Account Name               JAN(bal.)          FEB(bal.)          MARCH(bal.)          APR(bal.).

Kindly suggest ideas to crack this report.

Regards,

Ravi

[kothandaraman_nagarajan · Accepted solution]

Hi Ravi,

Try this query 1

SELECT T0.[FormatCode], T0.[AcctName], T0.[CurrTotal],month(T0.[UpdateDate]) as month FROM OACT T0 WHERE T0.[ActType] = [%0]  and year ( T0.[UpdateDate]) =2013 and  T0.[UpdateDate] between [%1] and [%2] GROUP BY T0.[FormatCode], T0.[AcctName], T0.[CurrTotal],T0.[UpdateDate] order by T0.[UpdateDate]

OR try this query 2

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,(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]) = 2013 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

Let me know the result.

Thanks & Regards,

Nagarajan

[former_member209725 · Accepted solution]

Hi Naga Rajan,

Yeah, it's the 2nd query I was needed.

Thankx a load.

Regards,

Ravi

[Former Member]

Hi Ravi,

Create the report using Crystal Reports

Regards,

JP