Kinamu Reporter is a reporting tool for Sugar CRM. It supports basic reports and allows the data to be exported to Excel.
The problem I was having was that some values stored in Sugar come out as numbers (integers). That was fine until the report was exported to Excel. Once Excel got the numbers, it automatically added two decimal places and commas. So the year 2012 was being displayed in Excel like this:
2,012.00
That does not exactly look like a year!
The solution is to tell Kinamu what datatype the column should be exported as. The export process then handles it nicely by telling Excel which values should be treated as string and not reformated. The “custom function” column in Kinamu Reporter allows me to do this.
To take a numeric value, but output it to Excel as a string, just enter this into the “custom function” column in the report editor:
CAST($ AS CHAR)
That’s it. The output value will be cast to a string, exported into the CSV as a quoted string, and then interpreted as a string by Excel, avoiding some of the reformating issues.
No comments yet.