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

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

1. Create a Crystal Report.
2. Click Insert > Summary.
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

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.

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.

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