Return top 10 rows in a table 1

[TimBiesiek]

Hi all,

I'm trying to create a report that has a location group, and shows for each location the top 10 incident types, with the number of each incident, like below:

Location 1

Incident amount Incident
4 Vehicle Accident
2 Injury
1 Potential Injury
etc etc

I have the basic table layout, but am struggling to show just the top 10 incidents...

I've tried using the rowindex function, but this returns weird values... eg:

4 Vehicle Accident 86
2 Injury 37
1 Potential Injury 61
etc etc

Any other ways I could do this? Note that the top 10 incidents will be different for each location, and I want all locations shown on the one report...

Thanks!

 

[xlbo]

Within your slice and dice panel, there should be a ranking function (near the break / sum functions - should look like a little podium) - within this, there is an option to show the top X items within a selected object - this should do exactly what you need.

 

[skrandel]

Ranking in BO is not very useful since you have to return the entire result set in order to do it.

You're better off using derived tables in 6.5 that can use the ranking capabilities within your database.

In XIr2, you can even do this in the query panel.

Steve Krandel
Symantec

 

[TimBiesiek]

xlbo - Yip, that did it thanks! Only issue is that it returns more than 10 items if some have the same value. But then, it would need more criteria to determine which ones to leave out in that case!

Will do the job though!

Thanks!