Parameters in Pivot for SAP B1 report

[krishnam_mathur]

Hi Team,

I need help to pass parameters in attached query

Can anyone please help me..?

[krishnam_mathur]

declare @month integer

declare @month_2 integer

declare @startdate datetime

declare @enddate datetime

declare @costcenter varchar(20)

set @month_2= 3--/* t3.finncPriod=*/ '[%0]'

set @month=@month_2-1

set @costcenter='88115100'

set @startdate= '03/01/2019'--/*t0.docdate from oinm t0 where t0.docdate>=*/'[%1]'

set @enddate= '03/31/2019'--/*t0.docdate from oinm t0 where t0.docdate>=*/'[%2]'

select * from

( select t0.name+'-Budget' as 'toPivot', t4.AcctCode as 'GLAccount',t4.AcctName,isnull(t2.DebLTotal,0) as 'value' from obgs t0 inner join obgt t1 on t0.AbsId=t1.Instance inner join bgt1 t2 on t2.Instance=t1.Instance and t2.AcctCode=t1.AcctCode and t2.Line_ID=@month inner join oact t4 on t4.AcctCode=t2.AcctCode where t0.name=@costcenter union all select t0.name+'-Actual' as 't0Pivot',t4.AcctCode as 'GLAccount',t4.AcctName,isnull(sum(debit-credit),0) as 'value' from jdt1 t3 left join oact t4 on t4.AcctCode=t3.Account left join obgs t0 on t0.Name=t3.ProfitCode left join obgt t1 on t1.Instance=t0.AbsId and t4.AcctCode=t1.AcctCode left join bgt1 t2 on t1.Instance=t2.Instance and t2.AcctCode=t1.AcctCode and t2.line_ID=2 where t3.refdate between @startdate and @enddate and t4.ExportCode is not null and t0.name=@costcenter group by t0.name,t4.AcctCode,t4.AcctName

) a pivot (sum(a.value) for a.topivot in

([88115100-Budget], [88115100-Actual])) as pivottable

[kothandaraman_nagarajan]

Hi,

Share your query in txt format to run and check.

Regards,

Nagarajan