A Space for 1000 Separator in BI Publisher

natalieroberge

Active Member
Hello JDE List

Im trying to use a space as the 1000 separator in BI Publisher. I have set the server's setting to have the space in the Digit Grouping Separator.

In the Word Properties of my numeric field I have tried the following format with no success

# ##0.00
##0.00



Does anyone have any suggestions on how to force a space in the 1000 separator of a numeric field?

Thanks


Natalie
 

lmujica

Active Member
Hello Natalie,

Have you tried to use the "Oracle format mask" instead of "Microsoft Word's Native number format mask"?

BR,


Luis.
 

Abhishek Chhajer

Well Known Member
I will suggest a very simple but powerful approach. Format the Amount String in RDA. For ex.
Amount = 123,45.66
Use code to replace comma by space and . by whatever is required.

Use that format string in your RTF.
 

jileto

Well Known Member
In the Word Properties of my numeric field I have tried the following format with no success

# ##0.00
##0.00



Does anyone have any suggestions on how to force a space in the 1000 separator of a numeric field?

#1 In the BIP property of the field, set the Formatting Type to "Number"; Format to "#,##0.00"
Yes, that's no typo up there. Grouping separator is a comma; decimal separator is a period.

What you have to understand is that the "," and "." are placeholders. They are not values that will be used in formatting. Just like when you use Oracle format mask, the "G" and "D" are placeholders for the grouping and decimal point characters. Placeholder... that is the key concept here.

#2 The grouping and decimal characters are determined by the template's LOCALE. I'm assuming that you want to format the numbers that way because you're working on a French style template.

So to achieve what you want, you can...

Method A:
Set the template LOCALE to French-Canada. In E1, do this in P95600 for the template record.

In the BIP Word plugin, do this in the plugin UI under Tools/Options/Preview. Select fr-ca as the locale. You have to do this because this is the only way (AFAIK) that the plugin can determine the locale. However, if anyone knows how to override the locale in the template, please share it with us.

Then proceed to set the BIP property of the field as I outlined in #1 above.

OR...

Method B:
If you want the leave the LOCALE as the default setting - en-us English-US - then set the text field to <?xdoxslt:format_number(TheXMLTagOfTheValueToConvert, numberOfDecimalPlaces, overrideLocale)?> for every field that you want to format.

For example: <?xdoxslt:format_number(AmountExtendedPrice_ID31, 2, 'fr-ca')?>

You don't need to worry about setting the BIP property Formatting Type to "Number". The plugin is intelligent enough to realize that the function result is a number and will force the BIP property formatting type to be "Number". Try and change it... You'll see that it will revert to "Number" when you get back to it.
 

jileto

Well Known Member
I learned something today... a less tedious and more flexible way of skinning this cat using XSL formatting.

For this example, let's say we want to format some numbers from 1234567.89 to 1^234^567*89

Where the grouping separator is '^' and the decimal point is '*'.

At the very top of your template, AFTER all the import:file directives (if you are using subtemplates), create a text field and set it to:

<xsl:decimal-format xdofo:ctx="begin" name="myFMT" grouping-separator="~" decimal-separator="*"/>

where "myFMT" is the name of our custom format.

Then set the text field that contains the tag of the number to format - for example, NumberFromUBE_ID99 - to:

<?format-number(NumberFromUBE_ID99, '#^##0*00', 'myFMT')?>

The myFMT mask we defined will be used to format the number in NumberFromUBE_ID99 displaying it in the PDF as 1^234^567*89

If you read up on the decimal-format XSL Element, you'll see that it is not just decimal and grouping separators that can be set. There's a lot more.

Thank you for asking the question. It prompted me to learn something new.
 
Top