Restricting Number of Rows Returned in a Table Analysis (OBIEE)

When recently reviewing a Dashboard showing the "Top 10", was it just by luck on my first visit to the page it showed a Top 10?

Surely it can't be difficult to just get the Top 10?  Depending on the filters set sometimes it was a Top 10, sometimes a Top 7, sometimes a Top 13, so was it just by luck I got the Top 10 the first time round?  I looked into the analysis, the RANK function had been used to ordering the results in Ascending order for the column the measure was about, this was fine, rows were ranked 1,2,3...  so why did I get too many rows?

The RANK function will give a sequential number as values increase, e.g.

RANK   VALUE
----   -----
1      10
2      11
3      15
3      15

... however, once a value is repeated the rank is set to the lowest value for that set.  Hence, in this example, if the filter was set to RANK(VALUE) <= 3, four rows would be returned.  What are the alternatives?  RSUM, RCOUNT (running aggregates)?  Working through an example, first I created an answer with Product Description, Revenue, Rank, RSUM and RCOUNT.

[Edit - not shown here, but TOPN behaves in a similar way to RSUM and RCOUNT]

Rank Formula:

To manufacture the results, I have used the CAST function to change the values to integers therefore duplicating the results.

RSUM Formula:

This in theory would create a sequential number.

RCOUNT Formula:

Again, I would expect this to result in a sequential number.

The results:

In the results, at Rank 10 there are three products with the same revenue (when rounded to zero decimal places).  As a result, rows 10, 11, 12 all get a Rank of 10.  Surprisingly, they all get the same RSUM value of 10.  RCOUNT continues sequentially.  Note, this is a simplistic set of data, for the data that prompted me to write this post I wasn't seeing sequential values for RCOUNT.  If you explore the SQL issued to the Database (using Oracle 11.2.0.4 at least), SQL isn't issued to derive the RSUM and RCOUNT columns, therefore these are being built on the BI Server and not necessarily in the order of data returned by the Database.

How do we solve this?

If you simply want the result in a table, it's simple - change the number of rows to be displayed by editing the table properties.

The default is to usually show the Paging Controls below the results:

Switch this to "Hide" and change the number of rows to display:

The Results: 

So we now get 10 rows returned.

The Limitations:

So, yes, this works for a table, but not on a chart based on the same results:

As you see in the chart, the full list or products appears.

Pivot Table:

The solution however is relatively simple, by creating a Pivot Table then set the page size to the number of desired rows as shown above, then, within the Pivot Table Edit View, create a chart on the Pivot Table:

Problem Solved.