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.
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAqgXNfDicv9iiN3d-qOslialJig9jUybFDpdTAc5GVy9rlM9dgNZJceQykBo19laoqTbP6c84Zb7cGXnKcRnWfW2zF83g9x8Hx-s2i1wUMocclSIAxJURg3pgjD8207RPY4ebAwZYGwQ/s1600/4_NOTEQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwR4clZjRZuQbU5EkJuy75-slvCrthOq3dJVPT_ZP52-mzv3KQODoWPmcnZaxOvYCTnP9AtgBwpx54mrzuhxrvEFADZQt7MZnnxKEJH9HyQOUVDSvVBmUg0teeXTD-g2CrxQr8ftVFMkQ/s1600/5_NOTEQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjt2s7pFxJ3ZQ1jiXjXV__4YALguIfpjMmPNZIC1TA2PlnP4PJLL1zkwmHVND8vO1UlN6IFY_t8_uhBnqnaVsGaN0foKGmDyGT_gHXtjDf8mKLF7ZmMNQZrl1hmNK2qBQtbZfKfHzyJCSg/s640/6_BETWEEN_1_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIxlhrGf_fBLsXA65_khQoFc1GNKlv2CX-MjhZjQa9pMK1GOvWmDrG59Zguwt1luNbv_eqIHgXZza0PGUUH8Ff7o7X4HgB8Sp4ZX3bVzLaXWiSVS_xaR6nGUkbnJ4dO2AFUOwZZ1eU0eI/s320/9_end1_param.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4g04U0YpE5iUoeIPrqfA1UDAOo75bo33Tv-TFL2M1tBWRxuyx5_D5uXU9nGTNMZaUELIGqBP9syZsErItjhUHsqH9NP-Ff1x2ET59oWBrLQvDpNSvuW5RAl5DmtYMxeNzO0-Db0OWS8w/s1600/7_BETWEEN_1_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhW43AJpZOsrVtkiWf8SGasFJh9THjc5dPQ2rX8AyxV-9zKhligWhQMIzfzmWKsofhHaMxwL41Ic9qM5gmAVEAYugVCavsfPOOf_UCC2HqiN-ZwlTq363jaIBvO8Ifj6MQ7rguBo0_qZnQ/s640/10_BETWEEN_2_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1YEhqLPJjes4EUDF59NpwdgqmgbZa4F8vtYeBw0BiQUcKC36ZULQKaloGZHoWmTmdQocxJUakf6b2EWRpO-lgMkNRH-KnqbscsxqhQsaa1P6CnvKDvzFMKCszOEnE7ut8HpCf4MIOBUU/s640/11_BETWEEN_2_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf0L46YwSjdvTApz9bmQ8trspBjNASVFv5reMdZZFggamGqiV-6VPU-qcikUNcAZ2Y4Mj8pl1zcYqrLtxEH5gvOc974hbPyTp9q_Ptco_M5no65fL8UtwbgX30wMUw5wS-0YqlbV8TfK8/s640/12_BETWEEN_3_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL_xsaBqTRsmqu4BdLcU5Lx6MAbX3LOIBov-VlxBnTM3ZE0uXFz_JiTSCAfUNB3JsHaGBcxaDHxq_7GadJO_OApBYB7QrJThJW-V2wn3HTp5JYCs4wPk7X_ScpvlaCGBE7PJwaCmge074/s400/13_BETWEEN_3_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsbi5fSIYJ0rb6mfIVI0YiQRMYjvcsEjqC_-CVm5lNRdUAW0tQhz1Qu5dfp-hu1Ifjy5ryY64rqn4A3l0WIxUZSpvKpL8P-uZF6ycugH2OlmxiV5hxJ9yLq6y0Ukf0I0XwAfBvT7rizxE/s1600/14_BETWEEN_4_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzWKDcJiegrJzfT598GYG1ehek2Wme3ivrNuKlVIq_SGk0rRa-CZt1uz3-49gSJSiK_nmwlrmrlqkCFlXfnRtkI62Q_aURJ6mLq158lvIMfn3Ab2p-yWEfOxus_rGe7rlepIM_2OCLb24/s400/15_BETWEEN_4_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMUWF-jWJ2YNIe2pziBshHOp4rdnJUzsFKfSbwj1Hj2nHgFkTyFBvvs9RcKH_r823_mg-FYFQNngO9_wVJh5fP7xECG4FwS9FfGiddWLEkvWjmHajDW19bTCZZ60wCTaOrgZ8_vPhPrxI/s1600/16_GREATER_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMypo0Mjbt3io1dpAh79jZUHLz0EjUlXTspZFlAWWLFLoTH1PBWXopHslb7nGheKtREDOQi6cCrWFspTrm6W2rVhXNALfiiXfFBMH7qxrBp8ZCKRb4myvKyTtpnnE_4HeXX3CToF3-n4U/s1600/17_GREATER_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPMboab2vYvRoV51Djuaj6N1JMZvT_XJnYJdlz3ciioDhInkuOVsEFiySrgxji7R2gLs3azPz-_thtMGNSJBPfRFMpVJHuvrfQ9M7Nk_CHVD1W5Vc2OkbYSz5eDzUVzjmTBKOlGQr1iCA/s640/18_GREATER_EQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_-r64aqXNLYrvr8W7fit6TxSvwdtlE65S-rZClZTciaVBLgbRogrLgX-rdqkzZlhZExhZMifRKAaNPLiBl8syUXf2AMYJFrNFkxYTf95LrcgEzY6je48Gk870yVsyCPVGzstyVWrIzGk/s1600/19_GREATER_EQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMtBB1t4j2FTX1gZN5yH8RhBrU_M7w8bh0qIa45XxgC52SeEbo-vG2siiVSomVOsl_8VJeB1BXwUE9NlEPd1_4nHTKQcBKH-7ZVDKxnjpU1E0nPHSmnckg7f55H5GlYPI1i5sUWgyCGhg/s640/20_LESS_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinjvLm0lT2DieQ8_EhKv1vT8MUstCuqaXEpsy1G539K-rdpVpcYGnQvbFt6iAKjimQ-apyROzS6QufNA_cjuOQzgg0ECcwWl85BZEIfY2a9N31YoDOPxgoDjWfBXLT9YaTWI0QfD-_WPk/s1600/21_LESS_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvBL6NGX6Uv8L-GRLRnv98ILLsNLYVAKByFONicPAxGJGyfenlbf7lCcusPDTx6H0KwjUstXhxwGkxJcoh2hl_03tAOWz3UNhUF47ZuZkCfNTgsqaPGtqTPnrjkPJgLAkeF6BkIJlyEiY/s640/22_LESS_EQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw1tSChWSIn2Z5CfyV9ll_4XmJ-69TVYKnc4ux_zc0YnOZ8C6cLSsl0LK4Ag3qdDs1CvKJPhbYGSTOdc8_wi0LBFgmWLRAR_fR2XjOkNLdWaS4nP1zRMRdhcpIUPge2Saq96L2w8X6YrE/s640/23_LESS_EQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyx5z2hX2vBjZhDIXs2JAyl0sp_HdQVwz5FYUEk6Ex8j2pojS3HRwODyqfaNDqTvPjN0iToh2w1HM1R9NXTVe2in6Qy-Mjfm0CC3XdqknVbZtQDxiR_wP9vucvDf24HrgCj8nG8bJCPsE/s640/24_IN_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTDnG754LH9HfMmoRdzp5Lm5mMZc2WXtLJNIPVnW-fz2ZZvLNX18or3ig5w-gjobuP_X17i59jhKU_s3wRtu73E9UIAS1_RJMvfRwJ6OZzLDU_E1H0j79kL04szCcO4r3zdco5d7tdqEs/s640/25_IN_param_definition.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhRbjY9ugSbSmSkbDE7fIZuVtLnCx4OeLpYp9xrHf2HLMmNphEQghHKmmW8YFfO30Z1UxLAqaP7m2q1biAzdfTI_8puoyWd7Ytv6vyniZe1O12TeLQrnIa4oFRgRs6gpfTVQxBJqJsmE/s640/25_IN_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv2_DT7W30D-6Ad-_lmwWYZ8Vci1Bs54YcH2L_Z3DnsL464hc9eKwVQOPbGh6xJAwsJtDWsVLKkZEJAXxKyGy10lDuuNjXCXzOMCiXU5gyjSMD1SHPKXmlt9_z1CdxpPRHiNIjBgDNM-U/s640/27_NOTIN_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht-fUS-hGsxaIOScQUo6ZUU7NDuwwBcrJaRZbhtaXYav8lu6ORx2LEVM7QYN0Z-YeETui9Xk7IIJDGipc0HqbVFWE1KzO0FXJ6MG_k16eACT6BRgq-P4ELuVxTE5AN8ZDn2agXkqp__7I/s640/28_NOTIN_param_output.jpg)
That's it.
Hope you enjoyed this post.
For more queries please contact me on : miwagada@gmail.com
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 |
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAqgXNfDicv9iiN3d-qOslialJig9jUybFDpdTAc5GVy9rlM9dgNZJceQykBo19laoqTbP6c84Zb7cGXnKcRnWfW2zF83g9x8Hx-s2i1wUMocclSIAxJURg3pgjD8207RPY4ebAwZYGwQ/s1600/4_NOTEQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwR4clZjRZuQbU5EkJuy75-slvCrthOq3dJVPT_ZP52-mzv3KQODoWPmcnZaxOvYCTnP9AtgBwpx54mrzuhxrvEFADZQt7MZnnxKEJH9HyQOUVDSvVBmUg0teeXTD-g2CrxQr8ftVFMkQ/s1600/5_NOTEQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjt2s7pFxJ3ZQ1jiXjXV__4YALguIfpjMmPNZIC1TA2PlnP4PJLL1zkwmHVND8vO1UlN6IFY_t8_uhBnqnaVsGaN0foKGmDyGT_gHXtjDf8mKLF7ZmMNQZrl1hmNK2qBQtbZfKfHzyJCSg/s640/6_BETWEEN_1_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4XFO5rJejKenaXHaAuZJ9XWRDdVU10zKT5IBRFA0DPnpvMlDpQepn3qgVtfFL68YuGcInfm8aUaCSD76JJD4OUB4RHT0p-vfIRNYDcPXkjJRWSmsK9yt_djup1hwAu8Mojt4T9bxVla8/s320/8_start1_param.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIxlhrGf_fBLsXA65_khQoFc1GNKlv2CX-MjhZjQa9pMK1GOvWmDrG59Zguwt1luNbv_eqIHgXZza0PGUUH8Ff7o7X4HgB8Sp4ZX3bVzLaXWiSVS_xaR6nGUkbnJ4dO2AFUOwZZ1eU0eI/s320/9_end1_param.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4g04U0YpE5iUoeIPrqfA1UDAOo75bo33Tv-TFL2M1tBWRxuyx5_D5uXU9nGTNMZaUELIGqBP9syZsErItjhUHsqH9NP-Ff1x2ET59oWBrLQvDpNSvuW5RAl5DmtYMxeNzO0-Db0OWS8w/s1600/7_BETWEEN_1_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhW43AJpZOsrVtkiWf8SGasFJh9THjc5dPQ2rX8AyxV-9zKhligWhQMIzfzmWKsofhHaMxwL41Ic9qM5gmAVEAYugVCavsfPOOf_UCC2HqiN-ZwlTq363jaIBvO8Ifj6MQ7rguBo0_qZnQ/s640/10_BETWEEN_2_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1YEhqLPJjes4EUDF59NpwdgqmgbZa4F8vtYeBw0BiQUcKC36ZULQKaloGZHoWmTmdQocxJUakf6b2EWRpO-lgMkNRH-KnqbscsxqhQsaa1P6CnvKDvzFMKCszOEnE7ut8HpCf4MIOBUU/s640/11_BETWEEN_2_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf0L46YwSjdvTApz9bmQ8trspBjNASVFv5reMdZZFggamGqiV-6VPU-qcikUNcAZ2Y4Mj8pl1zcYqrLtxEH5gvOc974hbPyTp9q_Ptco_M5no65fL8UtwbgX30wMUw5wS-0YqlbV8TfK8/s640/12_BETWEEN_3_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL_xsaBqTRsmqu4BdLcU5Lx6MAbX3LOIBov-VlxBnTM3ZE0uXFz_JiTSCAfUNB3JsHaGBcxaDHxq_7GadJO_OApBYB7QrJThJW-V2wn3HTp5JYCs4wPk7X_ScpvlaCGBE7PJwaCmge074/s400/13_BETWEEN_3_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsbi5fSIYJ0rb6mfIVI0YiQRMYjvcsEjqC_-CVm5lNRdUAW0tQhz1Qu5dfp-hu1Ifjy5ryY64rqn4A3l0WIxUZSpvKpL8P-uZF6ycugH2OlmxiV5hxJ9yLq6y0Ukf0I0XwAfBvT7rizxE/s1600/14_BETWEEN_4_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzWKDcJiegrJzfT598GYG1ehek2Wme3ivrNuKlVIq_SGk0rRa-CZt1uz3-49gSJSiK_nmwlrmrlqkCFlXfnRtkI62Q_aURJ6mLq158lvIMfn3Ab2p-yWEfOxus_rGe7rlepIM_2OCLb24/s400/15_BETWEEN_4_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMUWF-jWJ2YNIe2pziBshHOp4rdnJUzsFKfSbwj1Hj2nHgFkTyFBvvs9RcKH_r823_mg-FYFQNngO9_wVJh5fP7xECG4FwS9FfGiddWLEkvWjmHajDW19bTCZZ60wCTaOrgZ8_vPhPrxI/s1600/16_GREATER_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMypo0Mjbt3io1dpAh79jZUHLz0EjUlXTspZFlAWWLFLoTH1PBWXopHslb7nGheKtREDOQi6cCrWFspTrm6W2rVhXNALfiiXfFBMH7qxrBp8ZCKRb4myvKyTtpnnE_4HeXX3CToF3-n4U/s1600/17_GREATER_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPMboab2vYvRoV51Djuaj6N1JMZvT_XJnYJdlz3ciioDhInkuOVsEFiySrgxji7R2gLs3azPz-_thtMGNSJBPfRFMpVJHuvrfQ9M7Nk_CHVD1W5Vc2OkbYSz5eDzUVzjmTBKOlGQr1iCA/s640/18_GREATER_EQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_-r64aqXNLYrvr8W7fit6TxSvwdtlE65S-rZClZTciaVBLgbRogrLgX-rdqkzZlhZExhZMifRKAaNPLiBl8syUXf2AMYJFrNFkxYTf95LrcgEzY6je48Gk870yVsyCPVGzstyVWrIzGk/s1600/19_GREATER_EQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMtBB1t4j2FTX1gZN5yH8RhBrU_M7w8bh0qIa45XxgC52SeEbo-vG2siiVSomVOsl_8VJeB1BXwUE9NlEPd1_4nHTKQcBKH-7ZVDKxnjpU1E0nPHSmnckg7f55H5GlYPI1i5sUWgyCGhg/s640/20_LESS_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinjvLm0lT2DieQ8_EhKv1vT8MUstCuqaXEpsy1G539K-rdpVpcYGnQvbFt6iAKjimQ-apyROzS6QufNA_cjuOQzgg0ECcwWl85BZEIfY2a9N31YoDOPxgoDjWfBXLT9YaTWI0QfD-_WPk/s1600/21_LESS_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvBL6NGX6Uv8L-GRLRnv98ILLsNLYVAKByFONicPAxGJGyfenlbf7lCcusPDTx6H0KwjUstXhxwGkxJcoh2hl_03tAOWz3UNhUF47ZuZkCfNTgsqaPGtqTPnrjkPJgLAkeF6BkIJlyEiY/s640/22_LESS_EQUAL_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw1tSChWSIn2Z5CfyV9ll_4XmJ-69TVYKnc4ux_zc0YnOZ8C6cLSsl0LK4Ag3qdDs1CvKJPhbYGSTOdc8_wi0LBFgmWLRAR_fR2XjOkNLdWaS4nP1zRMRdhcpIUPge2Saq96L2w8X6YrE/s640/23_LESS_EQUAL_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyx5z2hX2vBjZhDIXs2JAyl0sp_HdQVwz5FYUEk6Ex8j2pojS3HRwODyqfaNDqTvPjN0iToh2w1HM1R9NXTVe2in6Qy-Mjfm0CC3XdqknVbZtQDxiR_wP9vucvDf24HrgCj8nG8bJCPsE/s640/24_IN_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTDnG754LH9HfMmoRdzp5Lm5mMZc2WXtLJNIPVnW-fz2ZZvLNX18or3ig5w-gjobuP_X17i59jhKU_s3wRtu73E9UIAS1_RJMvfRwJ6OZzLDU_E1H0j79kL04szCcO4r3zdco5d7tdqEs/s640/25_IN_param_definition.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhRbjY9ugSbSmSkbDE7fIZuVtLnCx4OeLpYp9xrHf2HLMmNphEQghHKmmW8YFfO30Z1UxLAqaP7m2q1biAzdfTI_8puoyWd7Ytv6vyniZe1O12TeLQrnIa4oFRgRs6gpfTVQxBJqJsmE/s640/25_IN_param_output.jpg)
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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv2_DT7W30D-6Ad-_lmwWYZ8Vci1Bs54YcH2L_Z3DnsL464hc9eKwVQOPbGh6xJAwsJtDWsVLKkZEJAXxKyGy10lDuuNjXCXzOMCiXU5gyjSMD1SHPKXmlt9_z1CdxpPRHiNIjBgDNM-U/s640/27_NOTIN_param_query.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht-fUS-hGsxaIOScQUo6ZUU7NDuwwBcrJaRZbhtaXYav8lu6ORx2LEVM7QYN0Z-YeETui9Xk7IIJDGipc0HqbVFWE1KzO0FXJ6MG_k16eACT6BRgq-P4ELuVxTE5AN8ZDn2agXkqp__7I/s640/28_NOTIN_param_output.jpg)
That's it.
Hope you enjoyed this post.
For more queries please contact me on : miwagada@gmail.com
can we use $P!{..} instead of $X{..} ?
ReplyDeleteYes.. If it seems that the data you require in Parameter should come from Jasper report itself, you can use $P!
DeleteHello. Thanks. How can i use in a report a filter only when the parameter/filter has a value??, in other words, if in the report i selected a value-> filter for that value ; if not for all values.
ReplyDeleteThanks