1213922 - How to convert a string to a number in Crystal Reports?

• How to convert a text to a number? 
• How to convert a string field to a numeric field in Crystal Reports?   
• A database field is defined as string data type, contains numeric characters. To perform calculations or summaries such as a sum or an average, the field must be defined as a number data type. How to convert a string field containing numeric characters to a number field in Crystal Reports?

• 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

• Use the function: ToNumber in a formula, to convert a string field containing numeric characters to a number data type.
    
1. In Crystal Reports, open the report on which you want to convert a text to a number.
       
2. Create a new formula field.
        
3. In the Formula Editor, use the function ToNumber to convert the text into a number like:
        
       ToNumber({YourStringField})
         
   Important Note: Best Practice is to also use the function: isNumeric, to avoid error in the event the field does not contain a numeric value. See the common error section below for an example on using this function.
           
4. Save and close the formula.
    
5. Place the formula on the report.
        
   Upon completing the above steps, you have created a formula field that returns a number data type.
   Calculations and summaries can be successfully performed on this formula field.
   Also, you can format this formula field using the predefined number styles or customized styles in the Format Editor.
               
           
       
• Issues when converting a numeric text value, to a number, using the function "ToNumber":
• Value converted to different number on different computers;
• When refreshing the report, it generates the error: "Field is non-numeric"
    
    
• Value converted to different number on different computer:
• This occur because the regional setting changes how a string value is converted to a number.
• For more details, and solution, see the SAP Knowledge Base Article 2733982      
     
    
• When refreshing a report, it generates the error: "Field is non-numeric":
• This error indicates non-numeric data is contained in the database string field, and therefore, the function ToNumber cannot convert non-numeric characters to a numeric value.
• To resolve this issue, use the function: isNumeric, before converting the text to number.
             
1. In Crystal Reports designer, go to the design tab of the report, and edit the formula field that convert the text to number. 
            
2. In the Formula Editor, use the function IsNumeric, as well as isNull before converting the text to a number. The formula will look like:
      
       If Not isNull({YourStringField}) Then
           If isNumeric({YourStringField}) Then
               ToNumber({YourStringField})
       Else 0
        
       
   Notes: 
   
   - This above formula verifies if the field does not contain null or blank values and if the field contains a numeric characters. If both confitions are true, then the characters are converted to a number value. If no, the value 0 is returned.
   
   - If the report has the report option: 'Convert NULL Field Value to Default' checked, the formula line, "If Not isNull ({YourStringField}) Then", is not required.
              
3. Save and close the formula.

NUMBER TEXT ALPHANUMERIC NON NUMERIC CONVERT CHANGE ADD DATA TOTAL VAL TONUMBER Crystal Reports Format numeric text Err Msg: "Non-Numeric Value" Err Msg: "The string is non-numeric." , c2010092 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

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