Sage 100

Has anyone seen this: Custom Crystal report exported to Excel. If you add up the columns in Excel, the numbers do not match the total that Crystal comes up with.

Check for rounding issues

I have! I have! Just the other day, out of 4.40. They had exported to Excel and pulled it up in Excel 2003. There were negatives involved and you could tell the total was not correct if you clicked on the column and looked at the auto total. BUT if you pulled it up in Excel 2007 (OR IT was 2007 wrong, 2010 right) the columns totalled correctly.

Did you export with the Data Only format? With the normal format, the export feature has introduced a lot of merged cells that will screw up what looks like a column. They do this in an attempt to produce an Excel output similar to the printed layout.

Client did the export, so I don't know. It was pretty clean when they sent it to me to test the column total thing, but it did have headers, so maybe they exported and cleaned it up. But the version of Excel definitely made a diff as to whether the columns added correctly, or not.

There may be some variable arithmetic or conditional running totals involved in the report that could cause the inconsistency.

From an EXPORTED report? They may have cleaned it up, but they didn't add anything to it. It was just a Check History Report.

@Beth, Was Crystal total wrong or the Excel total? Export was data only, no running totals. In this case the customer is running Excel 2007, and I have also opened the file in LibreOffice 3.3 Calc and gotten the same results.

I also went through report and made sure all the numeric fields were set at two decimal places. result did not change.

The Excel total was wrong.

I thought you meant that the total in Excel differed from the total in Crystal. Can you post the spreadsheet? I would very much like to look at that.

Here it is. The totals in green are from Crystal. The ones in magenta are the ones that should match. 2 out of 3 do not. Identifying info like customer names removed.

@JimWoodhead This isn't a formatting and truncation because they have no bearing on the Excel exported data other than to control the format display in Excel. @PhilMcIntosh I can't see how you are getting such large descrepencies even with the large number of rows in the xls. Is this a straight formula for the total and the summed field? Is there a conditional running totals or any basic or crystal logic involved?

@DanBurleson - no running totals. There is some suppression logic but it is on entire rows (if A and B and C are all 0 don't show it).

@PhilMcIntosh I would limit the record set to a few records and try to see if you can tell what's happening. I tested exporting lots of records with a full 10 digits of accuracy and it totals in the same exported to Excel (2003 & 2010) as it does in Crystal. Post you report it you want.

@DanBurleson - I limited it to customers beginning with A & B, and the totals matched