Suppress data which appears in different groups, and keep the one with the latest date

Question

My report needs to group and count a set of data, when the data appears in different groups with the same ID and TYPE but different DATE and DECISION, it requires to suppress the data that is not with the latest date and the total count should not include the suppressed data. Can you please help me with this?

Raw data

   ID              TYPE         DATE               DECISION
  1111              F           12/01/2016         Approved
  1122              E           3/02/2016          Approved
  1111              F           23/01/2016          Refused
  1133              G           3/07/2016           Refused

Before grouping, I am able to suppress the first record which is not with the latest date:

  ID              TYPE          DATE               DECISION
  1122              E           3/02/2016          Approved
  1111              F           23/01/2016          Refused
  1133              G           3/07/2016           Refused

After I group the data by DECISION:

Group 1 - Approved

   ID              TYPE         DATE               DECISION
  1111              F           12/01/2016         Approved
  1122              E           3/02/2016          Approved

Group 2 - Refused

ID              TYPE            DATE
  1111              F           23/01/2016          Refused
  1133              G           3/07/2016           Refused

Total Count of ID: 4

Expected Result:

Group 1 - Approved

ID              TYPE            DATE
  1122              E           3/02/2016

Group 2 - Refused

   ID              TYPE         DATE
  1111              F           23/01/2016
  1133              G           3/07/2016

Total Count of ID: 3

Answer 1

There are a few options - If you do not need Crystal to retrieve all of the records, the best option (from a performance standpoint) is to use a custom SQL command.

Suppress Nonadjacent Duplicates in Report

If that is not an option, or you need crystal to have all of the records (For a running total of ALL requests made in the system), you can use conditional suppression, but you won't be able to accomplish it by retaining your current grouping.

See this post for configuring a conditional detail suppression. Crystal Reports group sorting

The conditional detail suppression works by adding a number to each record within a group. You can determine which record is number "1" by using Record Sort expert, and then suppressing the details if the record number is greater than 1.

This approach won't work if you have the report grouped by decision first, because the ID is essentially a subset of that decision (thus, 1111 would appear in both decision groups).

If the objective of this report is to get aggregate data, this approach will be fine because you can create running totals which count records when a record is "approved" or refused, even without utilizing any grouping on Decision

EDIT: This running total will count ALL of the records (ID 1111 would be counted twice). The SQL command is the cleanest way to get you what you need... Another option might be to use a variable. I will research.