SAP Knowledge Base Article - Public

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

Symptom

  • 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.

Environment

  • 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

Reproducing the Issue

  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 

Cause

  • 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.

Resolution

  • 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

Keywords

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

Product

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