How to write conditional query in iReport using parameters

Inside iReport creating static query that is, writing a simple static query in the the Query executor and set it up running is quite simple and too monotonous.

The real challenge arrives in actually changing the query of the report dynamically based upon some condition, this could be achieved with a simple implementation of parameters.

Following are the steps to use the conditional SQL Statements.

1. Create a parameter if you want user triggered change of the SQL Query , if not user triggered then it must be based upon the field, so that can be used too.

2. Create a different parameter which will be responsible for triggering the changed query based upon the condition.

3. Now identify the condition which is responsible for changing the query,

4. The parameter which contains the conditional SQL switching should have the property "Used as prompt" turned off and the condition should be placed in the default value.

5. Then after setting this, move onto set the Report Query Expression. Now see the dynamism of the query could be provided to the whole query or a portion of query so accordingly place the expression .The parameter containing the conditional expression should be used as the expression and it should be denoted as $P!{name of the param containing expression}

Here is a little illustration

Suppose there is a parameter called $P{TestParamSQL}, the prompt should be turned off as we are setting the value at runtime checking the value of the $P{TEST_PARAM}. 

Set the default value of the parameter as:

For a single condition 

$P{TEST_PARAM}.equals("test")
? " select SQL query"
: " else SQL select query"

For a multiple condition 

$P{TEST_PARAM}.equals("test")
? " select SQL query"
: $P{TEST_PARAM}.equals("test1")
? " SQL select query"
: $P{TEST_PARAM}.equals("test2")
?" SQL Query"
: "else SQL Query"

Now in the iReport Query executor you should give the following expression$P!{TestParamSQL} 

Do take care that the dependent parameter $P{TEST_PARAM}. should be set before the $P{TestParamSQL} else would throw a null pointer exception.

Another thing which should be kept in mind is that the query given in the quotes in the expression should be syntactically correct, that is the SQL in as the string should be correct.

Here is a running sample-

Prerequesites-
1. JRXML File
2. PDF Export

Steps to set up this Sample Report.

1. Then you must choose Sample Database in the datasource

4. Then next you need to activate the the sample database from Help(in the iReport Menu bar)---> Samples---->Run Sample Database

5. Then Run the  Report

There is a PDF export also which would show exactly how the report looks(The input value is employeeId= 2).

Please put in you queries/suggestions, I will be more than happy to know.

Cheers .. Happy Coding!!