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

SAP Knowledge Base Article - Public

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

Symptom

  • How to convert a text to a number?
     
  • How to convert a string field converted to a number field?
       
  • A database field is defined as string data type. However, the string field 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. Also, to format the field using the predefined number styles or customized styles in the Format Editor, the field must be a number type. How can you convert a string field containing numeric characters to a number field in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • Use the function: ToNumber in a formula, to convert a string field containing numeric characters to a number data type.
      
    1. In Crystal Reports designer, 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})
         
      Note: The recommendation 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.
               
           
  • Common Issue when refreshing a report converting text to number using the function: ToNumber:
        
    When previewing a report in Crystal Reports, it generates the error "Field is non-numeric".
    This error message indicates that non-numeric data is contained in the database string field.
    The ToNumber formula function is unable to convert non-numeric characters to a number 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.

Keywords

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 BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016