Trimming

One little ‘feature’ of OBIEE keeps popping up in my projects – Automatic Trimming.
If you have a field with data, or calculated field, that has a trailing space in then OBIEE can remove the space for you.
This can lead to some issues, particularly when drilling down.Lets say you have a report with :

DimA Top Level Fact 1.
ABC 123

and the true value of DimA is ‘ABC ‘ – note the space
The value displayed in the report is ABC, clicking on the value will start the drill down, using a filter of ‘WHERE DIMA = ABC’
Of course NO data is returned.

The solution is to either make sure none of the data has trailing spaces or add the TRIM function. TRIM ( BOTH ‘ ‘ FROM DIMA) will do the trick.

TO_CHAR

I noticed this first when using the TO_CHAR function.If you to_char a date into month name (usually using OBIEE Function MONTHNAME) then the answer you get back is always 9 characters, which means AUGUST has three trailing spaces.You therefore have to trim the answer.