Need to pull the forth record only 1

[thorny00]

Using CR v.10
Have SSN and StartDate fields. I need to pull only the 4th record when the count of records per SSN > 4.
I group on SSN, then set up a formula FOO-
count({StartDate}, {SSN})
then in the Select Expert I made FOO > 4
Example:
SSN StartDate
123456789 7/1/05
7/2/05
7/3/05
7/4/05
7/5/05

I only want to show the 7/4/05.
How is this done?
Thanks in advance for you help and time!!

 

[Naith]

I'm presuming that you don't return any SSNs with less than 4 records.

You can handle the record count by inserting a running total on FOO, and conditionally suppress the section based on the running total <> 4.

Naith

 

[thorny00]

Naith,

Correct, I don't pull any records in with a count less than 4. I should have said >=4 in the Select Expert.
I'm having a mental block - some SSN's have 5, 6, 7 or more StartDates, I only need to show the 4 StartDate. When I do what you suggested only SSN's that have a count <>4 show and it shows all the dates. What am I missing?

Thanks!!

 

[synapsevampire]

You only want the 4th row of each group, or is there only one group?

Anyway, you don't need a formula, in the Report->Selection Formulas->Group place:

count({StartDate}, {SSN}) > 4

Then in the group header place aformula such as:

whileprintingrecords;
numbervar MyCount:= 1

In the details use a formula such as:

whileprintingrecords;
numbervar MyCount:= MyCOunt+1

In the Suppress formula for the details place:
whileprintingrecords;
numbervar MyCount;
MyCount <> 4

This will only display the 4th row of each detail section for each group.

-k

 

[Naith]

What I meant by <> 4 (and what SynapseVampire has intimated to) is that you are giving your suppression criteria a boolean instruction. Crystal will always apply the suppression logic except where the record count = 4. (i.e. <> 4)

Naith

 

[synapsevampire]

Sorry, the group header formula should be:

whileprintingrecords;
numbervar MyCount:= 0

-k

 

[thorny00]

Thank you both synapsevampire and naith!
What you provide worked great.
It's people like you that make this forum so valuable
Thanks again to both of you!