Selecting Top Record?

[jedooley]

I built an Inventory Report within Crystal. It basically gives me the Inventory at each location.

I want to limit it so it only brings in the top inventory record, because Inventory is not updated every day. I just want the most current record to pull in.

Can someone tell me how I can accomplish this in Crystal Reports 9.0?

My fields are

Product
Location
Date
Volume

[brucevde]

Group by Location.
Sort by Inventory Date
Suppress the Details section
Add database fields to the Location's Group Footer.

Anything displayed in the Group Footer will be from the last record within the Group.

[jedooley]

I don't that will work because Each Product can be found across different locations.
The results I get from this report only show my 1 location per product if we do it the way you suggested.

[brucevde]

Try
Group by Location.
Group by Product
Sort by Inventory Date
Suppress the Details section
Add database fields to the Product's Group Footer.

Or

Group by Product
Group by Location.
Sort by Inventory Date
Suppress the Details section
Add database fields to the Location's Group Footer.

The point is get the records sorted in the order you want them and then just display the last record.

Also check out Crystals NthLargest, NthSmallest and NthMostFrequent functions.

[jedooley]

Ok, If I do it that way it break it out all right but it is just totalling the Volume for all the entries recorded for that location which is not what I want. I want it to just give me the most current Inventory dates volume in the system for a location.

I am managing a pool of oil product basically.

thanks for your help

so basically

Toronto product a 6/4/06 45.6
Toronto product b 6/7/06 55.6
Edmonton product a 6/8/06 67.9
Winnipeg product b 6/7/06 98.5
Denver product c 6/2/06 45.8

[brucevde]

but it is just totalling the Volume for all the entries

???? I am not sure I understand. Did you add a Summary for the Volume field? Why would the report total anything?

Since I don't know your database or report layout it is hard to offer any more advice.

[jedooley]

Here is the way my report looks.

I just want to filter the date to only show the most current Inventory date per product. I think this will help. thanks for helping.

I know the the slect expert allows you to specify date ranges, periods etc. But I don't want any duplicate records.

SEE ATTACHMENT