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

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - 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 ?.

Advertising

3 - 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.

Advertising

4 - The solution

I know only one solution which is to :