Forum Stats

• 3,875,994 Users
• 2,267,015 Discussions

Discussions

Top N and Bottom N reports

Member Posts: 37
edited Sep 8, 2009 12:16AM
Hi

I need to create two reports 'Top 10 customers' and 'Bottom 10 customers' based on a measure 'Utilisation'
say..
Cust Number Cust Name Utilisation

My data is such that I have 422 records, of which 419 have a utilisation of 50% and 3 records have 0%.

I created a new column in Answers with the formula : rank(Utilisation.Utilisation desc) and filtered it saying 'Rank is less than or equal to 10'...

So what happens is the 419 records with 50% utilisation get rank 1 and the remaining 3 records get 420, 421 and 422. This happens irrespective
of whether I put rank(Utilisation.Utilisation desc) or rank(Utilisation.Utilisation asc).

So to get the Top 10 customers 'Rank is less than or equal to 10' condition gets me all 419 records when I need only 10 records. This obviously happens because
all the 419 records have rank 1. Is there a way I can restrict the records. Say if I have

Customer Utilisation
A 10
B 20
C 30
D 40
E 40
F 40
G 40
and I want to see Top 4, I should see only A, B,C and D. Maybe if the Utilisation is equal order by Name in ascending.

Also, can anyone help me on how to get the Bottom 10 customers.

Thanks a lot.
Tagged:

• Member Posts: 2,432
There is a very simple way to to this with a Table without using the Rank function at all. Simply do this: in the Properties of Table View, restrict the number of visible rows to 10. Then, in the same window, click on the "enable column sorting" checkbox. Set the sorting on the measure column to descending.

Go to your dashboard. When the report comes up, it is already showing the top 10. Now click in the header of the measure to switch the sorting. Voila, because the table only show 10 rows, you instantly have the bottom 10. The beauty of this also is that if the end user wants to see everything after all this, he/she can simply click the button at the bottom to show all records.
• Member Posts: 134
Hi,

Add one more column to report with column formula as RSUM(1) and add filter on this column some think like RSUM(1) <=10 , rename column name rsum(1) to Sequence NO or Rank.

--Srinivas Malyala
• Member Posts: 37
Hi David

Thanks for the suggestion. The thing is, the user wants to see only the Top/Bottom 10 customers.
Is it possible to restrict the number of rows displayed to 10 and not give him the option of seeing all the records.

Thanks.
• Member Posts: 37
Hi Srinivas

I'll try this out. Do you mean for the bottom 10 customers report, I can put the Utilisation in ascending order and use RSUM and For the Top 10
put the Utilisation in descending order and then use RSUM ?

Thanks a lot.
• Member Posts: 134
yes,

--Thanks
Srinivas
• Member Posts: 1,586
Hi,

All the suggestion above are right,seems your new so i would give you an example given by expert,for that go through this link

[http://obiee101.blogspot.com/search/label/TOPN]

Hope it helps you.

Best Wishes,
Kranthi.
• Member Posts: 37
Hi Kranthi

Thanks for the link. I actually went through the page before posting, but somehow the rank concept did not quite fit in
with the kind of data I had (419 having one rank and 3 having different ranks).

Thanks
This discussion has been closed.