Selecting top 10 counts from a graph

Question

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.

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.

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

Sherry George · Answer

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.

3427784 · Answer

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.

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

JustTheFacts · Answer

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)

Hope that helps.