How to create a crystal reports for these procedure?

Feb 12 2015 7:26 AM

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

Answers (4)

MD5 hash without salt

Difference between Method hiding and Method Overriding?