Return top 10 rows in a table

[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!

 

[MisterMo]

Hi,

One way to takle this one is to use a stored procedure and do most of the logic in there.

do you have rights to write stored procs?


-Mo

 

[Madawc]

If you already have the incidents in the proper order - which is what your data looks like - then all you need to do is suppress every line after the 10th, for each location.

Create a running total, reset for each location, counting one for each incident.

Right-click on the section and choose 'Section Expert'. Choose the formula for suppression (the X and pencil icon). Set it to suppress for running total values above 10.

This is less efficient that doing it via a stored procedure, which makes the server do the work. But if run-time is not enormous, it does work OK.

It helps if you give your Crystal version, 8 or 8.5 or 9 or whatever. Suppression works from at least 8.5, but Stored Procedures have varied a lot.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Are the incident counts summaries or database fields? If they are summaries, you could use group sort/topN. If they are database fields, you can use suppression as Madawc suggested.

If you only want to return the top10 to the report (without suppression or using group sort), how you write the query depends upon your datasource.

-LB

 

[TimBiesiek]

Oh, bummer, sorry! Just realised I posted this in the wrong forum! Was meant to be a Business Objects issue, not Crystal Reports!

Sorry!