I have a simple report (in report studio), version 11.2.4. A few columns have quantity or dollar amounts (all numbers). The user requirement is that they want to export the report in excel data format and the number columns need to be in the excel format 'Number' (not excel format 'general'). So far easy enough, but then there is a second requirement: they also want to have the number values to have no commas for the thousands separator. I can't figure out how to satisfy both of these requirements. In Cognos I set format to 'Number' and set thousands separator = 'No'. But that does not transfer over to excel, it comes in as excel format 'Number' WITH the thousands separator.
I tried to convert the number to a character string, this satisfies requirement #2 (removes comma) but does not satisfy requirement #1 (excel format = number).
Any ideas on this?
Quote from: hamlet on 06 Aug 2025 02:17:14 PMI have a simple report (in report studio), version 11.2.4. A few columns have quantity or dollar amounts (all numbers). The user requirement is that they want to export the report in excel data format and the number columns need to be in the excel format 'Number' (not excel format 'general'). So far easy enough, but then there is a second requirement: they also want to have the number values to have no commas for the thousands separator. I can't figure out how to satisfy both of these requirements. In Cognos I set format to 'Number' and set thousands separator = 'No'. But that does not transfer over to excel, it comes in as excel format 'Number' WITH the thousands separator.
I tried to convert the number to a character string, this satisfies requirement #2 (removes comma) but does not satisfy requirement #1 (excel format = number).
Any ideas on this?
There are some limitations on what formatting options in a Cognos report Excel supports, including the decimal and group separators in numbers. In short, I don't think you can do this easily. One option might be to fire a macro after the Excel output has been generated, but this wouldn't take into account people rendering the reports interactively.
Details of the formatting restrictions are below:
https://www.ibm.com/docs/en/cognos-analytics/11.2.x?topic=format-unsupported-cognos-analytics-watson-formatting
Cheers!
MF.