Create Posted June 20, 2018 Share Posted June 20, 2018 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. Link to comment Share on other sites More sharing options...
Solution Create Posted June 20, 2018 Author Solution Share Posted June 20, 2018 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`". Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now