SAP Knowledge Base Article - Public

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

Symptom

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

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 therefore if the values stored in the database contains dots, then it will be seen as thousand separators in some countries, and this is why the values converted to a number is different depending on the Regional Setting of the local computer.

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 like:

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

Local StringVar decimalSeparator := ".";
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