Restrict records at Pivot Report level

Question

Hi All

I got a pivot table where Revenue is summed up (i.e. totals) for each state. To keep it simple, the Pivot table shows for each employee Total revenue across states in US (East/Central)

The challenge here is after making the Totals, i can't do TOP 10

Employee
 
ILMAMNMITotal RevABC$100404020XYZ$90202040PQR$80104030

.

goes on

The above is Pivotal view and the requirement is i want to do TOP 10 on Total Revenue column. Plz note Total Revenue column is SUM which generated in Pivot

I got the below ideas but not that helpful or got struck

Option1:

--> Pivot table properties --> Data viewing --> paging ctrls to "Top" and entering "Rows per page" = 10 which works well but we all know it displays first 10 rows if we do like this, still we can see other records also

Option2:

if i want to display only top 10. I can keep descending order on "Total revenue" and it will fetch 1000's records. In Oracle DB we can restrict records using "row_num" by writing a query. can you plz help where i can keep this "row_num<10" in my pivot report ?

or if i am going anywhere wrong, any suggestions, please provide your thoughts/fixes.

I am using 12.2.2 version of OBIEE.

KBabu · Answer

Thank you Joel and Martin

I got the Top 10 records as per your advise. However i don't know we can write SUM(query count) BY another attribute untill i saw your screenshots.

Martin van Donselaar · Answer

The solution Joel is suggesting should help. You don't need to calculate rank however, just order by total column in your analysis.

Joel Acha · Answer

You'll need to do something like this although I didn't quite get the rank ordering to work but this should help: