when amount is 0 how to set cell blank.
Posted on October 16, 2008 at 2:26am
I have generated excel report using Jasper (ireport tool).
On final generated excel sheet ,if amount is 0- blank cell should be displayed ,i'm getting $0.00
Pattern applied to the cell is $#,##0.00;[Red]($#,##0.00) ,blank when null option is checked. why this issue ,hope value is returned as 0 hence 0 is formatted as per the pattern and displayed as $0.00, but only blank space should be set to the cell .how to establish this?
Please guide to fix this issue.
waiting for your eply.
Thx,
Ani
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 16, 2008 at 2:38am
using an expression set zero to null value. and use "blank when null" option in text box.
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 17, 2008 at 12:27am
I have coded expression to set "0" or " " when value doesn't exists.Have also checked blank when null option.
Still i get $0.00 displayed ,for 0 value the pattern gets applied and displayed.
Anyother options to follow up?
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 17, 2008 at 12:33am
Thanks for your reply
Field type is double ..
I tried these ways ,also blank when null option is set to true
Double.valueOf($F{BUDGETADJ}.length()==0?" " :$F{BUDGETADJ}.trim())
Double.valueOf($F{BUDGETADJ}.length()==0?"0 " :$F{BUDGETADJ}.trim())
Double.valueOf($F{BUDGETADJ}.length()==0?null :$F{BUDGETADJ}.trim())
please guide to fix this..
waiting for your reply at the earliest..
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 17, 2008 at 1:16am
Two things you can try.
Just hard code the 'null' value and check if you are getting blank. This will tell u that the solution works(i know it works).
Second try ($F{BUDGETADJ}.length()==0?null :Double.valueOf($F{BUDGETADJ}.trim()))
Double.value must be intialising from null to zero again.
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 17, 2008 at 2:06am
I'm more confused
Cell type is set as double ,
Actual expression is as below :
Double.valueOf(($F{BUDGETADJ}.indexOf("-")!=-1?"-"+($F{BUDGETADJ}.substring(0,$F{BUDGETADJ}.indexOf("-"))).trim():($F{BUDGETADJ}.length()==0?null:$F{BUDGETADJ}.trim())))
value exists as 10000.00 - ,so this expression checks for - sign at the end and brings it to front ,second condition i check for empty values..can you suggest how to update this expression?
waitign for reply ASAP...
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 17, 2008 at 2:11am
Thanks for your reply.
My opinion is Default value for string is null - 'blank if null' option works if the field type is String.I tested the same .
But for our report cell type is set as double for some reason,default value for double will be 0.00 ,hence the pattern gets applied to it and displayed as $0.00 ,am i right in my view?
So I think now i should update my query as ,In iReport tool for Double cell type is it possible to set blank when value is 0.00 ?
Any idea?
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 17, 2008 at 2:28am
It works for Double as well. I can see blank value inspite of a pattern applied. I used this expression Double.valueOf((double)100000.0) and null. It worked for me. It showed pattern when value was present and blank when null.
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 17, 2008 at 2:40am
Replace $F{BUDGETADJ}.length() by $F{BUDGETADJ}.trim.length() nin yr expr
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 17, 2008 at 2:57am
i tried after trim() still i get $0.00
Can you send across the exact expression you tried with for double.
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 17, 2008 at 2:59am
The resilting value of your expression would be as shown in the attachement
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 17, 2008 at 4:35am
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 19, 2008 at 10:33pm
hi,
Thanks for your reply and guidance..
I fixed it now,it works...
Thanks,
Ani
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
Posted on October 19, 2008 at 10:45pm
Waht does the expression look like now ? If that is the change that fixed it. Waht way did it fix otherwise ?
Joined: Apr 5 2008 - 8:59am
Last seen: 9 years 6 months ago
Posted on October 19, 2008 at 11:17pm
Hi ,
My expression is the same...it returns 'null' when value is empty for that field in XML data source.
Option 'Blank when null' is set to true. As when the value it null it is set to blank as the option says..
My Excel sheet is now with blank cell when value from XML file is empty or 0 or 0.00.
Little i updated my expression to check for all these three values and set to null..
It works fine...
To Conclude :iReport tool supports setting blank to field value in Excel when value is null/0/0.00/<empty space>.
We have to play around with expressions...:)
Joined: Sep 10 2008 - 3:07am
Last seen: 15 years 2 weeks ago
