Number format a decimal in Birt reports

Question

I have a table which gives me values in Nos. and Decimal (weight-kg) E.g.10.455 Kg ( that is 10 Kg and 455 gms) and nos. as in 10 Nos.

Now all these values come from a column (Decimal (10,3)-mysql) in table and based on the unittype column I have to decide whether to have 3 decimal or zero decimal in the BIRT report.

I know that through scripting the values can be modified.. but I am unable to utilise the scripting.

I am writing this on onFetch

if(row["unittype"]=="Nos")
var df = new Packages.java.text.DecimalFormat("#,###.##");
else
var df = new Packages.java.text.DecimalFormat("#,###");

df.format(row["invoicedquantity"]);
this.setDisplayValue(df);

I am unable to get the values

Ursulina

what is visible when you use this script, after report render? – Mimosa

No change in the output – Ursulina

Answer 1

I don't believe this can work from the onFetch script, this kind of script should be put in "onRender" event of a data element. Furthermore, the formatter returns the result so it should be:

this.setDisplayValue(df.format(row["invoicedquantity"]));

But i think it would be easier to create a computed column as datatype "String" in the dataset, with expression:

if(row["unittype"]=="Nos"){
  Formatter.format(row["invoicedquantity"],"#,###.## Kg");
}else{
  Formatter.format(row["invoicedquantity"],"#,### Kg");
}

EDIT: After a deeper look at this, i found a more appropriate way, by changing "numberformat" property. In onRender or onCreate script of the data element (which should be a number datatype), we can do something like:

if(row["unittype"]=="Nos"){
  this.getStyle().numberFormat="#,###.## Kg";
}else{
  this.getStyle().numberFormat="#,### Kg";
}

This is a better approach because the data element has still a numeric datatype, so that if the report is exported in excel it will be recognized by excel as a number.

Quincyquindecagon

Thanks Dominique for replying but InvoiceQuantity and unittype come multiple times from the table (since there are multiple line items in the invoice) and when I add the above script of if-else on the onRender of that column, it doesnt change/effect the values..Am I missing something ? I am applying on the onREnder of the dataelement that I have to format – Ursulina

Dominique .. this is awesome.. It works smoothly.. Can you please help me to format it for a Indian number which is (#,##,##,##0.00)... I am unable to get that set for this number. Birt adds commas after every 3 numbers..Thanks in advance !! – Ursulina

Answer 2

Easiest way is use Number formatting with Fixed decimals,

Select the field Go to: Property Editor -> Number formatting

Select Currency and change accordingly.

Plaid