Selecting top 10 counts from a graph — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Selecting top 10 counts from a graph

Received Response
428
Views
3
Comments
3427784
3427784 Rank 4 - Community Specialist

Hello all,

I created a graph to produce some web analytics, in this case the top documents downloaded. I did a union to 2 dimensions that houses the data.

pastedImage_0.png

Now, I have a graph against this data that shows a simple bar graph In the past, BI publisher was able to pick up the top 10 counts easily.

pastedImage_1.png

The filtering options doesn't seem to work in this case and looks like I will be needing to add in a SQL statement. Would that be the right course of action and also is there a way I can aggregate the counts from both tables (as I did a union)?

Best,

Andrew

Answers

  • JustTheFacts
    JustTheFacts Rank 4 - Community Specialist

    There is a way to get a "top 10" look, though it won't actually be returning just the top 10. Users won't know the rest of the data is there behind the scenes.

    1. Sort your measure descending (in the "Result Columns" level of your union)

    2. Create a pivot table of the results; you don't need to pivot any of your dimensions, you just need the data in a pivot view

    3. Set "Rows per page" to 10 and set Paging Controls to "Hide"

    4. Create your bar chart from the pivot table

    (This was built from a union of identical sub-queries, one filtered to state=CA, the other to state=TX)

    pastedImage_0.png

    Hope that helps.

  • 3427784
    3427784 Rank 4 - Community Specialist

    Thanks so much for your help. Followed your instructions and almost there. As you stated it doesn't actually return just the top 10 as I'd like.

    pastedImage_0.png

    But this is exactly was I was looking for. My last question - is there a way to trim this down just 5-10 values vs. what is currently on the screen shot (trying to minimize the clunkiness).

    Thank you,

    Andrew

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    One other option would be to create one more report and filter all the criteria column in that report based on the union report. Then you can use the top n filter to get the top 10.