when amount is 0 how to set cell blank.

[honeyani]

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

 

[shrije]

using an expression set zero to null value. and use "blank when null" option in text box.

[honeyani]

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?

 

[honeyani]

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..

 

[shrije]

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.

 

 

[honeyani]

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...

[honeyani]

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?

[shrije]

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.

[shrije]

Replace $F{BUDGETADJ}.length() by $F{BUDGETADJ}.trim.length() nin yr expr

[honeyani]

i tried after trim() still i get $0.00

Can you send across the exact expression you tried with for double.

[shrije]

The resilting value of your expression would be as shown in the attachement

[shrije]

did it work finally

[honeyani]

hi,

Thanks for your reply and guidance..

I fixed it  now,it works...

Thanks,

Ani

[shrije]

Waht does the expression look like now ? If that is the change that fixed it. Waht way did it fix otherwise ?

[honeyani]

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...:)