I’ve recently upgrade from 4.2 SP3 to 4.2 SP7. Since the migration I’ve seen some odd behavior with one of my dynamic prompts. The prompt is built off of a SQL view that is imported into the Data Foundation. The view contains two columns, a key field and a description field. The view is then sourced for a Business Element which then connects to a shared Business View and finally a List of Values is created.
The issue I’m having is when I run the SQL query I get a certain set of data but when I run the List of Value as a prompt in a report the data is different, there are values missing. I know that the Business Element is getting the correct data because if I create a brand new report and use that Business Element as the source it shows the same data as the SQL query.
I’ve tried creating a new List of Values off the Business View but it still hides some of the values from query. Does anyone have any ideas on what I could try to do next fix this issue?
I was able to find the solution to this and wanted to post it in case anyone else runs into the same issue. The solution can be found in SAP KnowledgeBase article 1218588. I’ll reproduce the text below. Hope this helps someone.
Symptom
Dynamic prompt not populated with all values.
List of Values (LOV) is not showing everything.
Dynamic Parameter does not display all the values in the List of Values (LOV).
How to modify the maximum number of values available in a dynamic parameter in Crystal Reports?
Environment
SAP Crystal Reports 2008
SAP Crystal Reports 2011
SAP Crystal Reports 2013
SAP Crystal Reports 2016
SAP BusinessObjects Enterprise XI 3.1
SAP BusinessObjects Business Intelligence Platform 4.0
SAP BusinessObjects Business Intelligence Platform 4.1
SAP BusinessObjects Business Intelligence Platform 4.2
Reproducing the Issue
In Crystal Reports, create a report off any data source.
Create a dynamic LOV parameter containing over 1,000 values.
Insert the parameter on the report.
When refreshing the report, notice it only display a maximum of 1,000 values.
This is also true after saving the report to the SAP BusinessObjects BI Platform and viewing the report in InfoView or SAP BI Launchpad.
Cause
For performance reasons, the maximum number of values that are returned for a List of Values (LOV) in Crystal Reports is set to 1,000.
Resolution
To increase the maximum number of values for a List of Values (LOV), you can either:
Increase the value for the Registry Key: MaxRowsetRecords; OR
Add the environment variable: BOE_CRLOVMAXSIZE
Registry Key Solution
To increase the maximum number of values for a List of Values (LOV), set the registry key: MaxRowsetRecords, to a value greater than 1,000:
WARNING The following resolution 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
Open the Microsoft Registry Editor (regedit)
In the Microsoft Registry Editor, navigate to the path corresponding to the version of the product used:
Crystal Reports 2008:
32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\LOV
64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\LOV
Crystal Reports 2011, 2013, 2016:
32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\LOV
64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\LOV
Crystal Reports for Visual Studio 2010:
32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\DatabaseOptions\LOV
64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\DatabaseOptions\LOV
SAP BusinessObjects Enterprise XI 3.1:
32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\LOV
64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\LOV
SAP BusinessObjects Business Intelligence Platform 4.x:
64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\LOV
Unix/Linux
Go to <install_dir>/sap_bobj/data/.bobj/registry/32/software/sap businessobjects/suite xi 4.0/crystal reports/databaseoptions
Note: if navigating manually to this location, files and folders starting with dot (.) are hidden by default and should use ls -la to see them.
Create a new key/folder called lov if it does not exist.
Inside lov key/folder, create a new file called .keyname if it does not exist.
vi .keyname
Ensure it contains LOV on the first line of this file. Insert LOV at the beginning if you created the file.
Save and exit.
Inside lov key/folder, create a new file called .registry if it does not exist.
vi .registry
Add the following line (change to another number if needed):
"MaxRowsetRecords"="5000"
Save and exit
NOTES: Add the key LOV, if it is not present.
NOTES: Restart Crystal Reports or Server Intelligence Agent (for BI) after the change.
Add the String value: MaxRowsetRecords
Set the value of: MaxRowsetRecords to the maximum number of records in the table where the values are stored.
IMPORTANT NOTES:
- The higher the number of values set, the longer it will take to see the dynamic parameter prompt screen. In brief, it impacts the performance.
- The value 0 (unlimited), or any value less than 1, will not work with BusinessObject Enterprise XI 3.1 or SAP BI 4.0, 4.1 and 4.2 (including Crystal Reports Server), you must specify a value equal to or greater than 1.
For the change to take effect, restart the application.
For Crystal Reports: close and reopen Crystal Reports.
For SAP BusinessObjects Enterprise 3.1 and SAP BusinessObjects BI 4.x, restart the Server Intelligent Agent (SIA)
Environment Variable Solution for SAP BusinessObjects Enterprise or for the SAP BusinessObjects Business Intelligence Platform
In MS Windows, create a system environment variable named: BOE_CRLOVMAXSIZE
Set the value of: BOE_CRLOVMAXSIZE, to the maximum number of records in the table where the values are stored.
For example 5000:
BOE_CRLOVMAXSIZE = 5000
IMPORTANT NOTES:
- The higher the number of values set, the longer it will take to see the dynamic parameter prompt screen. In brief, it impacts the performance.
- The value 0 (unlimited), or any value less than 1, will not work with BusinessObject Enterprise XI 3.1 or SAP BI 4.0, 4.1 and 4.2 (including Crystal Reports Server), you must specify a value equal to or greater than 1.
- This will override the registry key value if present.
For the change to take effect, restart the application, restart the Server Intelligent Agent (SIA)