A simple percentage question

Bea Haazen · June 19, 2012

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 · June 19, 2012

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

Bea Haazen · June 20, 2012

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 · June 20, 2012

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 · June 27, 2012

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: 
 
<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>
 
Hopefully this gets you what you're wanting. Let me know if you have issues or questions.

Bea Haazen · June 28, 2012

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 · June 28, 2012

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 · June 28, 2012

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 · June 28, 2012

What is your data type on your column?

Bea Haazen · June 28, 2012

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

mwilliams · June 29, 2012

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.

Bea Haazen · July 3, 2012

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 ?

CBR · July 3, 2012

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 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.text);
importPackage(Packages.java.util);
</pre>

Bea Haazen · July 3, 2012

still the same error.

mwilliams · July 3, 2012

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 · July 3, 2012

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 · July 4, 2012

- 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 · July 5, 2012

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 · July 6, 2012

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

mwilliams · July 6, 2012

Try this. It just makes sure you're working with a float before trying to format. 
 
<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>

Bea Haazen · July 9, 2012

Michael, this is it ! it works

thank you so much for your support !!

mwilliams · July 9, 2012

You're welcome! Glad to help! Let us know whenever you have questions!

Bea Haazen · September 19, 2012

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.

mwilliams · September 19, 2012

You should just have to wrap the other code in an if statement that checks if your value is null or not. If it's null, just output null, instead of a formatted value.

Bea Haazen · November 19, 2012

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,
Bea

mwilliams · November 20, 2012

For the percentage ones, instead of putting #.00 as the format code, try using 0.00. For the other, the thousands separator should be a '.' Can you show me an example that has this issue?

Bea Haazen · November 21, 2012

enclosed a screenshot of a preview.

the "?" numbers have "space" as thousand separator. the format in my expression for euro numbers = "#,### ?"

mwilliams · November 26, 2012

With da_DK, I get '.' as separator, not a space. Can you reproduce the issue, where you're getting a space, in a way that I can run it?

Tais Belousova · December 17, 2020

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%

Tais Belousova · December 17, 2020

need format #0.00'%', not  ###,##0.00%

A simple percentage question

Comments

Categories