How to create a crystal reports for these procedure?

Hi Friends,

      i m creating P&L reports in these reports i had 2 levels
1) Level -I
         i had to calculate the sum of values of each accounts
2)Level - II
            in these level i had map level3 sum vales in particular  group and dept ,Category
like drill down concept

i made the procedure like below



alter procedure Pl_Levl2        
 @fmdate datetime,                    
 @todate datetime,
 @catagory varchar(100)       
      
                   
as         
 begin         
  create table #temp         
    (        
    responsibility varchar(500),        
    Dept           varchar(500),        
    category       varchar(500),        
    Fs_accounts    nvarchar(800),        
    Actuals         float,        
    )        
        
    insert into #temp (        
     responsibility,        
     Dept,        
     category,        
     Fs_accounts,        
    Actuals        
     )        
    select 'Sales','Sales','Sales (Net of Sales Tax)',             
  v.fs_accounts,            
  v.sales            
from              
(            
select         
coalesce(fs_account_no,'Total SALES TO THIRD PARTIES') as fs_accounts,            
sum(case when  left(fs_account_no,1) ='R' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2) * -1            
                  when   left(fs_account_no,1) ='R' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2)             
                 else 0            
                  end             
                 )as Sales             
From             
FMS..fs_postings(nolock)            
where            
 fs_tran_date between @fmdate and @todate            
and             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','E','L')and fs_account_no in ('R001-AP100-1001','R001-OT100-1004','R001-OT100-1005','R001-EX100-1008','
 
    
      
R001-EX100-1006')            
            
group by             
               
   fs_account_no with  Rollup            
            
)v        
        
union all         
        
select 'Sales','Sales','LESS:-DISCOUNT (TRD)',                           
 o.fs_accounts,                            
 o.sales                                       
from                             
(                            
select                             
coalesce(fs_account_no,'Total INDIRECT REVENUE DEDUCTIONS') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                         
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E006-SA100-2203','E006-SA100-2320','E006-SA100-2324')                            
                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)o                            
union all        
select 'Finance','Sales','LESS:-EXCISE DUTY' ,                    
 w.fs_accounts,                      
 w.sales                            
from                      
(                      
select                       
coalesce(fs_account_no,'Total OTHER TAXES & FEES') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
                 else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E008-ED100-2062')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
)w                      
union all        
select  'HR','Personnel Cost','SALARIES & WAGES',                       
 f.fs_accounts,                          
 f.sales                           
                          
from                          
(                          
select                           
coalesce(fs_account_no,'Total SALARIES & WAGES') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                           
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end                           
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate                          
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E002-SW100-2100','E002-SW100-2106','E002-SW100-2103','E002-SW100-2105','E002-SW100-2104','E002-SW100-2114','E002-SW100-2113')                          
                          
                          
group by                           
                             
   fs_account_no with  Rollup                          
                          
                          
)f                          
        
union all        
        
        
select 'HR','Personnel Cost','SOCIAL WELFARE EXPENSES',                        
  g.fs_accounts,                          
  g.sales                                
from                          
(                          
select                           
coalesce(fs_account_no,'Total SOCIAL WELFARE EXPENSES') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                           
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end                           
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate                          
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E003-SW101-2182','E003-SW101-2153','E003-SW101-2164','E003-SW102-2176','E003-SW101-2165','E003-SW102-2175','E003-SW101-2152','E003-SW101-2150','E003-SW101-2159','E003-SW101-2157','E002-SW100-2110','E002-SW100-2111','E003-SW102-2179','E
 
    
     
003-SW101-2166')                          
                                            
group by                           
                             
   fs_account_no with  Rollup                          
)g                          
        
union all        
        
select 'HR','Personnel Cost',  'EXCEPTIONAL PERSONAL EXPENSES',                       
 h.fs_accounts,                          
 h.sales                     
                          
from                          
(                          
select                           
coalesce(fs_account_no,'Total EXCEPTIONAL PERSONAL EXPENSES') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)          
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end                           
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate                           
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E003-SW102-2182','E003-SW102-2180','E003-SW101-2154','E003-SW101-2160')                          
                          
                          
group by                           
                             
   fs_account_no with  Rollup                          
)h                          
union all        
        
select 'HR','Personnel Cost','ADDITONAL PERSONAL EXPENSES',                          
 i.fs_accounts,                          
 i.sales                                
from                           
(                          
select                           
coalesce(fs_account_no,'Total ADDITONAL PERSONAL EXPENSES') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                           
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end                           
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate                          
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E002-SW100-2107','E002-SW100-2108','E002-SW100-2109')                          
                          
                          
group by                           
                             
   fs_account_no with  Rollup                          
)i                         
        
union all        
        
select  'HR','Personnel Cost','OUTSIDE SERVICE FROM THIRD PARTIES',                           
  j.fs_accounts,                            
  j.sales                             
                            
from                            
(                            
select                             
coalesce(fs_account_no,'Total OUTSIDE SERVICE FROM THIRD PARTIES') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E002-SW100-2124','E004-WW100-2580','E002-SW100-2120','E002-SW100-2121','E002-SW100-2122','E002-SW100-2123','E002-SW100-2126','E004-FR100-2205')                            
                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
            
)j                            
union all        
        
select   'Materials & production','COGS','MATERIAL CONSUMPTION OTC' ,                     
 b.fs_accounts,                            
 b.sales                             
                            
from                            
(                            
select                             
coalesce(fs_account_no,'Total Material Consumption') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate --@fmdate and @todate         
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E001-RM101-2053','E001-RM101-2061','E001-CG100-2099','E001-RM102-1992','E001-RM102-2010','E001-RM102-2052','E001-RM102-2054','E001-RM102-2055','E001-VA100-2109','E001-RM102-2057','E001-RM102-2058')                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
)b                            
union all        
        
select 'Materials & production' ,'COGS','COST OF AGENCY PRODUCTS',                         
 c.fs_accounts,                          
 c.sales                          
                          
from                           
(                          
select                           
coalesce(fs_account_no,'Total COST OF AGENCY PRODUCTS') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                           
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end             
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate                           
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E001-RM103-2059','E001-RM103-2092')                          
                          
                          
group by                           
                             
   fs_account_no with  Rollup                          
                          
)c                          
union all        
        
select  'Materials & production','COGS','VARIANCE',        
  d.fs_accounts,                          
  d.sales                           
                          
from                           
(                          
select                           
coalesce(fs_account_no,'Total VARIANCE') as fs_accounts,                          
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                           
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                          
                 else 0                          
                  end                           
                 )as Sales                           
From                           
FMS..fs_postings(nolock)                          
where                          
 fs_tran_date between @fmdate and @todate      
and                           
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                           
fs_account_no in ('E001-VA100-2097','E001-VA100-2098','E001-VA100-2099','E001-VA100-2100','E001-VA100-2101')                          
                          
                          
group by                           
                             
   fs_account_no with  Rollup                          
)d                          
union all        
select  'Production','COGS','FUEL & POWER',                          
 l.fs_accounts,                       
 l.sales                             
                           
from                             
(                            
select                             
coalesce(fs_account_no,'Total FUEL & POWER') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E002-PW100-2030','E002-PW100-2031','E002-PW100-2060','E002-PW100-2032')                            
                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)l         
union all        
        
select  'Production','COGS','REPAIRS & MAINTENANCE FROM THIRD PATIES',                          
m.fs_accounts,                            
m.sales                                     
from                            
(                            
select                             
coalesce(fs_account_no,'Total REPAIRS & MAINTENANCE THIRD PATIES') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                              
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E005-TR101-2578','E004-RE100-2051','E003-SW102-2177','E003-SW102-2178','E004-RE100-2846','E004-RE100-2041','E004-RE100-2042','E004-RE100-2045','E004-RE100-2040','E004-RE100-2043','E004-RE100-2046','E004-RE100-2047','E005-TR101-2587','E
 
    
      
004-RE100-2048','E004-RE100-2049','E004-RE100-2044','E005-TR101-2532','E004-RE100-2050','E004-RE100-2052')                            
                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
)m                            
union all        
        
select 'Marketing','Advertisement Expenses','PROMOTIONAL EXPENSES',                    
  r.fs_accounts,                      
  r.sales                             
from               
 (                      
select                       
coalesce(fs_account_no,'Total PROMOTIONAL EXPENSES') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
                 else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate --                      
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E006-SA100-2332','E006-SA100-2334','E006-SA100-2333','E005-AD100-2311','E005-AD100-2303','E005-AD100-2300','E005-AD100-2329','E006-SA100-2327','E006-SA100-2330','E005-AD100-2314')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
)r                      
union all        
        
select  'SCM','FREIGHT & TRANSPORTATION','FREIGHT & TRANSPORTATION',                           
 p.fs_accounts,                            
 p.sales                                      
from                            
(                            
select                             
coalesce(fs_account_no,'Total OUTBOUND FREIGHT') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                          
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                              
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E004-FR100-2217','E004-FR100-2218','E004-FR100-2211','E004-FR100-2199','E004-FR100-2215','E004-FR100-2216','E004-FR100-2200','E004-FR100-2201','E004-FR100-2223')                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)p                
union all        
        
select 'Sales','Selling Expenses & Commission','SPECIAL SELLING EXPENSES',                           
 q.fs_accounts,                            
 q.sales                                        
from                             
(                            
select                             
coalesce(fs_account_no,'Total SPECIAL SELLING EXPENSES') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
      when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                       
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and           
fs_account_no in ('E006-SA100-2206','E006-SA100-2241','E006-SA100-2220','E006-SA100-2336','E006-SA100-2339')                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)q                            
union all        
select  'HR','Travel & Admin','Travelling',        
 k.fs_accounts,                            
 k.sales                            
                           
from                             
(                            
select                             
coalesce(fs_account_no,'Total TRAVELLING & REPRESENTATION') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                              
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E005-TR100-2230','E005-TR100-2552','E005-TR100-2232','E005-TR100-2551','E005-TR100-2553','E005-TR100-2233','E005-TR101-2555','E005-TR101-2556','E005-TR101-2042','E005-TR100-2234','E005-CN100-2554','E005-TR101-2579','E005-TR101-2580','E
 
    
      
002-SW100-2112','E005-TR100-2550','E005-TR100-2554')                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)k                            
union all        
select  'HR','Travel & Admin','Communication',                   
 z.fs_accounts,                    
z.sales                         
from                    
(                    
select                     
coalesce(fs_account_no,'Total TELECOMMUNICATIONS') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                    
                 else 0                    
                  end                     
                 )as Sales                     
From                     
FMS..fs_postings(nolock)                    
where                    
 fs_tran_date between @fmdate and @todate                     
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                     
fs_account_no in ('E005-CE100-2535','E005-CE100-2537','E005-PO100-2530','E005-PO100-2541','E005-CE100-2539','E005-CE100-2538')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
)z                    
union all        
select  'HR','Travel & Admin','Insurance',                   
 ae.fs_accounts,                    
 ae.sales                        
from                    
(                    
select                     
coalesce(fs_account_no,'Total INSURANCE') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                    
                 else 0                    
                  end                     
                 )as Sales                     
From                     
FMS..fs_postings(nolock)         
where                    
 fs_tran_date between @fmdate and @todate                   
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                     
fs_account_no in  ('E008-IS100-2512','E008-IS100-2515','E008-IS100-2510','E008-IS100-2513','E008-IS100-2516','E008-IS100-2517','E008-IS100-2518','E008-IS100-2519','E008-IS100-2520','E008-IS100-2521','E008-IS100-2522','E008-IS100-2523','E008-IS100-2524','
 
E    
      
008-IS100-2514')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
                    
)ae                    
union all        
select 'HR','Rates & Taxes','OTHER TAXES & FEES',                      
 v.fs_accounts,                      
 v.sales                             
from                      
(                      
select                       
coalesce(fs_account_no,'Total OTHER TAXES & FEES') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
        else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E008-RT100-2520','E005-TR101-2504','E008-RT100-2525','E001-RM102-2222','E008-ST100-2213','E008-RT100-2212','E008-ST100-2210')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
                      
)v                
union all        
select  'Finance','PROFESSIONAL SERVICES','PROFESSIONAL SERVICES',                     
s.fs_accounts,                      
s.sales                
from                      
(                      
select                       
coalesce(fs_account_no,'Total CONSULTANCY') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
                 else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E005-LE100-2564','E003-CS100-2563','E003-CS100-2565')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
                      
)s                      
        
union all        
        
        
select   'IT','IT Cost','EDP EXPENSES',                          
  j.fs_accounts,                            
  j.sales                             
                            
from                            
(                            
select                             
coalesce(fs_account_no,'Total OUTSIDE SERVICE FROM THIRD PARTIES') as fs_accounts,                            
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E002-SW100-2124','E004-WW100-2580','E002-SW100-2120','E002-SW100-2121','E002-SW100-2122','E002-SW100-2123','E002-SW100-2126','E004-FR100-2205')                            
                            
                            
group by                             
                               
   fs_account_no with  Rollup                            
                            
)j                            
union all        
        
Select 'Finance','Bank Charges','Bank Charges',                    
 ag.fs_accounts,                    
 ag.sales                    
                    
from                     
(                    
select                     
coalesce(fs_account_no,'Total FINANCIAL EXPENSES') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                    
                 else 0                    
                  end                     
                 )as Sales                     
From                     
FMS..fs_postings(nolock)                    
where                    
 fs_tran_date between @fmdate and @todate                     
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                     
fs_account_no in  ('E007-IN100-2625','E007-IN101-2630','E007-IN101-2626','E007-IN101-2627','E007-IN101-2628','E005-BK100-2588','E005-BK100-2595','E005-BK100-2575','E005-BK100-2577','E009-BD100-2569')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
                    
)ag                    
union all        
select  'HR','Others','RENTALS & LEASING',                           
 n.fs_accounts,                            
 n.sales                                      
from                            
(                            
select                             
coalesce(fs_account_no,'Total RENTALS & LEASING') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                             
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                            
                 else 0                            
                  end                             
                 )as Sales                             
From                             
FMS..fs_postings(nolock)                            
where                            
 fs_tran_date between @fmdate and @todate                             
and                             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                             
fs_account_no in ('E005-RE100-2501','E005-RE100-2500','E005-RE100-2559','E005-RE100-2502','E005-RE100-2503')                            
                            
                            
group by                             
                               
 fs_account_no with  Rollup                            
                            
)n                            
union all        
select 'Finance','Others','AUDIT FEES',                      
 t.fs_accounts,                      
 t.sales                             
from                       
(                      
select                       
coalesce(fs_account_no,'Total AUDIT FEES') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
                 else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E005-AU100-2560','E005-AU100-2561','E005-AU100-2562')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
                      
)t                      
union all        
        
select  'Finance','Others','DONATIONS & OTHERS',                     
 x.fs_accounts,                      
 x.sales                            
from                      
(         
select                       
coalesce(fs_account_no,'Total DONATIONS & OTHERS') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
                 else 0                      
                  end                       
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E005-DN100-2573','E005-TR101-2571','E005-TR101-2573','E005-TR101-2574')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
                      
)x                      
union all        
select 'HR','Others','BOOKS & STATIONERY',        
 y.fs_accounts,                    
 y.sales                         
from                    
(                    
select                     
coalesce(fs_account_no,'Total BOOKS & STATIONERY') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                    
                 else 0                    
                  end                     
                 )as Sales                     
From                     
FMS..fs_postings(nolock)                    
where                    
 fs_tran_date between @fmdate and @todate                     
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                     
fs_account_no in ('E005-TR101-2572','E005-PR100-2545','E005-PR100-2546','E005-PR100-2547')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
)y        
union all        
select 'Finance','Others','MISCELLANEOUS EXPENSES',         
af.fs_accounts,                    
 af.sales                        
from                     
 (                    
select                     
coalesce(fs_account_no,'Total MISCELLANEOUS EXPENSES') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                    
                 else 0                    
                  end                     
       )as Sales                     
From                     
FMS..fs_postings(nolock)                    
where                    
 fs_tran_date between @fmdate and @todate                    
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                     
fs_account_no in  ('E005-TR101-2588','E005-TR101-2589','E005-TR101-2585','E005-DR100-2574')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
)af        
union all        
select 'Finance','Others','BAD DEBT LOSSES-ASSETS DISCARDED',                     
 u.fs_accounts,                   
 u.sales                             
from                      
(                      
select                       
coalesce(fs_account_no,'Total BAD DEBT LOSSES') as fs_accounts,                      
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                       
                  when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                      
        else 0                      
                  end                     
                 )as Sales                       
From                       
FMS..fs_postings(nolock)                      
where                      
 fs_tran_date between @fmdate and @todate                       
and                       
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')and                       
fs_account_no in ('E009-BD100-2346','E009-BD100-2345','E010-AD100-2651','E010-AD100-2650')                      
                      
group by                       
                         
   fs_account_no with  Rollup                      
                      
)u        
union all        
Select 'Finance','Others','DEPRECIATION',                 
 aj.fs_accounts,                    
 aj.sales                    
     
from                     
(                    
select                     
coalesce(fs_account_no,'Total DEPRECIATION') as fs_accounts,                    
sum(case when  left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2)                     
         when   left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2) * -1                     
                 else 0                    
                  end                     
                 )as Sales                     
From                     
FMS..fs_postings(nolock)                    
where                    
 fs_tran_date between @fmdate and @todate                     
and                     
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','R','L')                    
and fs_account_no in  ('E011-DE100-2701','E011-DE100-2702','E011-DE100-2703','E011-DE100-2704','E011-DE100-2705','E011-DE100-2706','E011-DE100-2707','E011-DE100-2708')                    
                    
group by                     
                       
   fs_account_no with  Rollup                    
                    
)aj           
union all        
select 'Finance','Others','NON OPERATING INCOME / (LOSS)',           
 h.fs_accounts,            
 h.sales            
from            
(            
select             
coalesce(fs_account_no,'Total OTHER INCOME FROM 3RD PARTIES') as fs_accounts,            
sum(case when  left(fs_account_no,1) ='R' and fs_dr_cr_code='Dr'  then round(fs_post_amt,1,2) * -1            
                  when   left(fs_account_no,1) ='R' and fs_dr_cr_code='Cr'  then round(fs_post_amt,1,2)             
                 else 0            
                  end             
                 )as Sales             
From             
FMS..fs_postings(nolock)            
where            
 fs_tran_date between @fmdate and @todate             
and             
fs_locn_code not in ('APMC','SDAR','SDAB','SDPL','SDHB','SDBB','SDCB','SHUB','SDMB','SMUM','SDOB','SDTB','SVIJ') and  left(fs_account_no,1) not In ('A','E','L')and            
 fs_account_no in ('R002-MI100-1443','R002-MI100-1441','R002-MI100-1411','R002-MI100-1403','R002-MI100-1440','R002-MI100-1404','R002-MI100-2800','R002-MI100-1449')            
            
group by             
               
   fs_account_no with  Rollup            
            
)h            
 
      
IF (@catagory IS  NULL  )        
  begin        
            
 select         
   responsibility,             
   dept,            
   category,          
      round((Max(Actuals)/100000),1,2)as Actuals from #temp        
        
group by         
    responsibility,             
   dept,            
   category     
       
end        
     
       
else         
 begin        
          
   select responsibility, dept,category,fs_accounts,Actuals from #temp where category = @catagory  group by responsibility,category,fs_accounts,Actuals,dept        
     
     
end        
       
    
        
  drop table #temp         
end        
        
        
--exec Pl_Levl2 '2012-01-01','2012-01-31','insurance'       
        
        
        
      
    
 
Here the problem i dont know how to display in crystal reports(Newbie of that reporting tool),Kindly Give suggestion ?


Thanks
rocky