Skip to Content
avatar image
Former Member

sap business one query manager parameters for pivot table

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Mar 01, 2017 at 06:02 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 01, 2017 at 06:25 AM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 01, 2017 at 06:36 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 01, 2017 at 06:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded