Show Second to Last Record per Group

[ternas1]

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.

 

[hilfy]

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
previous({table.service_day})
else
{table.service_day}

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.

-Dell

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

 

[lbass]

Or see: thread767-1666313. Is this a homework question or something? Seems pretty coincidental.

-LB

 

[ternas1]

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

If previous({ENCOUNTER.ENCOUNTER ID}) = {ENCOUNTER.ENCOUNTER ID} then previous({CHARGE DETAIL.SERVICE DAY})
else {CHARGE DETAIL.SERVICE DAY}

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

 

[lbass]

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.

-LB

 

[ternas1]

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

 

[lbass]

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.

-LB