SAP Knowledge Base Article - Public

1706213 - Parameter List of Value limited to 1000 values for report created off a Universe in Crystal Reports

Symptom

  • Missing values in the list of values.
  • List of Values (LOV) only show up to 1000 values.
  • Universe list of value (LOV) limited to 1000 for report created in Crystal Reports, and viewed in Crystal Reports designer, or SAP BI LaunchPad or CMC.
  • How to increase the List of Values (LOV) size for report based off a Universe (UNV) created in Crystal Reports, and viewed in Crystal Reports or SAP BI LaunchPad?

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
        
  • SAP BusinessObjects Business Intelligence Platform 4.0
  • SAP BusinessObjects Business Intelligence Platform 4.1
  • SAP BusinessObjects Business Intelligence Platform 4.2

Reproducing the Issue

  1. Create a universe using Universe Designer Tool, that connect to a data source that have database fields with more than 1,000 distinct values.
  2. Export the Universe (UNV) to the SAP BusinessObjects Repository.
  3. In Crystal Report, create a report off the Universe.
  4. In the Query Panel, create a query with prompt filter.
  5. When refreshing the report, there will be 5 batches, and each of them will contain 200 values, so only 1000 values are shown in the list of value.

Cause

  • By default, parameter List of Values is limited to 1,000 values, for report base off a Universe in Crystal Reports.

Resolution

  • To increase the maximum number of values for a List of Values coming from a Universe, increase the value of the registry key: QPMaxLOVSize 
     
         
    WARNING The following resolutions involves editing the Microsoft Registry. Using the Microsoft Registry Editor incorrectly can cause serious problems. Use the Microsoft Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322     
      
       
    • For Crystal Reports: ( Client tool )
      1. Open the Microsoft Registry Editor.  (Run "regedit")
            
      2. In the Microsoft Registry Editor, navigate to the following path: 
          
              HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database
              
        Note: If the Database key does not exist, you need to create it.          
                  
      1. Under the key Database, create a new DWORD type value named: QPMaxLOVSize 
           
      2. Set QPMaxLOVSize to a value larger than 1,000    ( For example: 50000 )
          
      3. Close and re-open Crystal Reports.
          
      4. If the list of values is still limited to 1,000, then ensure the Web Intelligence Processing Server List of Values option is also increase. For more information, see the SAP Knowledge Base Article 1195974
                                     
             
    • For the SAP BusinessObjects Business Intelligence Platform server (Windows): 
        
      1. Login to the server where the SAP BusinessObjects Business Intelligence Platform is installed.
               
      2. Open the Microsoft Registry Editor.  (Run "regedit")
             
      3. In the Microsoft Registry Editor, navigate to the following path:
          
            HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database
           
        Note: If the "Database" key does not exist, you need to create it.   
                   
      1. Under the key Database, create a new DWORD type value named: QPMaxLOVSize

      2. Set QPMaxLOVSize to a value larger than 1,000    ( For example: 10000 )

      3. Restart the Server Intelligent Agent (SIA) of the SAP BusinessObjects BI Server.
         
        Note: The Server Intelligent Agent needs to run on the same account under which the registry key has been added, otherwise the List of Values will still be 1,000, as the SIA wouldn't be able to read the registry of another user.

      4. If the list of values is still limited to 1,000, then ensure the Web Intelligence Processing Server List of Values option is also increase. For more information, see the SAP Knowledge Base Article 1195974
        1.              
            
                
    • For the SAP BusinessObjects Business Intelligence Platform server (non-Windows, e.g. Linux):

      1. Login to the server where the SAP BusinessObjects Business Intelligence Platform is installed.

      2. Go to the folder:
         
        BusinessObjects installation folder>/sap_bobj/data/.bobj/registry/32/software/sap businessobjects/suite xi 4.0/crystal reports/database
             
      3. Use vi tool to edit the .registry file. 
        ( For example:  Run a command like: vi .registry )
           
      4. Add one row as below:
          
        "QPMaxLOVSize"=dword:BB8
          
      5. Save the file and restart the SAP BusinessObjects BI server.
         
        Note: BB8 is HEX value which is equal to 3000 as DEC. This will make the limit up to 3000.

         
         
    • IMPORTANT NOTE: In a clustered environment, follow the same steps on all servers in the cluster.

See Also

  • For List of Values coming from a Business View, please see SAP Knowledge Base Article 1218588 for steps on how to increase the number of list of values returned.

Keywords

1000, limit, limitation, CR 2013, CR 2011, regedit, QPMaxLOVSize, max , KBA , BI-BIP-ADM , BI Servers, security, Crystal Reports in Launchpad , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016