IReport, Jasperserver: different result for the same report

Question

My report based in this request:

select "Annee" ,"Diam", sum("Consommation") from "Consom_N","CptDim"
where "Annee" >= $P{a1} and "Annee" <= $P{a2} 
and "Consom_N"."NumCpt"="CptDim"."NumCpt"and "District"= $P{dst}
and $X{IN, cast("Diam" as character varying), DiamRQn}
group by "Annee","Diam"
order by "Annee"
;

"Diam" field is a smallint, the problem is in this part

$X{IN, cast("Diam" as character varying), DiamRQn}

DiamRQn is the parameter used by the control input wich is a multiple selection from request, when remove casting it says:

PSQLException: ERROR: operator does not exist : smallint = character varying

with casting it work fine in ireport preview , but on the jasperserver, the chart shows all the values of "Diam", the selected and the not selected. As i understood,for jasperserver, casted "diam" is always exist in "DiamRQn" collection, although everything went ok for ireport !

Additional inf: DBMS: postgresql ireport ver: 5.0.1

its java.util.Collection i tried the class List and same result, also i tired this java.util.Collection<Short> and no way — 54l3d
Make sure the parameter name is identical to the name of the input control. It would probably throw an error if it was not, but it's worth checking. — Tom
Another thing you could do for testing is to put a text field on your report and set the expression to: $P{DiamRQn}.toString(). That way you could see what the input control is passing to the report. — Tom

Tom Tom · Accepted Answer · 2013-06-10T14:29:47

I have had some problems before performing functions with the $IN{} statement in Jasper. My solution is usually to perform the function first, the doing the $IN{} statement after. I usually do whatever function I need to do within a sub-select, and then do the $IN{} statement outside of it. So you could try this:

SELECT *
FROM
(
SELECT "Annee" ,"Diam", sum("Consommation"),
       cast("Diam" as character varying) AS Diam_char
FROM   "Consom_N","CptDim"
WHERE  "Annee" >= $P{a1} and "Annee" <= $P{a2} 
       and "Consom_N"."NumCpt"="CptDim"."NumCpt" and "District"= $P{dst}
GROUP BY "Annee","Diam"
) x
WHERE  $X{IN, Diam_char, DiamRQn}
ORDER BY "Annee"

I would probably also move part of your where clause into a join between the Consom_N and CptDim tables, but that's optional.

SELECT *
FROM
(
SELECT "Annee" ,"Diam", sum("Consommation"),
        cast("Diam" as character varying) AS Diam_char 
FROM   "Consom_N"
       JOIN "CptDim" ON "Consom_N"."NumCpt"="CptDim"."NumCpt" 
WHERE  "Annee" >= $P{a1} and "Annee" <= $P{a2} 
       and "District"= $P{dst}
GROUP BY "Annee","Diam"
) x
WHERE  $X{IN, Diam_char, DiamRQn}
ORDER BY "Annee"

IReport, Jasperserver: different result for the same report

2 Answers