A simple percentage question
Bea Haazen
Member
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
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
0
Comments
-
yes, it does, thanks Michael.
but it goes wrong starting from 100,00 - #,00% then results in 1.00.00%
any idea why?
thanks,
Bea0 -
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.Warning No formatter is installed for the format ipb0
-
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:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
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();
</pre>
<br />
Hopefully this gets you what you're wanting. Let me know if you have issues or questions.Warning No formatter is installed for the format ipb0 -
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".0 -
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".Warning No formatter is installed for the format ipb0
-
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 more0 -
the BaseData and CompData columns are type "Float" - analysis type "Dimension"0
-
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 ?0 -
Rhino cares about spaces.<br />
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.<br />
<br />
Try<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.text);
importPackage(Packages.java.util);
</pre>0 -
still the same error.0
-
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?Warning No formatter is installed for the format ipb0 -
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.Warning No formatter is installed for the format ipb0
-
- 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();0 -
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.Warning No formatter is installed for the format ipb0
-
enclosed a screenshot of my dataset preview. the values are like 80.00
btw, my system locale is Dutch(Belgium)0 -
Try this. It just makes sure you're working with a float before trying to format.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
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();
</pre>Warning No formatter is installed for the format ipb0 -
Michael, this is it ! it works
thank you so much for your support !!0 -
hi Michael,
Remember the expressions you provided me with to format computed columns?
When the "UnitName" column has no value, the expressions generate a "question mark" in my report.
How can I avoid this ? when the UnitName column is empty, I want the computed columns to be empty too.
thanks for your help.0 -
hi Michael,
I'm coming back to this topic with one more question.
we use the locale "da","DK" in this expression. which locale do I need to use if I want:
- a . as thousand separater (now it is a space)
- a zero before the decimal separator, being a "comma" (now i get eg. ,85% instead of 0,85%)
tx,
Bea0 -
enclosed a screenshot of a preview.
the "?" numbers have "space" as thousand separator. the format in my expression for euro numbers = "#,### ?"0 -
I found such a solution: if you have 80.00, but you want to get 80,00%, then you need to set the format like this: Format: Custom and Format code: ###,##0.00%
0 -
need format #0.00'%', not ###,##0.00%
0
Categories
- All Categories
- 118 Developer Announcements
- 52 Articles
- 150 General Questions
- 137 Services
- 56 OpenText Hackathon
- 35 Developer Tools
- 20.6K Analytics
- 4.2K AppWorks
- 9K Extended ECM
- 912 Cloud Fax and Notifications
- 82 Digital Asset Management
- 9.3K Documentum
- 30 eDOCS
- 175 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 6 XM Fax