SAP Knowledge Base Article - Public

2256996 - Empty or incomplete list of values when Browsing Data or using the Select Expert in Crystal Reports

Symptom

  • Missing values when browsing data.
  • When browsing data, it shows nothing. It is blank.
  • Not all values listed when using the Select Expert in Crystal Reports.
  • When right clicking on a Database Field, and selecting "Browse Data...", it is not showing all the distinct database values in Crystal Reports.
        
           
  • Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

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

Reproducing the Issue

  1. In Crystal Reports, create a new report off any data source that contains large number of records.
        
  2. In the "Field Explorer" pane, right click on a database field, and select "Browse data..."
      
       BrowseData.jpg
          
  3. Notice it is missing values, or shows no values.   

      EmptyData.jpg

Cause

  • By design, when browsing data, it shows a sample of the values for the selected Database Field, and this is why it does not show all the different values present in the data source.
        
  • To ensure the end user does not need to wait for long time for a potential list of values, and therefore maintaining a good performance, the sample list of values retreive is limited by:
    • Query Timeout in seconds.
    • Number of distinct values.
            
  • Whichever limit is reached first determine the number of distinct values that will display in the sample list of values when browsing a database field. And it could result is blank list of values if the timeout is reached before receiving any data from the data source.

Resolution

  • There is no option in Crystal Reports to retreive a full list of values when browsing data for a database field, or when using the Select Expert, but it is possible to increase the Query Timeout, and the Number of Values to Browse limit by changing the registry keys:  BrowseTimeout and MaxNBrowseValues
           
    WARNING: The following resolution involves editing the registry. Using the Microsoft Registry Editor incorrectly can cause serious problems. Use the Registry Editor at your own risk. For further information see the SAP Knowledge Base Article 1323322
      
    1. Before modifying the values, close Crystal Reports.
           
    2. In Microsoft Windows, open the Microsoft Registry Editor. ( Regedit )
          
    3. In the Microsoft Registry Editor, navigate to the following path:
           
      HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseServer
            
    4. Increase the number of seconds to browse by modifying the key:  BrowseTimeout
      1. 4.1 Right click on the key: BrowseTimeout, and select "Modify"
      2. 4.2 In the "Edit" window, set the base to: "Decimal"
      3. 4.3 Increase the value from 5 seconds, to a greater value like: 120
      4. 4.4 Click "OK" to accept the change.
      5.        
    5. Increase the number of values to browse by modifying the key: MaxNBrowseValues
      1. 5.1 Right click on the key: MaxNBrowseValues, and select "Modify"
      2. 5.2 In the "Edit" window, set the base to: "Decimal"
      3. 5.3 Enter the maximum number of values to browse, like: 100000
      4. 5.4 Click "OK" to accept the change.  

Keywords

crystal, reports, browse, browsing, data, sample, timeout, limit, maximum, distinct, cr , 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