Dynamic query with IReport 4.5.1 and JasperServer 4.5

[jd6strings]

Hello  All:

I've created a report in IReport with a dynamic query such that the query is adjusted depending upon the existence of a input parameter.  In other words the query parameters are appended to the SQL statement depending upon whether or not a parameter is empty.  The query looks like this:

SELECT
    alpha_FILLDATA."FillDate", alpha_FILLDATA."FillAmount", alpha_FILLDATA."InvoiceNo",
    alpha_MTRDATA."Well_Name",
    alpha_OPFILE."OpName"
FROM
    { oj ("gas"."dbo"."alpha_FILLDATA" alpha_FILLDATA INNER JOIN
"gas"."dbo"."alpha_MTRDATA" alpha_MTRDATA ON
        alpha_FILLDATA."OpCode" = alpha_MTRDATA."Op_Code" AND
    alpha_FILLDATA."WellCode" = alpha_MTRDATA."Well_Code")
     INNER JOIN "gas"."dbo"."alpha_OPFILE" alpha_OPFILE ON
        alpha_MTRDATA."Op_Code" = alpha_OPFILE."OpCode"} $P!{OpParm.Value}
ORDER BY
    alpha_FILLDATA."FillDate" DESC

Please notice the $P!{OpParm.Value}.  The Default Value Expression for OpParm.Value is set to:

($P{OpParm}.isEmpty()==true ? "" : " where alpha_OPFILE.OpCode='" + $P{OpParm} + "'")

Sooo...If OpParm is not empty the query should dynamically end up looking like this:

SELECT
    alpha_FILLDATA."FillDate", alpha_FILLDATA."FillAmount", alpha_FILLDATA."InvoiceNo",
    alpha_MTRDATA."Well_Name",
    alpha_OPFILE."OpName"
FROM
    { oj ("gas"."dbo"."alpha_FILLDATA" alpha_FILLDATA INNER JOIN
"gas"."dbo"."alpha_MTRDATA" alpha_MTRDATA ON
        alpha_FILLDATA."OpCode" = alpha_MTRDATA."Op_Code" AND
    alpha_FILLDATA."WellCode" = alpha_MTRDATA."Well_Code")
     INNER JOIN "gas"."dbo"."alpha_OPFILE" alpha_OPFILE ON
        alpha_MTRDATA."Op_Code" = alpha_OPFILE."OpCode"} where alpha_OPFILE.OpCode='" + $P{OpParm} + "'"
ORDER BY
    alpha_FILLDATA."FillDate" DESC

This works GREAT when running it within IReport but when I copy the report to JasperServer, I get an error:

Error executing SQL statement.

My question is why does this not work in JasperServer?  Shouldn't the Parameter expression be evaluated before execution?

[ernestoo]

Can you set JRJdbcQueryExecuter to DEBUG and see what query it's executing?