Rounding issues?

Tom

Hi guys, I'm new to BIRT so please pardon my n00bish questions. I'm sure I'll have quite a few.

I'm running a report with a table where I'm trying to calculate what is essentially an average. I expect these averages to always be very close to 1 (i.e. 0.99876). To do this, I'm running a query where I'm getting two "Count" results, lets call them "Count of P" and "Count of F". The number I need to calculate is:

("Count of P")/("Count of P" + "Count of F")

I had no problem getting BIRT to do this, but I am running into an issue. It appears that, no matter what I do, for decimal values very close to 1 (like, for example, 0.9999), BIRT rounds up to 1.

At first, to calculate the average, I was using the following expression:

row["Count of P"] / (row["Count of P"] + row["Count of F"])

That seemed to work fine, except, as I mentioned above, when values were very close to 1. Thinking it might be a JavaScript rounding thing, I switched to this expression:

Finance.percent((dataSetRow["Count of P"]+ dataSetRow["Count of F"]), dataSetRow["Count of P"])

Which gives me the same exact result, except *100. So, instead of getting 99.99, I get 100.

I also tried messing with the "Format Number" settings. I set it to custom with a Format Code of "#0.0000000000". Still the same problem.

Any ideas?

Virgil Dodson

Hi Tom,

Are you selecting Decimal for your type? Also, are you creating this computed field within a report element, or as a computed column on the data set? I created a quick test as a computed column on the data set and then formatted that field on the report as fixed 5 decimal places. I don't see the rounding problem you are seeing.... except that (6) 9's after the decimal .999999 correctly rounds to 1 when I select round to 5 decimal places.

If you want to handle the rounding yourself, maybe a javascript function like below will allow you to truncate after a certain number of decimals. 5 in this example.

var numStr=row["computed"].toString();
var decPos = numStr.indexOf(".");
if ( decPos == -1 )
numStr + ".0000";
else
{
// add zeros to get in case it ends at the decimal
numStr += "0000";
numStr.substr(0,decPos+5);
}

In this example, I first created a computed column called 'computed' that took (field_a/(field_a+field_b))... then created this second computed column that did the truncation. You should be able to easily make this a single computed column.

Once you add this new field to the report, just select Unformatted so it won't try to round.

preetiGu

Hello,

I am doing aggregation is my report. I want data to be printed till 2 digits after decimal. For ex:- 12.34
I found out one Math.round(). But it is not allowing me to specify number of digits.

Do let me know if you have info about it.

Thanks,
Preeti

Virgil Dodson

Hi Preeti,

You should be able to set the number formatting on the aggregation in the Property window without using an expression to set the precision. See screenshot.

If you still need to do the rounding in an expression, the Math.round function can still be used, with a little extra work like below:

For 2 decimal places
var result=Math.round(original*100)/100

For 3 decimal places
var result=Math.round(original*1000)/1000

etc.

preetiGu

Hello Vergil,

Thanks for the reply. Actually I have to export report generated in xls. The issue is when I am doing division and format number to 2 decimal places data displayed comes as 2 decimal places only. But when I select that column in xls it shows me the 12 places of decimal.
If report has more columns with decimal places then report fails to open in xls. I checked the logs it says bad value(77.5862068965517).
So I did rounding in database query for all columns. But aggregation is happening in Birt report. Even though I have specified format to 2 places. Whole value is getting exported to xls.
I tried Math.round(val*100)/100. Still it's showing more then 2 decimal places when I select that value.
Please see the attachment.

Thanks,
Preeti

carlosridg

It seems like Math.round() is a better solution, but it is not! In some cases it will NOT round correctly. Also, toFixed() will NOT round correctly in some cases.

To correct the rounding problem with the previous Math.round() and toFixed(), you can define a custom JavaScript round function that performs a "nearly equal" test to determine whether a fractional value is sufficiently close to a midpoint value to be subject to midpoint rounding. The following function return the value of the given number rounded to the nearest integer accurately.

Number.prototype.roundTo = function(decimal) {
 return +(Math.round(this + "e+" + decimal) + "e-" + decimal);
}
var num = 9.7654;
console.log( num.roundTo(2)); //output 9.77