Using boolean / checkbox inputs in the report query.

[kburns]

I'd like to use some IF ELSE logic in a report.

Here is the situation

I have two inputs:

• The first is a list of checkboxes for a multi select (LABEL = "Pick some items"). This is a java.util.List. The parameter name is itemList.
• The second is a single checkbox that is intended to run the query on all of the previous choices (LABEL = "Use all items"). This is a java.lang.Boolean. The  parameter name is useAll.

The logic of the query is

   If (useAll is TRUE)  
      (SELECT * FROM XYZ...) 
   ELSE 
      (SELECT * FROM Something else) 

In SQL I know I can do this

   IF (1 = 1)       BEGIN          SELECT 'wow its true' as result       END   ELSE       BEGIN          SELECT 'wow its false' as result       END

 

My question

Can I use the IF...ELSE in iReport? And if so what is the syntax?

So far these have not worked:

• IF $P(useAll} ...
• IF $P(useAll}=1 ...
• IF $P(useAll}=true ...
• IF !$P(useAll} ...

 

[despec]

Here is the format for the "If" statement in Ireport:

 

( condition expression ? true expression : false expression )

 

David

[kburns]

So how would I use my boolean parameter $P(useAll} in the query?

• ($P(useAll} ? this : that)
• ($P(useAll}=1 ? this : that)
• ($P(useAll}=true ? this : that)

??

 

despec
Wrote:

Here is the format for the "If" statement in Ireport: ( condition expression ? true expression : false expression ) David

 

[mdahlman]

I think your premise in "In SQL I know I can do this" isn't quite right. It's correct that you can create a procedure to do something like that. But that's not a single SQL query anymore, and you need a single SQL query in your report.

You need to look at the problem more like the following. Create the parameter useAll as a Boolean. Create the parameter mySQLQuery as a String. Give it a default value like this:

$P{useAll} ? "SELECT * FROM XYZ" : "SELECT * FROM ANOTHER_TABLE"

Then your SQL query in the report is:

$P!{mySQLQuery}

There are lots of variations possible. Maybe it's better to make "useAll" a String to handle more than 2 cases. Maybe you most of your SQL query to actually be in the SQL query field. Then "mySQLQuery" could contain just a small snippet of SQL. But that idea should get you going.

Regards,
Matt