$X{....} Parameter in Jasper report

Welcome Guys..!!!

Today v'll learn Third type of Parameter $X{..} which is most useful while filtering a query.

$X{...} Parameter acts as "conditions" applied to SQL query in where clause separated by Logical Operators (and / or ).

3 Basic steps to use $X{} Parameter in a report.
Step 1 : Change in Parameter "Default Value Expression".
Step 2 : Change the Report's "Dataset and Query Editor" Property with below keywords with correct syntax.
Step 3 : Preview and check the report output which works exactly as required.

Below are the various keywords used in $X{} Parameter with different syntax and explained briefly their meaning with executable report screenshots.

1) EQUAL
2) NOTEQUAL
3) BETWEEN
4) [BETWEEN]
5) [BETWEEN
6) BETWEEN]
7) GREATER
8) [GREATER
9) LESS
10) LESS]
11) IN
12) NOTIN

1) EQUAL :
Keyword "EQUAL" works exactly as "=" (equals) operator in SQL
ex : select * from address where ID = 36
OR
      "select * from address where $X{EQUAL, ID , equal_parameter}"

Here in $X{},
EQUAL : Keyword refers to equality check functionality.
ID : FieldName i.e Columns retrieved in select statement.
equal_parameter : User-defined Parameter with defined / prompted value.(default value given : 36)

Below screenshots explains everything about use of EQUAL in where clause.

Pic 1 : Query with $X{EQUAL,....}

Pic 2: Pre-defined value instead of prompting to user

Pic 3 : Output which satisfies conditions ID = 36

2) NOTEQUAL :
Keyword "NOTEQUAL" works exactly as "!=" (equals' complement) operator in SQL
ex : select * from address where ID != 36
OR
             "select * from address where $X{NOTEQUAL, ID , equal_parameter}"

Here in $X{},
NOTEQUAL : Keyword refers to equality checking functionality with negation.
ID : Fieldname i.e Columns retrieved in select statement.
equal_parameter : User-defined Parameter with defined / prompted value.

Below screenshots explains everything about use of NOTEQUAL in where clause.





3) BETWEEN :
Keyword "BETWEEN" works something different as "between" clause operator in SQL.
ex : select * from address where ID>10 and ID<15
OR
      "select * from address where $X{BETWEEN, ID , start1, end1}" 

Here in $X{},
BETWEEN: Keyword refers to range check functionality.
ResultSet excludes values of start and end parameter value and returns in-between values of inputs given.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.
end1 : ending range value.

Below screenshots explains everything about use of "BETWEEN" in where clause.


 


4) [BETWEEN] :
Keyword "BETWEEN" works exactly as "between" clause operator in SQL
ex : select * from address where ID between 10 and 15
OR
      "select * from address where $X{[BETWEEN], ID , start1, end1}" 

Here in $X{},
'['BETWEEN']': Keyword refers to range check functionality.
ResultSet includes values of start and end parameter with in-between values of inputs given.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.
end1 : ending range value.

Below screenshots explains everything about use of "[BETWEEN]" in where clause.




5) [BETWEEN :
Keyword "BETWEEN" works somewhat different as in "between" clause operator in SQL
ex : select * from address where ID>=10 and ID<15
OR
      "select * from address where $X{[BETWEEN, ID , start1, end1}" 

Here in $X{},
'['BETWEEN: Keyword refers to range check functionality.
ResultSet includes values of start parameter value and with in-between values of inputs given.
It excludes value of end parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.
end1 : ending range value.

Below screenshots explains everything about use of "[BETWEEN" in where clause.




6) BETWEEN]:
Keyword "BETWEEN" works somewhat different as in "between" clause operator in SQL
ex : select * from address where ID>10 and ID<=15
OR
      "select * from address where $X{BETWEEN], ID , start1, end1}" 

Here in $X{},
BETWEEN']': Keyword refers to range check functionality.
ResultSet includes values of end parameter value and with in-between values of inputs given.
It excludes value of start parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.
end1 : ending range value.

Below screenshots explains everything about use of "BETWEEN]" in where clause.




7) GREATER:
Keyword "GREATER" works exactly as ">" relational operator in SQL query.
ex : select * from address where ID>10
OR
      "select * from address where $X{GREATER, ID , start1}" 

Here in $X{},
GREATER: Keyword refers matched column output which is "greater than" parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.

Below screenshots explains everything about use of "GREATER" in where clause.




8) [GREATER:
Keyword "GREATER" works exactly as ">=" relational operator in SQL query.
ex : select * from address where ID>=10
OR
      "select * from address where $X{[GREATER, ID , start1}" 

Here in $X{},
'['GREATER: Keyword refers matched column output which is "greater than equals" parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.

Below screenshots explains everything about use of "[GREATER" in where clause.




9) LESS:
Keyword "LESS" works exactly as "<" relational operator in SQL query.
ex : select * from address where ID<10
OR
      "select * from address where $X{LESS, ID , start1}" 

Here in $X{},
LESS: Keyword refers matched column output which is "less than" parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.

Below screenshots explains everything about use of "LESS" in where clause.




10) LESS]:
Keyword "LESS" works exactly as "<=" relational operator in SQL query.
ex : select * from address where ID<=10
OR
      "select * from address where $X{LESS], ID , start1}" 

Here in $X{},
LESS: Keyword refers matched column output which is "less than equals" parameter value.
ID : Fieldname i.e Columns retrieved in select statement.
start1 : starting range value.

Below screenshots explains everything about use of "LESS]" in where clause.




11) IN:
Keyword "IN" works exactly as "IN clause in SQL query.
ex : select * from address where ID  not in (2,10,20)
OR
      "select * from address where $X{IN, ID , extended_query}" 

Here in $X{},
IN: Keyword refers values falls under list of values.
ID : Fieldname i.e Columns retrieved in select statement.
extended_query : list or collection type contains values to be matched in "IN" clause.

Expressions for parameters :
extended_query : new java.util.ArrayList()
arraylist10: $P{extended_query}.add(10)
arraylist2: $P{extended_query}.add(2)
arraylist20: $P{extended_query}.add(20)

Below screenshots explains everything about use of "IN" in where clause.







12) NOTIN:
Keyword "NOTIN" works exactly as "NOT IN clause in SQL query.
ex : select * from address where ID  not in (2,10)
OR
      "select * from address where $X{NOTIN, ID , extended_query}" 

Here in $X{},
NOTIN: Keyword refers values falls under list of values.
ID : Fieldname i.e Columns retrieved in select statement.
extended_query : list or collection type contains values to be matched in "NOTIN" clause.

Below screenshots explains everything about use of "NOTIN" in where clause.





That's it.

Hope you enjoyed this post.

For more queries please contact me on : miwagada@gmail.com