Sunday, March 24, 2013

OBIEE - Top 2 of top 10 with Selection Steps

In the past I talked about Hierarchical Columns and selection steps. 

Lets talk about additional sample of using Selection Steps. This time I don't use Hierarchical Column, just to show, we don't need to.

What I want is a report of Top 2 Offices with top 10 Products.
Lets start with a simple report of Department, Product and Revenue.

The result isn't exactly what I wanted, since the same products are sold by different  Departments.  
I want top 10 unique Products.
So lets create a column that gives a unique sum for each product ( SUM(Revenue by Product) ) and run the in Top 10 on it.
Now we have a list of 92 row, since they are 10 Departments.
Now I want the top 2 departments by its revenue from the 10 top products.
I could create another column and filter on it, but it's easier to use Selection Steps:

Lets concentrate on Offices - Department, and create a new condition.
Select the Top.. based on... condition
And select the "Keep Only", "is top", 2.

We can select the Measure (Revenue for our needs)
And run it for all members of the Products.

As a result we have the top 2 Departments that sell the top 10 Products:

You don't believe me?
Lets remove the Selection Step, and summarize by Department:
The result proves to be correct. Q.E.D.

No comments:

Post a Comment