Oracle BI Administration Tool

How to order by month number not alphabetically by month name?

When you add a column with MON or MONTH date format in OBIEE report its values would be sorted alphabetically, not by the month number order. That way December would come before January etc. To sort by months in their correct order we need a date in a month number format (1 for January, 2 for February etc.).

There are two solutions for this:

Change in repository

To avoid such issues, a date column with Month name should have a proper sorting order. This can be done in the repository, by setting sorting order by another date column with correct order (Month Number) in a logical layer. In the logical layer click on Month column’s criteria and change the Sort order column settings to order it by Month number column (it should be available in Date dimension).

Workaround in BI Answers

There is also a workaround solution to achieve correct month order in Oracle BI Answers. To solve this, add the full date column from the same dimension, change the formula to evaluate date in YYYY-MM format.

EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER (30), "Date Column", 'YYYY-MM')

Now you can add the column to the table or graph and set the sorting by that column. The values will be sorted by column name. You can hide the Month Number column so its values are not visible in the report.