Top N Reporting

[dsallow]

I am wanting to create a report where I break my data down by number of incidents within a group.  I then only want the top 10 groups to be displayed on a graph.  I am using Report Studio 8.4.  What am I missing in doing a top n report.

[rockytopmark]

We don't know what you have tried, so help us understand your situation better. That said, here are some Basics:

• If you are using a Dimension source (DMR, Cube, etc) TopCount() function will get you there.  (TopCount Ranks and orders all at once)
  
• If relational source, you need to use Rank() function, then filter so you only return where the Rank is <= 10.  (You may need to filter after auto-aggregation, or in a Summary Filter, or use a reference query to simulate a sub-query to get desired results

Good luck.... M

[dsallow]

Thank you so much.  I am using a relational model.  I am coming from crystal where top n existed as a reporting option so i never thought of the rank().  I am going to try that., ;D

[blom0344]

It may be trickier that the examples given. If your resultset is based on an aggregate, then you will find that rank() is established prior to aggregation and the values are pretty meaningless (since they do not apply to the ultimate grouped rows)

In SQL terms this would mean having an inline view that stores the outcome of the aggregated set and a second select to apply the ranking to the individual groups..