## Sunday, December 1, 2013

### OBIEE - Sorting Graphs / Charts

Lets talk about sorting graphs in OBIEE. I will cover 3 options:

1. Basic sorting.
2. Sorting using Pivot.
3. Sorting by the Bar total.

#### 1. Basic Sorting

I created a simple analysis, with the Revenue sorted descending.

The Table is sorted by Revenue but the default bar-graph is not. It's sorted by the horizontal axis. In this case, "Product Type".

Apparently, it's because of the "Per Name Year" column, that is in the "Vary Color by Horizontal Axis" Section above the Measures.

Once I Exclude it, the sorting seems fine.

When I place it ("Per Name Year") in the "Group by" part,  the sort is broken by it. That is an expected behavior.
Unfortunately, placing the "Per Name Year" in Graph Prompts or Section, spoils our sort again:

What can I do to have a complete control?
To use Pivot with Graph and select "Graph Only" in the presentation Position.

#### 2. Sorting using Pivot

Now any sort I do in the pivot, is reflected in the Graph. I can hide the Pivot Table and show "Graph Only":

For the previous example, when I sort the pivot by Revenue, it's reflected in the graph:

#### 3. Sorting by the Bar total.

Unfortunately, even when working with pivot, the default options are not always enough. Look at the example bellow. I sorted by Revenue of Year 2010, but what I really wanted is sorting by the total Revenue of all 3 years.

For this I need a little extra work.
I created an extra column with Formula Sum("Revenue" by "Product Type"). That's actually the bar total.

I'll sort the Analysis by this Sum:

In the Pivot I moved the sum from Measures to the leftmost column of the Rows axis. And... Nothing!

Why? Because I should have marked it "Attribute column" in the Formula:

Now I'll just hide this Sum column it in the Column Properties (otherwise we will see the values in the Vertical axis):

This is the desired result.