Wednesday, October 23, 2013

OBIEE - Grouping measures and counting group members

Few days ago I was asked for something like this: "We have a table of Revenue for each Product. How can the end user assign the values of Revenue into groups he invents and then count the number of Products in each group." 
It seems easy but, try it in SQL. It's not trivial at all. 

For some reason I had a blackout. Since I reinvent the answer to similar questions for the third time at least; It's time to write about it here. The process actually takes about 3 minutes, but it will be a bit long post, since it's a very detailed explanation.

We start with the basic analysis: Product and the Revenue (twice). One Revenue column to work with and the other for QA.
 Next edit one of the Revenues formula and select "Bins". Here we can easily create groups, we could otherwise create by a big manual case statement.
I created a bin named "1. Under 100K" with condition of Revenue<100000.
It's important to uncheck the "Treat result as numeric value ..." since the result is textual.

otherwise you will get the following error:
I created few bins and then created the last one by marking "create a bin of all other values".
Since I want to use the result of the bins as attribute later (count the number of Products in each bin), I'll check the "Treat as an attribute column",at the bottom of the function screen.
I named the column "Bins".
In "results"Tab, we have a result table, I'll delete it and create a Pivot table.
Now it's time to edit the pivot:
I will switch the Products column and the Bins column in the Pivot Table:
 
First I'll move the Bins column to the left axis of the pivot:
Next move the Products column to the Measures section of the Pivot:
Of course the Product Names can't be aggregated and are left blank in the Result.
So it's time to click the Products column and select "Aggregation Rule" and "Count":
Now it's perfect.
If you prefer you can exclude the regular Revenue column or add a Chart:
This is the result:

5 comments:

  1. Hi Boris,

    Does this work on OBIEE 11.1.1.7.0 ? Once i set, "Treat as an attribute" for the column Bin and change the view to Pivot, i am getting the error [nQSError 42039] Columns in BY clause of REPORT totalling function must be in select list.

    Any pointers on how this can be fixed?

    Divya

    ReplyDelete
  2. Divya, it works on my 11.1.1.7.141014. That's not the issue. If you google on the issue there is a case in Oracle Comunity with same error caused by excluding a column from the pivot. That's one option.

    Regards,
    Boris

    ReplyDelete
  3. Woo hoo! You solved my problem. I had the BINS but could not figure out the rest. Thank you, thank you, thank you!

    ReplyDelete
  4. This is really useful and interesting, thanks!

    ReplyDelete
  5. Can we create a grandTotal for this report without clicking on summation symobol.

    ReplyDelete