February 22, 2015 at 11:17 pm
Hi Friends,
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... ..
production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1
(
responsibility varchar(500),
Dept varchar(500),
category varchar(500),
Actuals float
)
insert into #temp1 (
responsibility,
Dept,
category,
Actuals
)
select 'Sales','Sales (Net of Sales Tax)','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
like i m inserted so many accounts in temp tables .
IF (@catagory IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp
group by
responsibility,
category ,
dept
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
For my requirement i done procedure above its level 2 .
in Level 1
Expecting Output is
RESPONSIBILITY DEPT YTD ACTUALS YTD ACTUALS
(2014-2015) (2013-2014)
SALES NET RETURNS 422.10(net sales - TRD) 432.10 (net sales - TRD)
Finance LESS ED 40 30
Sales Totals Sales 382.10(RETURNS - ED) 402.10(RETURNS - ED)
production SALES 22 45
So i made procedure for Level1:
alter procedure Pl_Levl1
@fmdate datetime,
@todate datetime,
@catagory varchar(100)
as
begin
create table #temp1
(
responsibility varchar(500),
Dept varchar(500),
category varchar(500),
Actuals float
)
insert into #temp1 (
responsibility,
Dept,
category,
Actuals
)
--my original code
exec Pl_Levl2 @fmdate, @todate , @catagory
select
Responsibility,
Dept,
sum(Actuals) as 'YTD ACUTALS '
from
#temp1
where dept not in('Sales','Sales Return')
group by
Responsibility,
Dept
drop table #temp1
end
-- exec Pl_Levl1 '2011-01-01','2011-01-31',' '
here i can display only current YTD only how to display previous year (13-14) YTD in level1
Kindly Guide me Guys ?
How to do?
Thanks & Regards
Rocky
February 23, 2015 at 12:32 pm
I would pass a parameter use conditional logic to decide what to display.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply