Top N with no Summary?

[TerrieS]

I want to show the Top 20 Airfare flight costs for a given date range.

Unfortunately, there is no "summary" needed as I just want the Top N (20) flight detail lines.

Here is a sample of how it should look:

Smith, John $5,600 1/5/2010
Smith, John $4,300 2/1/2010
Andrews, Tom $3,700 1/29/2010
Evans, Chris 2,300 2/20/2010
Andrews, Tom 1,900 2/28/2010
Andrews, Tom 1,900 2/28/2010
Evans, Chris 2,300 2/20/2010

Unfortunately, if I do create a group (which I supress from printing) based on an "almost" unique formula per each flight ( Date, Amount, Employee and put a sum on the flight cost - don't you know it, but sometimes, the employees do have more than one flight in a day for the same cost and it shows as follows:

Smith, John $5,600 1/5/2010
Smith, John $4,300 2/1/2010
Andrews, Tom 1,900 2/28/2010
Andrews, Tom 1,900 2/28/2010
Andrews, Tom $3,700 1/29/2010
Evans, Christ $2,300 2/20/2010


Any thoughts on this?

Thanks in advance for your time.

 

[TerrieS]

Sorry, please ignore the second and last line
Evans, Chris $2,300
on the "how it should look" : )

 

[lbass]

Shouldn't it be:

Smith, John $5,600 1/5/2010
Smith, John $4,300 2/1/2010
Andrews, Tom $3,700 1/29/2010
Evans, Chris 2,300 2/20/2010
Evans, Chris 2,300 2/20/2010
Andrews, Tom 1,900 2/28/2010
Andrews, Tom 1,900 2/28/2010

What is the formula you are grouping on? Don't you have a flight number or ID? You should be able to insert a maximum at the flight number group level and then do a group sort on that->descending.

-LB

 

[TerrieS]

Yes, you are correct in "what is should look like" and I had added that in an addendum reply to my own posting. This is from a T&E accounting extract and I don't think it pushes the flight number out as the General Ledger doesn't need that much detail, but I will look for another identifier.

It seems so simple a request that I felt like I must be missing something...

Thanks.

 

[lbass]

There must be something that makes the flight unique--destination, for example. You didn't provide the formula you are currently using for grouping, but if you concatenate one more field that is unique, you can then follow the steps I outlined earlier.

-LB

 

[chelseatech]

If the records are in the details section, then why not just sort it into the sequence you want and then use section suppress to skip everyone else out.

Formula in Group /Report Header:
WhilePrintingRecords; Global Numbervar LineNo ;
if not InRepeatedGroupHeader then LineNo:= 0;

Formula in Details:
WhilePrintingRecords; Global Numbervar LineNo := LineNo + 1;

Section Suppress:
WhilePrintingRecords; Global Numbervar LineNo; LineNo > 20

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

Chelseatech is right. But you could do it even more simply by just suppressing with:

recordnumber > 20

-LB

 

[TerrieS]

Perfect and simple. Thank you!

 

[TerrieS]

Okay, just a head's up. I did add a grouping as the request was modified to be the Top 20 Flights for each "Department". Unfortunately, the recordnumber solution suppressed after 20 detail lines period. So, I used Chelseatech's formula, with an additional "ResetCounter" formula added (to reset counter [LineNo] to zero) in the group header. Everything works perfect now.

 

[lbass]

You also could have just inserted a running total that evaluated for each record and reset on change of group: department, and then just referenced the #rt:

{#rt} > 20

-LB