Nth Most Recent by Date 1

[WestView]

Hello All,

I’ve been searching this site for help on how best to do this but haven’t found an applicable solution so let me just put my issue out there.

I am working with a publication database that tracks client’s subscriptions to various products. I need to create a report that returns Nth number of records based on a product and a date. For example, this report needs to return say, the 200 most recently expired clients (based on their publication’s expire date).

I created two parameter fields, one for the publication/product ({?Product_Code}) and one for the number of records returned ({?NoMostRecentlyExpired}) where the user enters the product code and the number of records they wanted returned. Then, using the "Format Formula" (x+2) from the "Format Section", I put in the following condition: RecordNumber >= {?NoMostRecentlyExpired}.

This works fine - with only one product code. However, I have now been directed to expand this report to allow the {?Product_Code} parameter field to include multiple publications. So, if the user now chooses product codes “101” and “102” my solution no longer works because it only pulls the first 200 records of the first criteria in the parameter (product code “101”).

I need this report to now pull the 200 most recently expired “101’s” and the 200 most recently expired “102’s” (400 records total).

How do I do this!!!

Any/all help suggestions would be greatly appreciated!!!!

Thank,

- Tom

 

[lbass]

Create a running total {#cntwingrp} using the expert, where you choose a recurring field, count, evaluate for each record, reset on change of group. Then in the section expert->details->suppress->x+2, enter:

{#cntwingrp} > {?NoMostRecentlyExpired}

-LB

 

[WestView]

LB,

That's it!

Once again sir, I am in your debt.

Thanks VERY much!!!

- Tom