1361146 - The number of decimal places changes to 10 when Crystal report containing the Sum field is exported to Excel file

SAP Knowledge Base Article - Public

1361146 - The number of decimal places changes to 10 when Crystal report containing the Sum field is exported to Excel file

Symptom

  • The number of decimal places is occasionally changed to 10 when the Crystal report containing the Sum field is exported to Excel file.
  • Wrong decimal places in Excel file.
  • Inaccurate value in Excel file.
  • The issue only occurs with certain data, but the issue does not occur with other data.

Reproducing the Issue

  1. Create a Crystal Report.
  2. Click InsertSummary.
  3. Choose a number type field from Choose the field to Summarize.
  4. Choose Sum from Calculate this Summary.
  5. Export the report to Excel format.
  6. Open the excel file, then highlight the cell containing the sum value. For instance, it reads as 87496.105903; However, it reads as 87496.1059029999 in fx field.
  • Crystal Reports 2008
  • Crystal Reports XI R2

Cause

It is caused by the way that Microsoft Excel stores and calculates floating-point numbers. Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers. Floating-point arithmetic may give inaccurate results in Excel.

Resolution

  1. Workaround 1: Creata a formula in the crystal report to explicitly truncate and round the Sum value:   Truncate(Round(Sum ({Table1.value}),6),6)
  2. Workaround 2: In Excel, click Option > Tools > on the Calculation tab > click to select the Precision as displayed check box.

See Also

Please refer to Microsoft KBase 78113 for detailed information.

Keywords

Excel, wrong value, decimal, format , KBA , BI-RA , Reporting, analysis, and dashboards , Problem

Product

Crystal Reports 2008 V1