report query: parameter inside quotation marks

[wisoo123]

In my report query on a postgre DB I use the crosstab function:
select * from crosstab('select x,y from table where id=1') as a,b;

This works fine as long as the ID is a constant in the query. If I replace the "1" by $P{PAR_ID}, I get an error:
"... Error preparing statement for executing the report query ...". The parameter PAR_ID is of type Integer, so this should be OK.

Does anybody know how to use parameters inside quoted sql parts?

Thanks!
Wisoo123

[ftama7]

I haven't really worked with Postgre, but you should do it like this: 

select * from crosstab('select x,y from table where id=' || $P{PAR_ID}) as a,b;

This is because the crosstab function takes a string as a parameter, and if you put the $P{} notation inside single quotes, it won't evaluate the parameter as a prepared statement, but rather it'll take it literally. That's why the stuff in the $P{} notiation should be concatenated to the first part of the query inside the single quotes.

[ftama7]

*comment moved to answer*