Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Show Second to Last Record per Group

Show Second to Last Record per Group

Show Second to Last Record per Group

I am using CR 8.5 and have a report that is grouped by Patient ID and Service Day.  The data is shown on the Service Day group.  Each patient will have more than 1 record (multiple days in the hospital), and I need to retrieve the second to last day of each patient.  In the example below I need day 2 from patient #1 and day 3 from patient #2.  

Patient ID    Service Day
#1             1
#1             2
#1             3
#2             1
#2             2
#2             3
#2             4

I started with {serviceday} = maximum({serviceday}) in the Group selection, but it returned only the patient with the longest stay (i.e. had the most service dates).  

I also tried the Suppress function in Format Section, but no luck so far.  Any help is appreciated.   

RE: Show Second to Last Record per Group

Try this:  Group by patient ID, sort by service day.  Assuming that a patient can have just 1 service day, create formulas that look something like this:

If previous({table.patient_ID}) = {table.patient_ID} then

By placing this in the patient group footer, this will return the only service day if the patient has only one, otherwise it will return the next to last service day.


A computer only does what you actually told it to do - not what you thought you told it to do.

RE: Show Second to Last Record per Group

I tried the formula below as suggested, but it retuned the same values as service day.  Nothing was supressed - all fields still show.  


No, this is not homework.  I'm not very good with counter formulas.  

RE: Show Second to Last Record per Group

All you have to do is copy the formula, place it as indicated in the section expert suppression area, and replace {table.field} with any recurring (non-null) field.


RE: Show Second to Last Record per Group

If I place the formula in the suppression area I get an error message:  must be boolean.   

RE: Show Second to Last Record per Group

But it is a boolean, so please paste in the actual formula you are using. Also you would need to add the group condition (patientID) to the counts.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close