Home > Enterprise >  ¿How to load a parameter in a query in quotes in BIRT?
¿How to load a parameter in a query in quotes in BIRT?

Time:12-20

I have a query that inside in quotes:

DECLARE @PivotQuery AS NVARCHAR(MAX),
        @PivotColumns AS NVARCHAR(MAX),
        @PivotNull AS NVARCHAR(MAX)
SELECT @PivotColumns= ISNULL(@PivotColumns   ',','')   QUOTENAME(DepId)
FROM (SELECT DISTINCT sp.DepId FROM SKDEP sp) AS Stock
SELECT @PivotNull 
    = ISNULL(@PivotNull   ',','')
      'ISNULL('   QUOTENAME(DepId)   ', 0) AS '
      QUOTENAME(DepId)
FROM (SELECT DISTINCT sp.DepId FROM SKDEP sp) AS Stock
SET @PivotQuery =
N'SELECT P.P, P.D, ' @PivotNull '
FROM
        (
            select 
                s.prdid as P,
                p.prddsc as D,
                case when mstes=''S'' then sum(-MstCntDisp) else sum(MstCntDisp) end as Stock,
                s.DepId
                from SKMOV S
                join PRODUC P on s.PrdId=p.PrdId
                where s.mstsal>0 AND s.MstTpo=''S'' and s.PrdId=**''*TF033''**
                group by s.prdid, p.PrdDsc, s.MstES, s.depid
        ) AS t
PIVOT(sum(Stock)
FOR DEPID IN ('   @PivotColumns   ')) AS P'
EXEC sp_executesql @PivotQuery

In the where clause I need specific my parameters with the question mark, so:

and s.PrdId=?

But I don't know how to specify the parameter when having the query in quotes, because it doesn't take it.

CodePudding user response:

Finally i resolved my problem that this way:

In the where clause, im pass value of one string,

where s.mstsal>0 AND s.MstTpo=''S'' and s.PrdId=''PRODUCT_ID''

And after when report open (in tab Script/beforeOpen), im replace the string value with the parameter.

this.queryText=this.queryText.replace("PRODUCT_ID",params["ID"].value);

And works!

  • Related