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
- In Crystal Reports, create a report based on a database that contains numeric values stored in a string field.
- Create a formula to convert the string to a number, like:
ToNumber ("1.45")
- Insert the formula on the report.
- Set the Product Locale to a country like: Italy
- 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