Number Formatting in Webi Report

[Former Member]

Hi Webi Experts!

I'm struggling here with number formatting:

I have a value in report: 234,764,97

instead I want to have: 234.765

and negative balances have to be in parentheses:

is: - 234,764,97

expected: (234.765)

there is not standard definition for the requirements I have, so I need to write a formula,and trying this:

=ToNumber(FormatNumber([Value];"##.####"))

but the result is not what I want..

Can anyone assist?

Many thanks!

Irina

P.S. if the question was already raised and answered, just give me a link, I couldnt find exactly the same question. 

[Former Member · Accepted solution]

I could fix it by using custom function and modifying/creating new custom format the most appropriate number format.

KR,

Irina

[former_member188911]

Hi Irina,

the thousands and decimal separators come from the localization settings in the preferences, you can't alter them.

For what concerns the parentheses for negative values this can be done with right click, format number

Thanks and regards,

Simone

[Former Member]

Hi Simone,

you mean, I there is no chance to change "comma" to "dot" manually using formula?

and these localization settings you mentioned, can be influenced?

[Former Member]

Privet Irina,

you could be clever and use a combination of string manipulating functions such as Right() and Left() in tandem with the rounding function, however i think its gonna be a headache as you will have to work with string/number data

[Former Member]

Privet, Gleb,

if it will work, I'd lilke to try it.

But actually we wanted to keep it as simple as possible.

the next problem that I see is to exclude currency symbol.

I need pure values in the report. without currencies.

Do you any solution fot the issue?

[former_member188911]

You can choose between the document locale or the preferred viewing locale (options in Webi Rich Client or preferences in BI Launchpad) , once they are set the numbers and currencies are formatted following those preferences.

For example if i select  English USA  the dates will be formatted  MM/DD/YYYY if i select English UK they will be DD/MM/YYYY, however you can change date format with the FormatDate() function.

For numbers same concept, except that there is no way to format the decimals and thousand separators.  You can use ToNumber([String]) but at that point the number is treated as a string and useless for calculations etc. 

Therefore you have to play with the locale settings.

Thanks

Simone

[Former Member]

Will REPLACE function work?

replace "," with "."  the same as with currency symbol - replace all characters before [value] ?

[former_member188911]

Replace may work but if you convert the number to a string it will become useless for calculations etc.

My suggestion is to select a number format that you need for that document and then save it with "Permanent Regional Formatting" so that it doesn't change depending on the client settings.

I will write a Wiki page some day ... hoping sooner than later....

Thanks

[Former Member]

Hi Simone,

thanks for your help!

I was just changing preferred locale from one to another and found that german offers dots instead of commas.

BUT , it works if the value is full, for example 234.764.875,97

and as I need to have it rounded to the nearest million, and keep it meaningful for the calculations I devide it /1000000 =[Value]/1000000 and get again comma 234,77

in order to have: 234.77 I have to probably set again to english.

And still there is no way to use decimal separator and () for negative numbers ...

If you have any idea, let me know.

Thank you!

[former_member188911]

Hi Irina,

if you right click on the cell then you can select from the pop up menu Format Cell, from there you can customize the format for  Positive Negative and Zero, Undefined values.

Have a look and do some testing, apart from the above limitations you should be able to get to the format you need.

Cheers

Simone