2733982 - Function: ToNumber, returns different results in Crystal Reports depending on the Regional Settings

• Incorrect numbers.
• Number different when viewing the report in Crystal Reports, versus when viewing the report in SAP BI LaunchPad.
• Numeric values are different, depending on the computer regional settings, when converting string to numbers in Crystal Reports.
• The function: ToNumber ignores decimal places in Crystal Reports, when the Product Locale is set to some European countries.

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020
      
• SAP Crystal Reports for Enterprise 4.1
• SAP Crystal Reports for Enterprise 4.2
• SAP Crystal Reports for Enterprise 4.3

1. In Crystal Reports, create a report based on a database that contains numeric values stored in a string field.    
2. Create a formula to convert the string to a number, like:
   
      ToNumber ("1.45")
     
3. Insert the formula on the report.   
4. Set the Product Locale to a country like: Italy     
5. Notice the formula returns the number: 145, instead of the expected number 1.45 

• In many European countries, dot is not used as a decimal separator. The decimal separator used is a comma, and if the values stored in the database contains dots, then the function ToNumber will ignore this character when the regional setting is set to a country that uses comma for the decimal separator, and therefore the number will be converted to a different value.
       
• In other words, the function: ToNumber take in consideration the Regional Setting of the local computer when converting a numerical string to a number, and different countries uses different decimal separator, which will affect how the value is converted.

• When the numeric string contains decimals separators, then to be able to convert the string to a number regardless of the regional setting, create a formula that take in consideration the decimal separator character used in the data source to convert it to a number.   

Local StringVar stringValue := <Database Field Name>;  // Insert your numeric field stored in text format here.

Local StringVar decimalSeparator := ".";   // Decimal separator, dot (.)
Local StringVar integerValue := "0";
Local StringVar fractionalValue := "0";
Local NumberVar numericValue := 0;

If decimalSeparator in stringValue Then
(
     integerValue := Split(stringValue,decimalSeparator)[1];
     fractionalValue := Split(stringValue,decimalSeparator)[2];
)
Else
     integerValue := stringValue;

If isNumeric(fractionalValue) Then
     NumericValue := (ToNumber(fractionalValue)) / ToNumber("1" + ReplicateString("0",Length(fractionalValue)));

If isNumeric(integerValue) Then
     numericValue := numericValue + ToNumber(integerValue);

numericValue

  Currency Convertion, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020