SAP Knowledge Base Article - Public

1290291 - How to show row numbers on a cross-tab in Crystal Reports?

Symptom

  • How to show a row number in a cross-tab?
  • Is it possible to show the rows or columns number on a cross-tab in Crystal Reports? 

Environment

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

Cause

  • There is no option in a cross-tab to show rows or columns number in Crystal Reports.

Resolution

  • To display a row number in a cross-tab, use one of the following workaround:
    • Use a manual running total on the cross-tab; or
    • Create an array of the values you want to have a row number in the cross-tab, and use the position of the value in the array as the cross-tab row number; or
    • Create a report that looks like a cross-tab, where the groups are the rows, and columns are formulas.
        
  • Note: The proposed workarounds have limitations. Choose the workaround that work best for your report design requirements.
      
      
  • Use a manual running total on the cross-tab
    • The first workaround to show rows or columns numbers on a cross-tab is to use a formula on the cross-tab, but the drawbacks are:
      • Row numbers for the row headers will display in decending order by default , unless you change where the cross-tab total display.
        ( For example: If there is 10 rows, it will show 10, 9, 8, 7... )
      • If the cross-tab span over multiple pages, the row numbers will reset on each page.
      • Row numbers for summaries will show in ascending order, but if there are multiple columns, the row number for the next column will equal to the total number of rows of the first columns plus one. 
          
    • Below are the general steps on how to use a manual running total formula to show row numbers on a cross-tab:
         
      1. In Crystal Reports, create a report based on any data source.
          
      2. Insert a Cross-Tab.
           
      3. To show the row numbers, right click on the row field header of the cross-tab, and select "Format Field..."
           
      4. In the "Format Editor" window, under the tab "Common", click on the "X-2" button of the option "Display String"
           
      5. In the "Formula Workshop", enter a code like:

           WhilePrintingRecords;
           NumberVar RowNumber;
           RowNumber:= RowNumber + 1;
           ToText(RowNumber,"##") + " - " + CurrentFieldValue;
               
      6. Save and close the Formula Editor.
          
      7. Back to the "Format Editor" click "OK" to accept the change.
           
        Same procedure can be followed to add numbers for the columns of the cross-tab.
         
            
  • Create an array of the values you want to have a row number in the cross-tab
    • Create a group on the report based on the field you want to have a row number, then accumulate the values in an array, and use the array in the cross-tab to display the row number. The row number of the value will equal the position of the value in the array. 
        
    • The second workaround limitations are:
      • It requires a more advance understanding on how to use formulas in Crystal Reports.
      • Arrays have a limit of 1,000 elements.
      • The cross-tab can only be displayed in the Report Footer section, because the main report will be use to create the array.
           
    • Below are the general steps on how to use an array to show row numbers on a cross-tab:
        
      1. Insert a group on the report, based on the database field you want to generate a row number for in the cross-tab.

      2. Create a formula to stored the field value in an array. The position of the value in the array will be the row number. The formula will look like:

        WhilePrintingRecords;
        Local StringVar currentGroupName := <INSERT THE DATABASE FIELD NAME THE GROUP IS BASED ON HERE>;
        StringVar Array GroupNames;
        NumberVar ArrayRowNumber;
        ArrayRowNumber := ArrayRowNumber + 1;

        If ArrayRowNumber <= 1000 Then // Array have a limit of 1,000 elements.
        (
            Redim Preserve GroupNames[ArrayRowNumber];
            GroupNames[ArrayRowNumber] := currentGroupName;
        )
           
      3. Insert the formula in the Group Header to store the value of each group in the array.
          
      4. Suppress the sections: Group Header, Details, and Group Footer.
          
      5. Insert the cross-tab in the Group Footer section.
          
      6. In the cross-tab, format the row field you want to display a row number for, and under the tab "Common", click on the "X-2" button for the opyion "Display String"
          
      7. Create a formula to display the row number and the row value based on the array generated earlier.
         
        WhilePrintingRecords;
        Local StringVar CurrentGroupName := currentfieldvalue;
        StringVar Array GroupNames;
        Local NumberVar RowNumber := 0;
        Local BooleanVar Found := False;

        If currentGroupName in GroupNames Then
        (
           While Found = False Do
           (
              RowNumber := RowNumber + 1;
              If GroupNames[RowNumber] = currentGroupName Then Found := True
           )
        );

        If Found Then ToText(RowNumber,0,"") + " - " + CurrentGroupName;

            
    •  Sample report demonstrating this technique is available in the Attachement section below. ( Cross-Tab - Row Numbers.rpt )
        
         
  • Create a report that looks like a cross-tab, where the groups are the rows, and columns are formulas.
    • Create a report that looks like a cross-tab, and use a manual running total to display the row number.
        
    • The limitations of this workaround are:
      • Since it is not using a cross-tab object, the number of columns is not dynamic. Each columns is a formula.
      • Since it is not a cross-tab object, all summaries needs to be created manually.
           
    • For information on how to create a report that look like a cross-tab, see the SAP Knowledge Base Article: 121295

Keywords

numbered list, cross tab numbering, CR, xtab, CrossTab , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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

Attachments

Cross-Tab - Row Numbers.rpt