sap business one query manager parameters for pivot table

[rm_sebastian]

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!

[rm_sebastian]

Tried but still doesn't work. Gives me these errors

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'whereT0'.

2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '20160701'.

3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'R

[kothandaraman_nagarajan]

Try this,

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 >= */ '[%2]'

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

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

[rm_sebastian]

Thanks for your quick response Nagarajan K. As previously explained, I wanted to change the following dates '2016-07-01' & '2016-12-31' in to a parametized query ie. '[%2]' & '[%3]' so users could fill in transaction dates when they run the query. My query does not seem work and throws "Incorrect syntax near the keyword 'CONVERT' " error. See below

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 >= */ ''[%2]''

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

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

Hope this makes sense.

Thanks and more power!

Cheers!

[kothandaraman_nagarajan]

Hi,

Try this,

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

Regards,

Nagarajan