=upper

[maeling]

I have a field on my report called revision. This returns the revision number for a product. If I have two revisions of a product I get two products on my report. I would like only the product with the latest revision to be displayed. I.E the highest number.

 

[dgillz]

Please clarify: are there 2 values in you field called revision, or do you actually have 2 database records?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[maeling]

Hi there,
I have two database records.

Product Revision
R600 1
R600 2

 

[dgillz]

Group you report by Product, and sort your report do that the desired record show up last. Then drag or copy the desired fields to the group footer, and hide the details section.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[maeling]

Thanks but I was hoping for an SQL solution as I need to use the groupings elsewhere on the report.

 

[mbarron]

Another opiont would be it use conditional suppression.

You can suppress the detail band by using (Format/Section.. choose the detail band and click the (x-2)) button for Suppress:

{revision.field}<> maximum({revision.field},{group field})

The drawback is if you are doing summary calculations on a field in the band that is being suppress. These fields will still be calculated.

Mike

 

[synapsevampire]

Group by product, and then in the report->Edit record selection->Group place something like:

{table.Revision} = max({table.Revision}, {table.product})

This will pass the group by SQL if these really are the only rows in the report and you have the Perform Grouping on Server option enabled.

In either case, it should perform more quickly than suppression.

-k

 

[lbass]

All the above solutions would work and are the ones I would typically use, but I have also successfully tried using the SQL statement based on postings by lyanch, who has often presented a solution for using the SQL to select records with the maximum or minimum. One thread where she provides one for a minimum is thread767-566752 or you could search the threads she has participated in for more examples. I think that your ability to do this might be version-dependent though--I'm not sure, but it might not be possible in versions lower than 8.0.

-LB