Sorted data - now only want to show top 2 records for a field 2

[urbanhim]

I have a report which has the following informtion:

ACCOUNT_ID ACTION USER ID ADDRESS ID MOD.DATE CRE.DATE
AB2131 Insert SA1 12 18-Mar-08 18-Mar-08
AB2131 Insert SA2 12 18-Mar-08 18-Mar-08
AB2131 Insert SA3 12 18-Mar-08 18-Mar-08
CD4343 Insert SB1 12 18-Mar-08 18-Mar-08
CD4343 Insert SB1 12 18-Mar-08 18-Mar-08
CD4343 Insert SB2 12 18-Mar-08 18-Mar-08
CD4343 Insert SAF 12 18-Mar-08 18-Mar-08
DF5656 Insert SAF 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08


I have sorted all my records in the order I want them, but I only want to show the first two fields of each ACCOUNT_ID
and only show those that have more than one record.

I need to add a formula field which will count the records based on ACCOUNT_ID - Then I need to select expert on this count, showing where the count >1 but <2

The above data when i'm finished would look like this...

ACCOUNT_ID ACTION USER ID ADDRESS ID MOD.DATE CRE.DATE
AB2131 Insert SA1 12 18-Mar-08 18-Mar-08
AB2131 Insert SA2 12 18-Mar-08 18-Mar-08
CD4343 Insert SB1 12 18-Mar-08 18-Mar-08
CD4343 Insert SB1 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08
FG7878 Insert SAF 12 18-Mar-08 18-Mar-08

Anyone know how I can do this?

Thanks




UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[spcc07]

To Find a Top N/Sort
1. Select the field you want to Top N/Sort
2. Select Report ?Top N/Sort Group Expert
3. Select Top N, Bottom N from the drop down menu on the left.
4. Select summary field for group selection, value for N, and Others options
5. Click OK

 

[lbass]

You could do this in two steps. First insert a group on acct ID and then go to report->selection formula->group and enter:

count({table.accountID},{table.accountID}) > 1

Then create a running total {#cntwingrp} that does a count of account ID, evaluate for each record, reset on change of group acct ID.

Then go into the section expert->details->suppress->x+2 and enter:

{#cntwingrp} > 2

You would suppress the group header and footer, and display only the detail section, which should also be formatted to "suppress blank section".

-LB

 

[urbanhim]

Thank you both for your replies, very much appreciated.

I've never used the Group Sort Expert, its really useful, thanks for your suggestion - although in this instance I cant get it to work because I already have a threeway sort order to get the fields in the order I need them.

LB, your two step approach in this instance works perfectly though, so this is what i've implemented in my report.

Thanks once again






UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]