Crystal Reports group sorting

crystal-reportsgroupingsorting

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!

Best Answer

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

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

Related Solutions

R – Crystal Reports group name error

I don't have CR in front of me, but there is formatting on the group that is causing this. Something like show by week instead of the actual date. I've had this bite me numerous times. I can be more specific when I get to work tomorrow.

Edited to add: On the insert group dialog, you need to change:

"The section will be printed: for each week" to "for each day".

Javascript – Sorting an array of objects by property values

Sort homes by price in ascending order:

homes.sort(function(a, b) {
    return parseFloat(a.price) - parseFloat(b.price);
});

Or after ES6 version:

homes.sort((a, b) => parseFloat(a.price) - parseFloat(b.price));

Some documentation can be found here.

For descending order, you may use

homes.sort((a, b) => parseFloat(b.price) - parseFloat(a.price));

Best Answer

Related Solutions

R – Crystal Reports group name error

Javascript – Sorting an array of objects by property values

Related Topic