Return Top 20 records of each group 1

[BHERNANDEZ]

I have a report that lists the top items sold in each state. I want to show only the top twenty items. Some states have hundreds of items. Using CRv7 and a SQL database, I've created the report and sorted my items under each state by TopN. I have grouped my states individually and then grouped the summary of each item under group 1. I created a running total formula as so:
Top 20 Item
Summarize {item no}
Count
Evaluate For Each Record
Reset on change of group (item or state)

The first time using the RTF, I formatted my state group section and under suppress-> {top 20 item}>20. This seemed to work, but halfway through the report, it stopped returning the top 20 and printed all records. I've tried putting this formula in the item group->suppress section and it disregards my topN sort for item quantity and seems to display only the first few records sorted alphabetically. I can't seem to make this work.

Bridget

 

[Ngolem]

investigate the use TopN sorting

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Not totally clear on what you are doing, but if you have a group on state and then a group on item, and you are doing a topN on the item group, then what you want for your running total is a distinctcount (not count) of {table.item}, evaluate for each record, reset on change of group (state). Or you could do a count of {table.item}, evaluate on change of group (Item), reset on change of group (state). Either one should work.

Also, I think you would have to use the suppression formula in details and the group footer sections, if they are not already suppressed.

-LB

 

[synapsevampire]

I'll assume that when you say a SQL database, you mean a SQL Server database (SQL is a language common to many databases).

If you're able to build Views (or have them built) on the database, create a View containing a select top 20 ... for use by the report, that way you'll filter on the database and enjoy reusability.

-k

 

[BHERNANDEZ]

lb,

Thanks! Your second suggestion worked. I didn't try the first one. I was sure I had already done what you had suggested, but apparently not in that particular order.

k,

You are correct. I am working with a SQL server database. I can't build views on the database though so, I couldn't try your suggestion. THanks for the info!

Bridget