SAP Knowledge Base Article - Public

3067776 - No data returned when refreshing a report based on unlinked Tables in Crystal Reports

Symptom

  • Report returns no data.
       
  • No data display on the report, despite there is data in one of the table.
      
  • Report based on unlinked Tables returns no data in Crystal Reports, eventhought one of the table contain data.
      
  • On a report with unlinked tables, when adding a database field from a table containing data first to the report, and a database field from the table that contains no data second, the report returns data in Crystal Reports.
       
  • But on the same report with unlinked tables, when adding a database field from the table containing no data first to the report, and a database field from a table that contains data second, the report returns no data in Crystal Reports.

Environment

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

Reproducing the Issue

  1. In Crystal Reports, create a report based on any data source.
  2. Add one Table that contains data in the database.
  3. Add a second Table that contains no data in the database.
  4. Unlink the Tables.
  5. Add a database field from the Table that contains no data to the report.
  6. Add a database field from the Table that contains data to the report second.
  7. When refreshing the report, it returns no data, despite there is data for the second table in the database.

Cause

  • Crystal Reports is a relational database reporting tool, and tables needs to be linked. When the Tables are not linked, it will create a Cartesian join between all the tables added on the report.
     
  • The order in which database fields are added on the report will determine which table is the "driving table", and therefore the result of the cartesian join will be different, depending on which table is the driving table.
       
    A cartesian join, or cross-join, combines the result of each row from the first table, with each row of the second table.
      
    For example, if we have the following Tables:

    - Table A  
    - Table B

    Where Table A, contains no data.
    And Table B, contains the following 2 rows:

    - Value 1
    - Value 2
      
    If we add a database field from Table B to the report first, then a database field from Table A second, it will return 2 rows, because it will combine the result of Table B with the result of Table A. Since Table B returns 2 rows, it will combine the 2 rows with nothing from the other table, which will result in 2 rows.
       
    But if we first add a database field from Table A, that contains no data, and a database field from Table B, which contains 2 rows, then it will return no data, because there is nothing to combine, since Table A returns nothing.
           
       
  • Note: When designing the report containing unlinked tables, the report designer would have recieved a warning the report contains multiple starting points, and it is not supported. And since there is no link between the tables, when refreshing the report Crystal Reports will generate a cartesian joins between the unlinked tables.

Resolution

  • When there is no relationalship between the tables used on a report, and you want to return the data each table contains independently, then use one table on the main report, and add the other table in a subreport.
          
  • A subreport is a report within a report, and no relationship is required between the main report data source and tables.

Keywords

CR, cross-join , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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