OBIEE - How to sort on a measure column of a pivot view ?

The problem

In a pivot view, you don't have any options to choose the sort order on any measure columns (column grand total include).

If you search to sort on the column grand total, see this article : OBIEE - How to sort on the column grand total value of a pivot view ?.

Articles Related

How the pivot view works ?

The steps that perform OBIEE when it create an pivot view are :

• to get the data from the database
• to sort them with the specified order of the criteria tab
• and then construct the pivot view.

Unfortunately, a pivot view is always sort by dimension.

Then you can in the pivot view choose :

• an order in the tab criteria
• or an ascendant or an descendant order

but the first order of the lowest dimension will always repeat.

In our above example, I have sort by year and by value of direct sales but I will get always this sort :

• by year
• and for the promotion by :
  • NO PROMOTION
  • TV
  • INTERNET
  • POST

The promotion sort is chosen during the first pass of the data in the table view.

The solution

I know only one solution which is to :

• Create a pivot view in the table view : OBIEE - How to perform a pivot in a table view
• and then choose the sort order that you want