Preventing Multiple RECORDS from showing????

[jolie555]

I have had good feedback from all my questions... hopefully someone can help me with this one.

I have a report that pulls records from our database by datestamp, which means if the record was referenced 6 times it will contain 6 datestamps and will be pulled in on my report 6 times. Is there some sort of formula or argument I can give that will only show me the most recent datestamp rather than showing all?

HELP!!! thank you!!!

Jolie

 

[lyanch]

You can add a subquery to your selection (where section) in the SQL window. It should reference the outer (main) SQL. I should preface this with.. it depends on your Crystal version. This works for 8.5. Here is an example subquery selection that I use to get the "first" and only "first" ship date:

shipment.shipment_date = (Select
Min(ship.shipment_date)
from shipment ship
Where ship.order_id =shipment.order_id)

This gets the min ship date for the order in the main Query. I just paste it into the show SQL window.


Lisa

 

[Naith]

Manually updating the SQL is something you'll have to address each time you execute the report.

If you're grouping these records, by ID, for example, and want the most recent date per ID, then go to the Report menu, select Edit Selection Formula, and Group, and enter the equivalent of:

{Date} = Maximum({Date},{ID})

which will extract the most recent date per group.

Naith

 

[lyanch]

You don't have to update the SQL each time you run the report. You just can't have parameters in the subquery. I use this in production reports that run from the server.

Lisa

 

[Naith]

You're right about the SQL impact on reports with no parameters. I'd only considered parameter driven reports.

 

[lyanch]

You can use parameters.. just not parameters in the subquery.

I use the above subquery in all my shipping reports with ship date(s) as a parameter. They work fine.

Lisa

 

[CrystalVis]

You can group by some id in the table. Then sort by the datestamp field. Place all the fields you want to display in the report footer. This will display only the latest record of the six records.

 

[jolie555]

Thank you guys very much... I am fairly new with this so I tried Naith's formula and it worked... But thanks to all!!