How To Incorporate New Line In Column Text In OBIEE?

Sometimes you want to wrap the column text at some specific point or position. For that you need to understand following two concepts:-
i) Word wrap takes place when a "Line Feed('/n')" character is used. The ASCII of Line Feed is 10 in decimal. In OBIEE, char() functions returns an equivalent character for an ASCII value. So, if we use char(10) then a Line Feed is expected.

ii) In HTML, "PRE" tag is used to preserve Spaces And Line Feed.

Combining the above two concepts we can introduce a New Line in Column Text. e.g:- A column "Name" contains both the "First Name" And the "Last Name".



Now, we want to display it like the following:-



For this, edit your column formula like the following:-

Code:

REPLACE(<YOUR COLUMN>, ' ',char(10))

The above formula will replace the spaces with New Line character.

Now, Override Default Data Format and use Custom Text Format Like the Following:-



The custom Text Format is:-

Code:

  @[HTML]"<pre>"@"</pre>"

OBIEE 11g: Calculated % always shows up as 0%

 Calculations in Answers will be showing zeros instead of the calculated amount.  Below is one such calculation

(SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(BuyPoint.Match ))*100

However, this was returning 0% as the result.  This happens when you have an integer in a calculation.  A very easy fix to this is to multiply by 1.0 (don't use 1, as this will not work.  Use 1.0).  The updated calculation below works and provides a meaningful result.

((1.0*SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(BuyPoint.Match )))*100

OBIEE 11g - How to get proper date sorting in graphs

When using OBIEE, I've run into situations with graphs and tables where, if I want to show a date format of just month (Jan, Feb, etc.), using the function MONTHNAME ends up converting the date to a character, and then the sort ends up being by alphabetical order, rather than in date order.  Below is an example of this:

One way people get around this is by using the date number.  I'm not a fan of that, as it makes the reporting look bad.  Even worse, is when you have dates that cross years.  You then have to add the year to the axis, which looks bad as well, since in order to sort it correctly, it needs to show year first.  Report users aren't used to this, and don't like it.  I think you'll agree it doesn't present well.

Fortunately, there is a nice solution to get around this.  In my example, I also have the complication of having multiple days per month in my date field.  Thus, just using the 'Data Format' options in 'Column Properties' doesn't really work by itself.  Even if I switch it to be 'MMM yyyy' it will still show a separate data point for each date, showing many duplicates of the same month/year along the X axis.  The way around this is to:

1.  Convert your date to the first of the month.  To do this, you'll need to use the following OBIEE function where my date field is "Time Detail"."Time Date":    

TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH("Time Detail"."Time Date") * -1) + 1, "Time Detail"."Time Date")

2.  Use the 'data format' option to show the date in the right format (e.g. MMM yyyy).

In the below table, the first column is the raw date.  The second column is the manipulated date using the function shown in #1 above, and the 3rd column is the same as the 2nd, but using the date format as shown in #2 above. 

Now, when you graph this, your sorting ends up as expected, and there are no issues crossing over years. 

Even if I remove the year from my format, the graph is still 'smart' enough to show in the correct order.