OTBI and Unusual Number Formats

Posted on

At one of our clients we’ve exposed a number of infolets to employees to give them information about sicknesses, vacation etc. One of these infolets lists the holiday balances an employee has left.


The upper right figure shows the carryover from the previous holiday year plus any adjustments that HR have applied. As an adjustment could be positive or negative the client wished to always display the sign against the number, not just to show the minus sign when it was a negative adjustment (which is OTBI’s default).

The quickest way to achieve this was to apply a custom number format mask to the field.

Most people will be familiar with using a mask such as ###,###,##0.## where the hash is for an optional digit – so the data doesn’t pad with unnecessary zeroes – however you can use similar principles to force a plus sign in front of positive numbers.

The two mask techniques to combine are using ” ” around literals and using a semi-colon to split positive and negative number formats.

This means the mask:


will use the blue part (with the prefixed ‘+’) for positive numbers and the red part (with the prefixed ‘-‘) for negative numbers.

The documentation for this can be found here:


however I found that the quotes were not necessary and the mask +##0.##;-##0.## worked fine.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s