Crystal Reports group sorting

Question

I have flight data records for each user. The table below shows an example for one user id. I need to group by user_id (show only one row for each user_id) that has the latest flight_date.

When I added grouping by user_id, the result report displays the right flight date, but the rest of the fields come from the last row for each user (highlighted fields).How do I make it show the entire row with the latest date (4th row in my example)? Thank you!

Answer 1

If you only want to see the most recent record, per userID, based on the flightdate, there are a few options. The most efficient way is to use a custom SQL command, but Crystal can do it by retrieving all of the records, and then suppressing the records you don't want to see.

1.Group the report by USERID (Report > Group Expert > Add the USERID field to the group section)

2. Sort the records by FlightDate ( Report > Sort Expert > Add FlightDate and select Descending NOTE: if FlightDate is not a date, we will need to create a formula to convert it into a date first, otherwise Crystal will treat it as a string and 12/2015 will be higher than 1/2016 (for example)

3.Create a running total to count the number of records within each group (This is how we will tell Crystal to suppress the records we don't want) --Right click "running Total" on the right hand side, select New. Name it whatever you want; under Field To Summarize add FlightDate, change summary to COUNT Leave Evaluate as "For Each Record" Under Reset, select On Change of Group and select the group for USERID

The report should look like this

4. In the Section Expert, select Details and click the formula button next to "Suppress" Enter the formula {#RecountCount} > 1 (Or whatever name your running total has)

Now you should only see one record for each user