2

I am creating a report in Business Objects Web Intelligence, and there is a requirement to format a number in millions. For example, $4,879,987.23 would be formatted as $4.9M.

I tried both a custom number format and a conditional format, and I can get it to display like I want, but when exporting to excel, I get $4.9M in the underlying data instead of $4,879,987.23 - the business I am creating this for really wants to see $4.9M, but be able to interact with the cell in Excel as if it is $4,879,987.23.

Does anyone know if there is a way to export from Webi showing a ###.#M format, but using the actual number (###,###,###.##) in Excel?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
RVD5Star
  • 47
  • 6

1 Answers1

0

Format Cell to a Number with Custom formatting as follows:

#.#,,"M"

This will convert the following value...

4321987

... within the cell to instead be displayed as...

4.3M

You can adjust slightly to your client's preferences from here.

Do note that it will round according to regular arithemetic rules.

If you do the custom formatting AFTER the data has been imported into Excel, as opposed to prior, you will not see the real number adjusted or modified in any way.

gravity
  • 2,175
  • 2
  • 26
  • 34
  • Thanks for the response, gravity! Part of the requirement was that they didn't want to have to go into excel and edit the number format - they want business objects to do the work so they don't have to touch it every time the report is produced. It sounds like that might not be an option, though! – RVD5Star Jun 14 '19 at 14:02
  • Yes, this was kind of a leading answer. If you do the conversion prior to dumping into your intended output, that's the dataset you'll end up with. You could *easily* implement something in `vba` or `c#` that runs really quickly and converts the whole column to the aforementioned formatting for you, however. – gravity Jun 14 '19 at 14:04