CODEWITHSUNDEEP
excelbusiness-objectswebi
RVD5Star
RVD5Star

Reputation: 47

Export original value to Excel keeping format only in Webi

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?

Upvotes: 2

Views: 1508

Answers (2)

dezmoog
dezmoog

Reputation: 1

I often give the user a choice to convert (using a variable in an input control) and avoid inputting the M for million beside the value. I'll indicate the number format in the column/row header (in Millions).

Input control changes both the header title at the top (Millions / Thousands / hundreds, etc) adjust the key figure by dividing to the correct multiple.

Upvotes: 0

gravity
gravity

Reputation: 2056

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.

Upvotes: 0

Related Questions