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