Restrict records at Pivot Report level

KBabu

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		IL	MA	MN	MI
	Total Rev
ABC	$100		40	40	20
XYZ	$90	20	20		40
PQR	$80	10	40	30

.

.

.

.

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.

Joel

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

Martin van Donselaar

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

KBabu

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.