1182322 - How to show a RecordNumber of zero if there are no records on the report in Crystal Reports?

SAP Knowledge Base Article - Public

1182322 - How to show a RecordNumber of zero if there are no records on the report in Crystal Reports?

Symptom

  • Record Number shows 1, when there is no data.
  • Record Number should display the value zero, when there is no data on the report.
  • The special field "RecordNumber" works correctly if there are records in the report. However, if the report contains no record, the RecordNumber field still shows a value of 1 in Crystal Reports.
  • How to display a value of zero when no records are available on the report in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
         
  • SAP Crystal Reports for Enterprise 4.0
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Insert at least one database field in the Details section of the report.
  3. Insert the special field: RecordNumber in the Details section.
  4. Create a Record Selection Formula that returns no data.
  5. When viewing the report, there is no data on the report, but the RecordNumber Special Field shows the value: 1, despite we expect to see the value: 0

Cause

  • The Special Field: Record Number, actually display the number of Details section printed on a report, and the Details section always print once, even when there is no record on the report. This is why Record Number always display 1, regardless there is one record, or no record on the report.

Resolution

  • To display the zero when there is no record on a report, create a formula that check if the total number of record is null, which will indicate there is no data on the report. To do so:
           
    1. In Crystal Reports designer, open your report, or create a new report off any data source.
           
    2. Create a formula called: "My Record Number",  that will be used to display the number of records like:
              
          If isNull(Count(<INSERT A DATABASE FIELD HERE>)) Then
              0
          Else
              RecordNumber
                   
      The above formula count the number of records on the report, and if it is NULL, it returns zero, otherwize it returns the Record Number.
      The only thing to modify from the above formula, it's to insert a  database field where it is written: <INSERT A DATABASE FIELD HERE> 
                 
    3. Insert the formula in the Details section of the report to display the number of records.
            
      Now, when refreshing the report, if there is no data on the report it will display the value zero, but if there is records, it will display the record number for each row.

Keywords

record number count , 1175598 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016