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:
where my date field is "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
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.
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.
