How to check null in a Multi-Select/ Collection Parameter.
Posted on August 25, 2016 at 1:05am
Hi folks,
I have a Multi-Select input control(i.e. param_prj_id) on the Jasper Server which is having multiple Project ID's via Query. I created a collection parameter with the same code(i.e. param_prj_id) in the Jasper Report.
Everything is working fine when the values are passed. But I am unable to check for null when no value is passed in the Multi-Select (Since the $X{IN} converts the query to 1=1 if no values are passed).
I want to explicitly check for null in this Multi- Select input control. (i.e. if no id's are selected - display a message).
It would be great if someone helps me with this.
Joined: Jul 20 2016 - 3:20am
Last seen: 6 years 4 months ago
Posted on August 25, 2016 at 5:53am
Hi KKriplani,
The above default expression evaluated to be "null".
I was however able to achieve the desired result in the sql query by writing the below code to identify null in multi-select parameter:
--The Where condition will check if the Multi-Select Parameter isNull
Select 1 Data from Dual
where $X{IN, 0, param_prj_id}
UNION ALL
--The Where condition will check if the Multi-Select Parameter isNotNull
Select 2 Data from Dual
where $X{NOTIN, 0, param_prj_id}
If I get Null in Multi-Select I simply set the data variable to 1(lets say - test) and use this 'test' variable on a band as display expression. when $F{test} == 1.
The actual query is much more complicated, but this exactly what i needed :)
Thanks for all your help :)
Joined: Jul 20 2016 - 3:20am
Last seen: 6 years 4 months ago
Posted on August 25, 2016 at 2:12am
Hi,
This is the default functionality of multi-select, i.e, if no values are passed through the input control, it considers the scenario as all are values selected.
So if you want to display a message, create a parameter (Lets say $P{test} ) with an expression like below;
$P{param_prj_id} == null ? "1" :"0"
Using this parameter, display a band saying "No values selected" (as per your requirement) and use print when condition for that band using $P{test}
Regards,
KKriplani
Joined: Sep 4 2015 - 2:18am
Last seen: 1 year 5 months ago
Posted on August 25, 2016 at 5:01am
Hello KKriplani,
Thank you for the quick reponse.
I tried using $P{param_prj_id} == null ? "1" :"0", but the result is always 1 irrespective of the values i pass in the collection parameter.
Always resulting that the parameter is null.
Maybe it has something to do with the evaluation time, the parameters are evaluated before the report execution. I did try to find the evaluation setting in properties but Parameters doesn't have them.
Also if i take a plain text field & put the same expression in the text field it will work just fine.
JRXML Code :
<parameter name="param_prj_id" class="java.util.Collection" nestedType="java.lang.Integer"/>
<parameter name="is_null" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA[$P{param_prj_id} == null ? "1" :"0"]]></defaultValueExpression>
</parameter>
Joined: Jul 20 2016 - 3:20am
Last seen: 6 years 4 months ago
Posted on August 25, 2016 at 5:13am
Hi,
I apologize, I would like to correct myself.
Go with this default expression:
$P{param_prj_id}.isEmpty() ? "1" : "0"
Let me know if you get the desired results :)
Regards,
KKriplani
Joined: Sep 4 2015 - 2:18am
Last seen: 1 year 5 months ago
@KKriplani, your suggestion of using the below worked fine with a mention here - $P{Param_Prj_Id}.isEmpty()?1:0. The new param that I used was an integer and hence assigned 1 and 0 integer values. There was an evaluation error with the new param being string. Anyway, your suggestion saved my day! Cheers!
For me, I had to first set the default value of the parameter to
new ArrayList()
Then the isEmpty() and size() methods worked as expected. As you said, they were evaluating to null otherwise.