Top N Report For a Date Range

[BeckyMN]

I have a call center database. There are 2 tables: CALLS & PRODUCTS. Each call that comes into the center gets a new call record. Each CALL record is related to a PRODUCT record. Each PRODUCT can be related to many CALLS but each CALL can only be related to one PRODUCT.

I have been asked to produce a report that shows the top [variable] PRODUCTS that have had the most calls occuring from [variable] thru [variable].

(the user will enter the variables when prompted)

For example; the user would want to see the 5 products that had the greatest number of calls between January 1st and March 31st this year.

I'm having trouble with the "Top N" portion of the task. Using a summary field I can get a count of the products (I'm running this from the CALLS table) but I can't get it ordered properly nor can I exclude the "bottom N"

I have a feeling that I am coming at this completely from the wrong direction. All help is appreciated.

-Becky

[comment]

A rough sketch:

In Products, 3 global fields: gStartDate, gEndDate, gN.

A new TO of Calls, named CallsInRange.

Relationship:

Products::ProductID = CallsInRange::ProductID

AND

Products::gStartDate ≤ CallsInRange::Date

AND

Products::gEndDate ≥ CallsInRange::Date

In Products, a calculation field, cCountInRange (result is number) =

Count ( CallsInRange::CallID )

Script:

Go to Layout [ "Report" (Products) ]

Show All Records

Sort Records [ Specified Sort Order: Products::cCountInRange ; descending ]

Go to Record/Request/Page [ Products::gN + 1 ]

Omit Multiple Records [ Get ( FoundCount ) - Products::gN ]

Enter Preview Mode [ Pause ]

Enter Browse Mode Go to Layout [ original layout ]

Show All Records

This needs a bit more work to handle a tie for the last place.

[sbg2]

I was working on an example while Comment was making a reply. His way of limiting the found set with:

Go to Record/Request/Page [ Products::gN + 1 ]

Omit Multiple Records [ Get ( FoundCount ) - Products::gN ]

is much more efficient than the way I was planning. I used a Go To Field script step as the first step, otherwise If N was the last field I was in then a change to N did not seem to always take.

ProdsCalls.zip