Number Formatting in Webi Report BO 4.0

[Former Member]

Hello Webi Experts,

I am struggling with number formatting in WebI report. I am using BO 4.0 SP9.  I want to display number with one decimal point so I used custom format #,###.# . But If my value is 0.9 , it is truncating 0 (zero) and showing only .9.

Please advise how to show leading zero in report as well as in Excel sheet. Also this custom formatting is showing point(.) after zero like 1., 0. in excel sheet.

Any help would be much appreciated.

[former_member211235]

Hi,

In Format Number->custom-> type 0.0 for positive cell and also for undefined cell and click add.

Hope it helps...!!

Grtz

-Anila.

[tanveer1]

Hi,

Use the below formula

= formatnumber([Field];"0.0")

Thanks,

Tanveer.

[Former Member]

you can try following formula:

= FormatNumber(measure;"00.0#,###")

Thanks,

Swapnil

[Former Member]

Hi Swapnil, Tanveer and Anila,

I tried all options suggested by you all but they are not working as expected.

I had used #,##0.# in positive cell (custom) , now it's showing correct value in BO like leading zero in front of decimal point  but when I export it in Excel sheet, it shows dot (.) after all numbers like 1., 0., 2. etc

Any idea to remove this dot (.) from excel sheet.

Thanks.

[former_member211235]

Hi,

Instead of #,##0.# use #,##0.0 in positive cell-Custom and check it out.

Grtz

-Anila

[Former Member]

Hi Anila,

I checked this option #,##0.0 also but it shows values like 1.0, 2.0 etc. I want to show whole number, if value is whole number like if value is 1 then it show 1 instead of 1.0. And if value is 0.44 then it should show 0.4 (with leading zero) .

Also, in when we export this to Excel sheet it should not show dot (.) after whole number. Now after applying #,##0.# it is showing 1. (dot), 2. , 3. etc in excel sheet.

Thanks,

[tanveer1]

Hi ,

Use the below formula

= FormatNumber([Amount];"#,##0.#")

Thanks,

Tanveer.

[former_member211235]

Use Round() function.

=Round([Measure object];1)

Grtz

-Anila.