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.


4 comments:

  1. Well written. Very useful.

    ReplyDelete
  2. Thanks for the post. Helped me out and also confirmed just how tricky it is to do basic things like sorting in OBI. Such a pain

    ReplyDelete
    Replies
    1. Jacob, it's a matter of perspective. If you think of Pivot with "graph only" option, as your graph building tool, you have a very powerful tool with all the sorting options you need.

      Delete
    2. Sorting is working in the pivot but when I am trying to place a table prompt its not working . Any solution for this ?

      Delete