Using the value of a parameter as a field name?

0

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.

Create's picture
99
Joined: May 23 2018 - 10:12pm
Last seen: 8 months 4 weeks ago

1 Answer:

0

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`".

Create's picture
99
Joined: May 23 2018 - 10:12pm
Last seen: 8 months 4 weeks ago
Feedback