I have a line items file containing products. Each product is in a single category.

I have a report which shows how many of each product is in each category. The report is sorted, descending, by the number of products.

Some categories have around 100 products.

Is it possible to get a top ten list of products in each category?

The only way I can think of to do this is to export the report to a different file and loop through the products in each category using a variable 'counter'. Is there a better way?

Thanks,

ft.

Determine your found set and sort it (like you have done already).

Enter Find Mode

(insert your search criteria here that you already have)

Perform Find [Replace]

Sort Records [no dialog]

# store and omit the top ten categories from the found set

Go to first record/request

Set Variable [$topten ; category ; 1 ]

Enter Find Mode

Set Field [category ; $topten[1] ]

omit record/request

Perform Find [Constrain]

Sort Records [No Dialog]

Go to first record/request

Set Variable [$topten ; category ; 2 ]

Enter Find Mode

Set Field [category ; $topten[2] ]

omit record/request

Perform Find [Constrain]

Sort Records [No Dialog]

Go to first record/request

Set Variable [$topten ; category ; 3 ]

Enter Find Mode

Set Field [ category ; $topten[3] ]

omit record/request

Perform Find [Constrain]

Sort Records [No Dialog]

(REPEAT THE ABOVE UNTIL YOU HAVE STORED AND OMITTED THE TOP 10 CATEGORIES)

Enter Find Mode

Set Field[ category ; $topten[1] ]

New Record/Request

Set Field[ category ; $topten[2] ]

New Record/Request

Set Field[ category ; $topten[3] ]

New Record/Request

Set Field[ category ; $topten[4] ]

New Record/Request

Set Field[ category ; $topten[5] ]

New Record/Request

Set Field[ category ; $topten[6] ]

New Record/Request

Set Field[ category ; $topten[7] ]

New Record/Request

Set Field[ category ; $topten[8] ]

New Record/Request

Set Field[ category ; $topten[9] ]

New Record/Request

Set Field[ category ; $topten[10] ]

Perform Find [Constrain]

Sort Records [No Dialog]

You may have to add additional search criteria to the above find batch in order for you to get the proper record set, but this should get you what you want fairly quickly so long as none of your fields are unstored calculations or the search does not look into data stored in a separate related table.

You could also try to use a 10 row portal in your report that is sorted in Descending order based on your total field.

Many thanks for the great suggestions.

I have found that the initial find, sort, preview takes quite some time, so I have decided to index the resulting report into a different table for quick review. I will then use 10-row portals to display the top-10 products in each category.

This approach is a little lame in that the indexed top-10 can lag behind the true data (and also that the export of summary data has to be to a file - ug!). So I will put in a custom dialog to remind a user that the top-10 data was last compiled on date xyz and give them the opportunity to recompile if they want.

Thanks again.

ft.

Are you familiar with this technique:

http://www.onegasoft.com/tools/fastsummaries/index.shtml