How can I filter a report with duplicate fields in related records?

Question

I have a report where I need to filter out records where there is a duplicate contract number within the same station but a different date. It is not considered a duplicate value becuase of the different date. I then need to summarize the costs and count the contracts but even if i suppress the "duplicate fields" it will summarize the value. I want to select the record with the most current date.

Station Trans-DT  Cost    Contract-No
   8    5/11/2010  10         5008
   8    5/12/2010  15         5008
   9    5/11/2010  12         5012
   9    5/15/2010  50         5012

Answer 1

1. Create a group on Contract-No.
2. Create a formula field to display most recent Trans-DT.
   Something like: Maximum ({Trans-DT}, {Command.Contract-No})
3. Create your summary fields or running totals based on the newly created Contract-No group.

Edit:
To summarize costs and count contracts, you'll need a bit of trickery.

Add this (in a formula field) to the report header section:

// start the sum
// put in report header
WhilePrintingRecords;
Global NumberVar TotalCost := 0;

This goes in the report footer:

// final count
// put in report footer
WhilePrintingRecords;
Global NumberVar TotalCost;
TotalCost;

And place this in a formula field within your Contract-No or Station group:

WhilePrintingRecords;
Global NumberVar TotalCost;
if {Command.Trans-DT} = maximum({Command.Trans-DT}, {Command.Contract-No}) then
    TotalCost := TotalCost + {Command.Cost}
else
    TotalCost;

I'll leave the counting part to you. Good luck!