Selecting the proper record

[DBAMJA]

I am working on a report to get a list of addresses from an SQL database. The problem that I am having is that a patient may have multiple addresses in the address table but I need to be able to pick the last one based on a value of 1, 2, 3 ... in the field ADDSEQ. I have the rest of the report working fine except for have multiple records for those patients with mult. addresses. Is there anything in CR9.0 to help with this.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[lbass]

Assuming the highest value of {table.ADDSEQ} equals the "last" address, then if you group on {table.patientID}, you can go to report->edit selection formula->GROUP and add:

{table.ADDSEQ} = maximum({table.ADDSEQ}, {table.patientID})

This should only return one record per patient which contains the last address.

-LB

 

[FVTrainer]

LB's answer should work for you, but just so you don't get any unintended consequences, keep in mind that you're not really filtering the dataset when you use the Group Selection formula; you're suppressing the display of the records that don't meet the criteria of the group formula. (Crystal is essentially putting a HAVING clause into affect once the data has been returned to the report). The extra records are really still in the report. So, the consequence of using the group selection formula is that if you need to do a subtotal or summary at the PatientID (or report level), the suppressed records (i.e., the ones not meeting the group record criteria) will still be included in the summary operation. To actually get subtotals or summaries that don't include the suppressed records, you typically need to use running totals conditioned with similar logic as that used in the group selection formula.

 

[lbass]

I have to differ a little here.

It's true that you need to use running totals when using a group select and I didn't add that usual caution since it appeared that the goal was a list of addresses, not calculations.

But, group selects do not behave in the same way as suppression--I think it is better to think of group selected records as "lifted out" of the base records rather than suppressed, or to think of the base records as "underlaying" the group selected records.

Running totals of group selected records do not require that the condition used for group selection be used as a condition within the running total--the running total will only calculate based on the group selected records--but running totals will count suppressed records, so that if you are using suppression instead, you need to add the suppression criteria to the running total "evaluate based on a formula" section.

You can ordinarily use either group selection or suppression to accomplish the same results, but sometimes one or the other makes your task more complicated, e.g., while running totals are simpler with a group select, using the group tree for navigating the reports sometimes does not work as well as when using suppression.

-LB

 

[FVTrainer]

Indeed, my explanation of the logic to use with the running total was incorrect. And I suppose that since suppression has a very specific meaning within Crystal, I should have used a different term, given the behavior of running totals. Thanks for the correction.

 

[DBAMJA]

Sorry it has taken so long to get back to you. Other projects needed attention and I had to put this one aside for a bit.

I tried what you suggested and it still didn't work. I grouped on the patient id and then put the report group selection as stated above and still got the same number of records.

Any other ideas?

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[FVTrainer]

Hmmm...it should work. However, another feature of using the group selection formula is that you'll still see the full number of records initially returned listed at the bottom of the Preview window. This number represents the number of records returned prior to applying the group filter. So your report will display fewer records than is shown at the bottom of the Preview window.

However, assuming you're actually seeing the extra records in the report, and since you are running off of a SQL db, is it possible for you to create a view of the address table that only includes the "last" address? Also, it might be helpful if you provided more information about the tables involved and the joins between them.