1207640 - How to eliminate Duplicate Records on a report in Crystal Reports?

• How to eliminate duplicate rows?
• When linking multiple tables together, some of the data is repeated. How to remove the duplicate data?
• A report contains duplicate data, how to suppress the duplicate data from appearing on the report in Crystal Reports?
       
      
• Note: Images and data in this SAP Knowledge Base Article are from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

• Crystal Reports is a reporting tool, and does not create or generates data. Data are coming from the data source, and the main reasons it displays duplicate rows of data are:
• There is duplicate data in the data source.
• The way the tables are linked causes some of the data to be duplicated.

• The following symptom contains two scenarios that involve duplicate records being returned on the report in Crystal Reports. Both scenarios have slightly different resolutions but the concept of suppressing the duplicate records of appearing on the report is the same for both.
          
• In the first scenario, a report is created in the Crystal Reports that includes a field like Customer, Orders,...  When the report is previewed, this field and all its corresponding data are repeated in the report. How can you modify this report so that duplicates of this record and its corresponding data does not display?
      
• In the second scenario, a report is grouped on a unique field like Customer, Invoice, ....  Some groups display one instance of its corresponding data while other groups display duplicate instances of this field and its corresponding data for a single Customer, Invoice,... How can you modify this report so groups containing duplicate records do not display?
       
           
• To resolve the first scenario, follow these steps:
       
1. Sort the report by the database field containing the unique identifier of the Customer, like Customer ID, or Invoice ID,...
    
2. Under the menu "Report", select "Section Expert..."
     
3. In the "Section Expert" window, select the "Details" section.
   
   
4. Then on the right side, select the 'x+2' button next to the 'Suppress' option.
          
5. In the Formula Workbench, create a conditional suppression formula similar to the following:
        
   // This formula suppresses the details section if it is not the first instance of the {Customer.Customer Name} field and
   // the {Customer.Customer Name} field is equal to the previous occurrence of the {Customer.Customer Name}
   
   not onfirstrecord and {Customer.Customer Name} = previous({Customer.CustomerName})
          
    
   Now when you preview your report, each customer, or invoice number record prints only once.

 

• To resolve the second scenario, follow these steps:
   
1. Create a group on the the unique identifier field, like Customer ID, Invoice Number,....
      
2. In the Details section, right click on the database fields that is duplicated, and select  'Insert' menu, select 'summary'. The 'Insert Summary' dialogue box appears.
     
3. From the drop down list box called 'Insert a field which calculates the', select 'count'. Click OK.
     
   When you preview the report, you will notice that there is now a count of the {Customer.Customer Name} field in the Group Footer. You can suppress this field if you do not wish to see it on the report.
       
4. From the 'Report' menu select 'Edit Selection Formula ... - 'Group...'
      
5. Create a group selection formula similar to the following:
          
   // this formula takes the count of the {Customer.Customer Name} field in the
   // Customer group and tests to see if it is equal to 1.
   // If the count is equal to 1, the group is returned.
      
   count ({Customer.Customer Name}, {Customer.Customer Name}) = 1
      
   Now when you preview your report, only groups with no duplicates display.
     
      
• Other suggestions on how to suppress duplicate data, depending on your report design are:
    
• Only suppress the fields that are duplicated using the format field option "Suppress If Duplicated"; or
        
• Create a group on the unique identified field, like Customer ID, Invoice Number,..., and insert all the database fields in the Group Header, or Group Footer section, and suppress the Details section.

CR, duplicate data, duplicate rows, duplicate records , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020