Mar 01, 2017 at 03:35 AM

sap business one query manager parameters for pivot table

680 Views Last edit Mar 01, 2017 at 06:01 AM 4 rev

I'm trying to create a pivot table query in SAPB1 where my column header as project codes and row details as Account & AcctName. I have no problem passing the Project parameter but when I change the date parameter to the following

Declare @d1 date = /* SELECT FROM dbo.JDT1 T0 WHERE T0.RefDate >= */ ''[%2]''

Declare @d2 date = /* SELECT FROM dbo.JDT1 T0 WHERE T0.RefDate >= */ ''[%3]''

I'm stuck with this error 1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'CONVERT'. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared. Can someone help me with this issue please. The query below works fine without the date parameter.

DECLARE @Programs nvarchar(max)

SELECT @Programs = STUFF( ( select distinct ',[' + Project + ']' from dbo.JDT1 T0 whereT0.Project between ('[%0]') and ('[%1]') order by ',[' + Project + ']' for xml path('') ), 1, 1,'')

--------------------------------------------------------------------------------------------

DECLARE @PNL nvarchar(max)

SELECT @PNL = N'

Declare @d1 date = /* SELECT FROM dbo.JDT1 T0 WHERE T0.RefDate >= */ ''2016-07-01''

Declare @d2 date = /* SELECT FROM dbo.JDT1 T0 WHERE T0.RefDate <= */ ''2016-12-31''

SELECT *

From (

SELECT T0.Account, T1.AcctName, (T0.Project) as [Project], (Isnull(T0.Credit,0) - Isnull(T0.Debit,0) ) as [Amount]

FROM JDT1 T0 Inner Join OACT T1 on T1.AcctCode = T0.Account

where T0.Account >= (4000000)

and T0.Refdate >= convert (varchar(8), @d1, 112) and T0.Refdate <= convert (varchar(8), @d2, 112) and T0.Project is not null

) Data

PIVOT ( Sum(Amount) FOR Project IN ( ' + @Programs + ' ) ) PivotTable '

exec sp_executesql @PNL

Thank in advance!