Wednesday, August 15, 2012

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.

 

5 comments:

  1. Thanks this helped me a lot - this also fixed a data in consistency caused by date format - i am now seeing data for all months in the current format.

    Thanks a lot

    ReplyDelete
  2. This has helped me solve my problem, Thanks so much.

    ReplyDelete
  3. Thank you very much! This helped me solved my problem too :)

    ReplyDelete
  4. Awesome!!! Perfect solution to my issue. Thank you.

    ReplyDelete