Q: Is it possible to use the value of a parameter (of class java.lang.String) as a field-name inside a function in a WHERE clause (that is, for the Dataset and Query Dialog, in the Query/text area)?
Why needed:
I'm trying to build a number of trivial master reports that each call a common complex subreport. These master reports each pass a different parameter to the common sub-report with the name of a certain boolean field to test in the WHERE clause.
In other words in my underlying table I have several boolean fields, let's call them Field_B1, Field_B2, Field_B3.
Master report #1 needs to test if Field_B1 is true and only print those records.
Master report #2 needs to test if Field_B2 is true, and only print those records.
Master report #3 needs to test if Field_B3 is true, and only print those records, etc.
Otherwise the master reports are the same (and so use a common sub-report).
Example:
Let's say the sub-report has a parameter named MyField, with it's default value set to the following string: `Table1`.`Field_B1`.
How do you write something like this: WHERE ifnull($P!{MyField}, FALSE ) = TRUE for it's WHERE clause? (If the field is null, assume its FALSE; then test if it's TRUE).
What I've tried:
So far I've tried $P and $P! (and even $!P). Also tried removing the back ticks. Tried just replacing the whole WHERE ... line with a parameter.
Also I've tried setting MyField to both string and boolean. I guess I don't understand just how and where $P{} and $!P{} parameters can get put into a SQL statement.
And I seem to be stumbling over the needed back quotes.
Thanks.
1 Answer:
Set the paramater MyField as follows:
Class Type: java.lang.String
Default Value Expression: "`Table1`.`Field_B1`" <-- Be sure to include the outer double quotes!
(To mean it's the value of a string, not what creates a string)
Then write the WHERE as follows:
WHERE ifnull($P!{FieldName}, FALSE ) = TRUE
Note the use of $P!{..} not $P{..}.
TIP: It seems you can't insert a parameter between single back quotes, i.e. not this `$P..`, rather you must include the back ticks inside the parameter's value, i.e. "`table_name`.`field_name`".
