[Bea Haazen]

Can anyone please tell me how to describe numberformat for percentage correctly ?

My script says :

if (this.getRowData().getColumnValue("UnitName")=='Percentage'){
this.getStyle().numberFormat = "###,##0%";}

but the data from my dataset : 80,00 this way results in : 8.000% instead of 80%

thanks,
Bea

[mwilliams]

"#,00%" should get you 80,00% from 80,00.

[Bea Haazen]

yes, it does, thanks Michael.

but it goes wrong starting from 100,00 - #,00% then results in 1.00.00%

any idea why?

thanks,
Bea

[mwilliams]

I see the same if I set my Eclipse locale to German and use the code I said above. Normally, if you use % in the format code, it multiplies by 100 and adds the % sign. So, if you're wanting 80,00 to appear as 80%, try doing this. Divide your field by 100 and then set the format code to "Percent". That works for me. Let me know.

[mwilliams]

Based on your email where you state you want to format the values in a field depending on another field, you could do something like this. I added a computed column to my dataSet of type string, since this is only for display. Then, I used the following expression:

importPackage (Packages.java.text);
importPackage (Packages.java.util);
nf = NumberFormat.getInstance(new Locale("da","DK"));
if (row["UnitName"] == "Aantal"){
nf.applyPattern("#");
out = nf.format(row["Data"]);
}
else if (row["UnitName"] == "Aantal 2 decimalen"){
nf.applyPattern("#.00");
out = nf.format(row["Data"]);
}
else if (row["UnitName"] == "Percentage"){
nf.applyPattern("#%");
out = nf.format(row["Data"]/100);
}
else if (row["UnitName"] == "Percentage 2 decimalen"){
nf.applyPattern("#.00%");
out = nf.format(row["Data"]/100);
}
else{
nf.applyPattern("#,### €");
out = nf.format(row["Data"]);
}
out.toString();

Hopefully this gets you what you're wanting. Let me know if you have issues or questions.

[Bea Haazen]

Hi Michael,

I don't completely get this.

- is this the expression for the computed column ? or is this a script that I put in my cells "BaseData" and "CompData" ?
- which analysis type did you use next to String ? Dimension ?
- where you write : out = nf.format(row["Data"]) - should the ["Data"] refer to my original columns that contain my data ? Do I use ["BaseData"] for one computed column and ["CompData"] in another computed column ?

i tried the expression you gave me in a computed column,but i get an error when previewing the result of my dataset. i just get "error happened while running the report".

[mwilliams]

I just used a generic column, in my testing, called "Data". That's why my code has that field. You'd need to create two computed columns. One for each and replace the row["Data"] with your field name. You could also put this in a dynamic text box in your table, rather than defining a binding, for it. That would also work. For your other question, I chose "measure".

[Bea Haazen]

Michael, just implemented your solution for "BaseData".
When previewing I get the following error message :

org.eclipse.birt.data.engine.core.DataException: Fail to compute value for computed column "CompBaseData".
A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "importPackage (Packages.java.text);

importPackage (Packages.java.util);

nf = NumberFormat.getInstance(new Locale("da","DK"));

if (row["UnitName"] == "Aantal"){

nf.applyPattern("#");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 1 decimal"){

nf.applyPattern("#.0");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 2 decimal"){

nf.applyPattern("#.00");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Percentage"){

nf.applyPattern("#%");

out = nf.format(row["BaseData"]/100);

}

else if (row["UnitName"] == "Percentage 2 decimal"){

nf.applyPattern("#.00%");

out = nf.format(row["BaseData"]/100);

}

else{

nf.applyPattern("#,### €");

out = nf.format(row["BaseData"]);

}

out.toString();":
Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number (unnamed script#25)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:523)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper.process(ComputedColumnHelper.java:126)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.processFetchEvent(RowResultSet.java:160)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.doNext(RowResultSet.java:121)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:91)

at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.initialize(SimpleResultSet.java:177)

at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.<init>(SimpleResultSet.java:117)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:1009)

at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:441)

at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1124)

at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)

at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:173)

at org.eclipse.birt.report.engine.api.impl.ExtractionResults.nextResultIterator(ExtractionResults.java:74)

at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:75)

at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:337)

at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.
There are errors evaluating script "importPackage (Packages.java.text);

importPackage (Packages.java.util);

nf = NumberFormat.getInstance(new Locale("da","DK"));

if (row["UnitName"] == "Aantal"){

nf.applyPattern("#");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 1 decimal"){

nf.applyPattern("#.0");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 2 decimal"){

nf.applyPattern("#.00");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Percentage"){

nf.applyPattern("#%");

out = nf.format(row["BaseData"]/100);

}

else if (row["UnitName"] == "Percentage 2 decimal"){

nf.applyPattern("#.00%");

out = nf.format(row["BaseData"]/100);

}

else{

nf.applyPattern("#,### €");

out = nf.format(row["BaseData"]);

}

out.toString();":
Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number (unnamed script#25)

at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)

at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:946)

at org.eclipse.birt.data.engine.impl.ComputedColumnHelper$ComputedColumnHelperInstance.process(ComputedColumnHelper.java:486)

... 15 more

Caused by: org.eclipse.birt.core.exception.CoreException: There are errors evaluating script "importPackage (Packages.java.text);

importPackage (Packages.java.util);

nf = NumberFormat.getInstance(new Locale("da","DK"));

if (row["UnitName"] == "Aantal"){

nf.applyPattern("#");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 1 decimal"){

nf.applyPattern("#.0");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Aantal 2 decimal"){

nf.applyPattern("#.00");

out = nf.format(row["BaseData"]);

}

else if (row["UnitName"] == "Percentage"){

nf.applyPattern("#%");

out = nf.format(row["BaseData"]/100);

}

else if (row["UnitName"] == "Percentage 2 decimal"){

nf.applyPattern("#.00%");

out = nf.format(row["BaseData"]/100);

}

else{

nf.applyPattern("#,### €");

out = nf.format(row["BaseData"]);

}

out.toString();":
Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number (unnamed script#25)

at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:295)

at org.eclipse.birt.core.script.ScriptContext.evaluate(ScriptContext.java:154)

at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evalExpr(ScriptEvalUtil.java:918)

... 16 more

Caused by: org.mozilla.javascript.WrappedException: Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number (unnamed script#25)

at org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:1773)

at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:183)

at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)

at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:66)

at org.mozilla.javascript.gen.c2481._c0(unnamed script:25)

at org.mozilla.javascript.gen.c2481.call(unnamed script)

at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)

at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)

at org.mozilla.javascript.gen.c2481.call(unnamed script)

at org.mozilla.javascript.gen.c2481.exec(unnamed script)

at org.eclipse.birt.report.engine.javascript.JavascriptEngine.evaluate(JavascriptEngine.java:290)

... 18 more

Caused by: java.lang.IllegalArgumentException: Cannot format given Object as a Number

at java.text.DecimalFormat.format(DecimalFormat.java:487)

at java.text.Format.format(Format.java:140)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)

... 27 more

[mwilliams]

What is your data type on your column?

[Bea Haazen]

the BaseData and CompData columns are type "Float" - analysis type "Dimension"

[mwilliams]

That's what I used in my report. Take a look at the report I made, which uses the computed column code I gave you.

Attached File(s)

•  DynamicFormatReport.zip (2.55K)
  Number of downloads: 6

[Bea Haazen]

Hi Michael,
your example report does indeed work fine, exactly what I need. I copied the expression of your computed column to make one in my report, but I still get an error.

the error now shows a problem with your first two lines:

importPackage (Packages.java.text);
importPackage (Packages.java.util);

do I need to install/activate something on my computer to execute these instructions ?

[cbrell]

Rhino cares about spaces.
importPackage is a method. The content between the parantheses is the parameter value for this method call. In Rhino there must be no space between the method and the parantheses.

Try

importPackage(Packages.java.text);
importPackage(Packages.java.util);

[Bea Haazen]

still the same error.

[mwilliams]

Not sure if it'll help, but can you try DecimalFormat instead of NumberFormat? Just change that one line. I got a similar error helping someone else with a similar issue and changing from NumberFormat to DecimalFormat got rid of the error. Not exactly sure why, but it did.

If that doesn't work, can you send me your design?

[mwilliams]

I was pretty sure I didn't have the exact same error as you above, so I went and tried something out. The only way I was able to get the same error as you posted in my quick testing was to pass a string to the number format. You might make sure you're not passing a string field or that you don't have a typo in your script.

[Bea Haazen]

- changed NumberFormat to DecimalFormat : no change
"Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number (unnamed script#26)"
- I'm pretty sure I have no typo's, I copied your example expression and just changed "Data" to "BaseData" (my column name)
- "BaseData" is a float column - "FormattedBaseData" (my computed column) is a string column. i tried the expression with the "FormattedBaseData" set to float, but that doesn't work either.

Here's a copy of my expression:

importPackage (Packages.java.text);
importPackage (Packages.java.util);
nf = NumberFormat.getInstance(new Locale("da","DK"));
if (row["UnitName"] == "Aantal"){
//nf = NumberFormat.getInstance(new Locale("da","DK"));
nf.applyPattern("#");
out = nf.format(row["BaseData"]);
}
else if (row["UnitName"] == "Aantal 2 decimalen"){
//nf = NumberFormat.getInstance(new Locale("da","DK"));
nf.applyPattern("#.00");
out = nf.format(row["BaseData"]);
}
else if (row["UnitName"] == "Percentage"){
//nf = NumberFormat.getInstance(new Locale("da","DK"));
nf.applyPattern("#%");
out = nf.format(row["BaseData"]/100);
}
else if (row["UnitName"] == "Percentage 2 decimalen"){
//nf = NumberFormat.getInstance(new Locale("da","DK"));
nf.applyPattern("#.00%");
out = nf.format(row["BaseData"]/100);
}
else{
//nf = NumberFormat.getInstance(new Locale("da","DK"));
nf.applyPattern("#,### €");
out = nf.format(row["BaseData"]);
}
out.toString();

[mwilliams]

Can you show a sample of what some of your data in the BaseData column actually looks like? Maybe a screenshot of your preview results screen in your dataSet editor? Are the values already like 80,00 or are they like 80.00? If like 80,00, this could be the issue. In another post or something you sent me, I got the data I use for the example I posted above, which has decimals. Let me know.

[Bea Haazen]

enclosed a screenshot of my dataset preview. the values are like 80.00
btw, my system locale is Dutch(Belgium)

Attached File(s)

•  Dataset.jpg (141.66K)
  Number of downloads: 0

[mwilliams]

Try this. It just makes sure you're working with a float before trying to format.

importPackage (Packages.java.text);
importPackage (Packages.java.util);

nf = NumberFormat.getInstance(new Locale("da","DK"));
temp = parseFloat(row["BaseData"]);

if (row["UnitName"] == "Aantal"){
	nf.applyPattern("#");
	out = nf.format(temp);
}
else if (row["UnitName"] == "Aantal 2 decimalen"){
	nf.applyPattern("#.00");
	out = nf.format(temp);
}
else if (row["UnitName"] == "Percentage"){
	nf.applyPattern("#%");
	out = nf.format(temp/100);
}
else if (row["UnitName"] == "Percentage 2 decimalen"){
	nf.applyPattern("#.00%");
	out = nf.format(temp/100);
}
else{
	nf.applyPattern("#,### €");
	out = nf.format(temp);
}

out.toString();