Sorting by Count in Crosstab 1

[Abner2]

I have a cross tab report listing crime types. I would like to sort this report in descending order by the count of crimes - highest type of crime type to lowest. I only seem to be able to sort in alphabetical order. For example:

Burglary 45
Assault 40
Larceny 20 etc.

Secondly what formula do you do in order to show only categories with more that say 10 occurances?

Thanks

 

[lbass]

Are you showing us the entire report structure? Your current display does not indicate the need for a crosstab, although you may have your reasons, e.g., where you want to display the information. If you really need the crosstab, you can use specified order to reorder the rows, but this is essentially a manual manipulation of row order. You need to do this within the crosstab by selecting the row field, choosing group options, and then specified order.

Another approach would be to eliminate the crosstab, and just use the group header or footer section. If you really have only one column, and assuming that you have grouped on {crime.type} and that the number of crimes is a summary, e.g., count({crime.incident},{crime.type}), you can use TopN to order your groups in descending order.

To display only those groups with at least 10 instances, go to report->edit selection formula->group and enter:

count({crime.incident},{crime.type}) >= 10

If you have other columns in a crosstab, for example, say your rows are crimes, and your columns are cities, you couldn't really sort by crime count, since it would differ by cities.

If you just want the look of a crosstab, you can use borders or boxes to attain the same effect.

-LB

 

[kenhamady]

You want to do a TopN in the cross-tab, which is easy once you know how. For some reason CD made this feature less than intuitive.

First, select the cross-tab by clicking in the upper left (empty) corner of the cross-tab. Now go into the report menu and select the TopN (or Group Sorting in v9) feature. Instead of the REPORT TopN you wil get the Cross-tab TopN feature.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[danausten]

Ken,

How could I get your solution to work in the report footer? I've tried making a fake group expecting that I would be then able to sort the group but this didn't work as the Group Sort Expert is disabled. I'm using CR10

Thanks

 

[kenhamady]

danausten,

My solution works the same in the RF as in the RH, but my solution doesn't involve a fake group. If you are trying to rank the rows of the cross-tab you don't need a fake group.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com