"lakhs format" is not being applied correctly when used with aggregate functions.

[senthiltrends]

The number format "lakhs format" represented by the pattern #,##,##,###.00 is not being applied correctly when used with aggregate functions.

The dataset rows are displaying correctly, but when it comes to aggregates, if there are no decimals available, it displays in a format like #,##,##,###

We are using the expression

--------------------------------------------------------

var columnValue = dataSetRow["sale_value"];
var customFormat = "#,##,##,##0.00";
var customFormat1 = "#,###,###,##0.00";

// We can add a test here for conditional formatting
if (columnValue != null && dataSetRow["number_format_flg"] != null) {
var symbols = null;
var formatter = null;

if (dataSetRow["number_format_flg"] == "L") {
symbols = new Packages.com.ibm.icu.text.DecimalFormatSymbols(new Packages.java.util.Locale("en", "IN"));
formatter = new Packages.com.ibm.icu.text.DecimalFormat(customFormat, symbols);
} else {
symbols = new Packages.com.ibm.icu.text.DecimalFormatSymbols(new Packages.java.util.Locale("en", "US"));
formatter = new Packages.com.ibm.icu.text.DecimalFormat(customFormat1, symbols);
}

var value = new Packages.java.math.BigDecimal(columnValue.toString());
formatter.format(value);
} else {
"-";
}

[hvbtup]

You are showing a little JS and you say that soem numbers are not formatted correctly. But it is not clear how this is connected.
If you think there is a bug, please provide a self-contained example report.

[senthiltrends]

Hi
Attaching the how the expression is defined and the reports with formatted for row value and not formatted for aggregated value, kindly do the needful

[merks]

I think folks need a way to reproduce the problem---a small sample report---rather than a screenshot of a problem. (It seems strange to me (not having a clue about any of the details mind you) that the value is of type String rather than some numeric type for which numeric formatting would be applicable.

[senthiltrends]

Please download the sample RPT Design and sample excel file from our OneDrive link

https://levantaretechnology-my.sharepoint.com/:u:/g/personal/senthild_levantare_co_in/EY-T-vTrgiBImYnO6VxV7G4Bdd77ANL4ejBMUJ91LVOq_Q?e=mfcwmR

[hvbtup]

Without looking at the rptdesign (just the screenshot), I think it cannot make sense to use the SUM aggregate function with a data type of String. Implicit type conversion is happening then (I bet), which is the root of many programming problems.

If you want to calculate something, you need a number data type (in your case, Decimal).
Then you can apply number formatting for the data item in the layout.

However, the aggregation uses use double internally, which may cause additional conversion errors (that is another issue which is discussed in the comments of #1089)

In fact, when I develop invoice reports or similar reports where correct rounding and number formatting is really important, I do not use BIRT to calculate or format any numbers. Instead, I perform all the calculations and formatting at the SQL level.

[speckyspooky]

This topic is not a problem of BIRT this is a topic of the requirement solution of the report-developer.
The first thing is that you use at the same time in your report "number" and "string:minus".
This is a problem if the sum will try to calulcat a number with a string => exception.

The best way would be that you use a aggregation function based on ytpe "float" and use the number format.
And you add a second element to display a "minus" if needed both ypu can control with the visbibility condition.

But currently you try to much things at the same time.

Additional on it I don't know why you try to use at script level every time to convert the value to a string format.
Because the value will be re-casted internally for the aggregation formula of SUM.

[senthiltrends]

We've hit a snag in our reporting system regarding data formatting. We've established a rule: if the column value is "number_format_flg='L'", the number should be formatted in lakhs like #,##,##,###.00. Otherwise, it should follow the pattern ###,###,###.00. This rule works smoothly for individual rows when the data type is set as string. However, during data aggregation, the formatting isn't applied as expected.

As suggested by you, even after experimenting with setting the data type as numeric without any specific conditions on the column, the aggregate values persist in the ###,###,###.00 million format.

We're on the lookout for a solution where both individual row and aggregate value formatting adhere to the column value. For reference, we've included the RPT design and data Excel files in the provided link. Any further clarification needed is welcome. Your assistance in resolving this issue would be immensely valued.
Refer the screenshot for format issues

https://levantaretechnology-my.sharepoint.com/:u:/g/personal/senthild_levantare_co_in/EY-T-vTrgiBImYnO6VxV7G4Bdd77ANL4ejBMUJ91LVOq_Q?e=mfcwmR

[speckyspooky]

Here we have a combination of 2 things.
The first thing is that the "lakhs" format isn't correctly supported due to internal number format usage of the original java number format. I have changed it to the better library with PR #1708

The second thing is really that you mixed up the stuff of the total in one aggregation field.
Currently without the PR you can try to create 2 total line one for US- and one for IN-format and control it with the visibility condition.

With the fix you will be able to use a script at the onCreate method to format the values:

[speckyspooky]

The fix is merged to the master branch.