parameter containing whole query doesn't work

[cnesbit]

I used to be able to do this but came back to some of my reports recently and they no longer work.

For this example query: SELECT 'bob' AS StudentName

if I put that query in the "Report query" popup directly, it works. If I put any part of that query, or the whole query, it dies complaining about SQL syntax.

Only way for a parameter to work at the moment is if I use this query: SELECT $P{report_query} AS StudentName

 

What I need is for the parameter to contain the whole query (  $P{report_query}  = SELECT 'bob' AS StudentName) but when I do that I get this error:

Error:SQL problems:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "SELECT 'bob' AS StudentName" at line 1

It appears that ireport is trying to be "smart" and insert some extra quotes for me that I don't want. can I prevent this behaviour or work around it somehow?

HOW CAN I USE A PARAMETER FOR THE ENTIRE QUERY WITHIN IREPORT?

-cnesbit

 

EDIT:
I figured out the solution myself. It's not well documented, I happened across it by accident about halfway down this link The solution is to format your parameter with a ! character like so: $P!{my_query}   (rather than $P{my_query} ).

Post Edited by cnesbit at 01/12/2012 21:59