Chart of Accounts Report

Dear Experts,
i need to create either a Query or a crystal report to Display chart of accounts same as it display on char of account screen ( Draws, Levels and accounts hierarchy wise).
regards

 Answer 

You have to update the query based on number of level you have in COA


select TBL1.[GROUP],(TBL1.[AcctCodeLevel-1] + ' - ' + TBL1.[Acct Name Level-1]) [Level-1],
(TBL1.[AcctCodeLevel-2] + ' - ' + TBL1.[Acct Name Level-2]) [Level-2],(TBL1.[AcctCodeLevel-3] + ' - ' + TBL1.[Acct Name Level-3]) [Level-3] ,
(TBL1.[AcctCodeLevel-4]  + ' - ' + TBL1.[Acct Name Level-4]) [Level-4],
(TBL1.[AcctCodeLevel-5] + ' - ' + TBL1.[Acct Name Level-5]) [Level-5]

from (
     SELECT t0.groupmask, t0.grpline, t0.levels, T0.[AcctCode]  [AcctCodeLevel-1],
T0.[AcctName] [Acct Name Level-1],'-' [AcctCodeLevel-2] ,'-' [Acct Name Level-2],'-' [AcctCodeLevel-3],'-' [Acct Name Level-3]
,
'-' [AcctCodeLevel-4],'-' [Acct Name Level-4],'-' [AcctCodeLevel-5],'-' [Acct Name Level-5],

(case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES' END) [GROUP]
  FROM OACT T0 WHERE t0.Levels = '1'
  union all

SELECT t0.groupmask,t0.grpline,t0.levels,'-','-', T0.[AcctCode], T0.[AcctName],'-','-','-','-','-','-',
  (case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES' END) [GROUP]
  FROM OACT T0 WHERE t0.Levels = '2'
union all

SELECT t0.groupmask,t0.grpline,t0.levels,'-','-','-','-', T0.[AcctCode], T0.[AcctName],'-','-','-','-',
  (case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES' END) [GROUP]
FROM OACT T0 WHERE t0.Levels = '3'

       union all

SELECT t0.groupmask,t0.grpline,t0.levels,'-','-','-','-','-','-', T0.[acctcode], T0.[AcctName],'-','-',
  (case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES'  END) [GROUP]
  FROM OACT T0 WHERE t0.Levels = '4'

union all

SELECT t0.groupmask,t0.grpline,t0.levels,'-','-','-','-','-','-','-','-', T0.[formatcode], T0.[AcctName],
  (case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES'  END) [GROUP]

FROM OACT T0 WHERE t0.Levels = '5'
         ) TBL1

ORDER BY
tbl1.groupmask,tbl1.grpline,tbl1.levels


Thread Link :  http://scn.sap.com/thread/2085932