Setting a Sort Order Column

Have you ever noticed a property called Sort order column, located in the General tab of the Logical Column Properties Dialog Box? By default, this value is set to “None”, which is usually the desired value, but to demonstrate when this property comes in handy, let’s look at an example. Suppose your repository contains a column called “Month” and a column called “Month Number”. “Month” contains these values: Jan, Feb, Mar, Apr,…Dec. “Month Number” contains these corresponding values: 1,2,3,…12. If you have a Dashboard Prompt or a Report containing the “Month” column, the “Month” values will likely be sorted in this order (alphabetically as opposed to chronologically): Apr, Aug, Dec… Sept.

Sorting month names alphabetically is almost never the desired result; however, this sort order will be the default if the Sort order column for “Month” is set to None. You could try to correct this problem by using SQL in your Dashboard Prompt, or by adding additional sort columns to your reports, but there is a much easier way: simply set the Sort order column for the “Month” logical column in the repository. In this case, you would use the “Month Number” as the Sort order column. After reloading the metadata, the next time you use the “Month” column in a report or Dashboard prompt, OBIEE will automatically apply an ORDER BY Month Number clause in the SQL issued to the database, regardless of whether or not “Month Number” is included in the SELECT statement, or even in the Subject Area for that matter.

First published in :
http://www.biconsultinggroup.com/obiee-tips-and-tricks/setting-a-sort-order-column.html