How to Troubleshoot on Crystal Reports Dynamic Prompts/LOV Issues
- Dynamic List of Values (LOV’s) empty in Crystal Reports.
- Unable to get updated LOV’s.
- How to increase the number of values in a dynamic parameter list in Crystal Reports?
- Scheduled LOV’s fails with an error message:
- “Status of List of Values Instance: Failed
Details: Logon to underlying database failed”
- “Failed to open connection” and “Status of List of Values Instance: Failed”.
- Error Message: Error in File <filename.rpt>:
unable to connect: incorrect log on parameters. Details: [Database Vendor Code: xxxxx]
Please find the below steps for troubleshooting on LOV Issues:
- Create a sample Crystal report and add a Static parameter in the report. Make sure that the parameter prompt is showing all the values while refreshing a report.
- You can reproduce the behavior using a sample Database: Xtreme.mdb: https://smpdl.sap-ag.de/~sapidp/012002523100005852352008E/cr_xi_xtreme_rep_smpl_en.zip
- Download the database and create ODBC connection (32 bit).
- In Business View Manager, Create Data Connection, Data Foundation, Business Element, Business View and List of Values.
- Try scheduling LOV’s.
- Open the original report having dynamic parameter in Crystal Reports designer.
- Double click on the dynamic parameter from Field Explorer.
5. Note down the name of LOV from the option: Existing (under Choose a Data Source)
6. Login to Business View Manager (BVM). (Use an Administrator account and Enterprise Authentication)
7. It would open Welcome wizard. (Click on Cancel button)
8. You can see Repository Explorer on right side. (If not, Click on View > RepositoryExplorer)
9. Locate the LOV name as seen in Crystal Reports and Click on the LOV. (E.g. Customer Name)
Now we have found the problematic List of Value (LOV).
Start troubleshooting for this LOV:
- Click on the LOV.
- Click on the below icon to see referenced objects.
3. It will show you the referenced objects:
Here, you can see the Data Connection (DC), Data Foundation (DF), Business Element (BE) and Business View (BV) related to the selected LOV.
4. Open Data Connection and test it. If the test is successful then perform the below steps. If you receive any error message then edit the connection and do the required changes.
5. Open Data Foundation. Right click on one of the database objects and select Browse Field from Object Explorer. If you get data for the browsed field then perform below steps.
6. Open Business Element. Right click on one of the database objects and select Browse Field from Object Explorer. Check if you get data for the browsed field.
Note: At this point, if everything works fine, create a new Crystal report based on same Business View. (Follow steps 12 to 15). If you do not get data in Step 15 then perform the below steps:
7. Right click on Data Connection and select Edit Rights from Repository Explorer.
8. Edit Rights window would open. Now check Rights for Data Access by clicking on Preview:
9. You can explicitly set Granted Rights to the User or Administrator.
10. Right click on LOV and select Schedule List of Values from Repository Explorer.
11. Check the Schedule status by double clicking on the same LOV.
If the schedule status is failed then perform the below steps:
12. Launch Crystal Reports designer.
13. Create a new report on the same Business View on which the LOV is based on.
14. Insert the field (on which the LOV is based on) in the Detail section of the report.
15. If you get data in Crystal Reports then it proves that BVM Data Connection is fine.
16. Save the report to the Enterprise and schedule it.
17. If schedule fails, you need to do troubleshooting on BI servers.
Troubleshooting on BI Servers
If the scheduled List of values are failing in Business View Manager, you need to do troubleshooting on BI server machine.
- When you browse data in BVM – DF, BE or create and refresh a Crystal report based on Business View, it uses Data Connection defined in BVM.
- When you schedule an LOV in Business View Manager, it went through BV, BE, DF, DC to check security and rights. Then Adaptive Job Server (AJS) [In 4.0/4.1], LOV Job Server [In 3.1], Input File Repository (IFRS) and Output File Repository (OFRS) come into the picture.
- In BusinessObjects Enterprise XI 3.1 (Crystal Reports 2008) when you view a crystal report with dynamic parameters in CMC, Crystal Report Cache Server, Crystal Reports Processing Server (CRPS) and IFRS come into the picture.
- In BusinessObjects Enterprise XI 3.1 (Crystal Reports 2008) when you schedule a crystal report with dynamic parameters in CMC, LOV Job Server, IFRS and OFRS come into the picture.
- In SAP BusinessObjects Business Intelligence XI 4.0/4.1 (SAP Crystal Reports 2011/2013), when you view a crystal report with dynamic parameters in CMC, Crystal Report Cache Server, CRPS and IFRS come into the picture.
- In SAP BusinessObjects Business Intelligence XI 4.0/4.1 (SAP Crystal Reports 2011/2013), when you schedule a crystal report with dynamic parameters in CMC, AJS, IFRS, and OFRS come into the picture.
Note: When you explicitly edit the values (or select the values dynamically) from Report Properties > Schedule > Prompts > Edit Values, at that time along with above servers, CRPS and Crystal Report Cache Server come into the picture.). Find below screenshot.
- You can get updated LOV’s in 2 ways:
LOV OnDemand: When there is no LOV scheduled in BVM (Clear the intance in BVM by editing the LOV), when you refresh
the report in CMC which has that LOV, Crystal Reports Processing Server (CRPS) and Crystal Reports Cache server come into the picture.
Scheduled LOV: When we explicitly schedule an LOV in BVM (Like Hourly, Weekly, Monthly…) Adaptive Job server (In 4.0/4.1) / LOV Job server (In 3.1), Input File Repository (IFRS) and Output File Repository (OFRS) come into picture.
- Make sure, the Database client or drivers installed on the BI server are able to communicate with the Reporting Database.
- Follow the below steps to verify the same:
- – Try to ping reporting database from BI server
- – In case of Oracle, launch 32 bit SQL Plus/any oracle 32bit client and try connecting to the reporting database. (To confirm the client version [32 bit /64 bit], open Task Manager and check the process name. It should be: sqlplus.exe *32)
c. – You can verify PATH in Environment Variables.
d. – In case of SQL Server, create a 32bit DSN and test the connection. (Launch odbcad32.exe from the location: C:\Windows\SysWOW64\odbcad32.exe)
3. Check the Network security and firewall configuration.
Unable to get updated List of Values:
- Access your ‘Business View Manger’.
- Click ‘View’ menu and select ‘Repository Explore’.
- Double click on the any of the existing LOV (for report that doesn’t update).
- Set the ‘Oldest on-demand data given to a client (seconds)’ to 0.
- Select ‘Clear Instance’ and OK and test the behavior.
Troubleshooting for migrated LOV’s
- In Case of migrated Reports (Crystal reports and LOV’s), you can start troubleshooting by migrating a single Report with an LOV.
- Re-migrate one of the reports through Promotion Management with all the dependencies and user security and check if it works fine.
- As per the best practices, Business View Manager needs to be installed on the destination environment before migrating LOV’s.
- You can use Business View Manager Export-Import functionality if there is no Formula defined in the Business View.
Crystal Reports (BI-RA-CR):
- 1856647 – Master Note. Summary of potential reasons causing Empty LOV prompt in Crystal Reports
- 1845161 – List of values prompt is empty in Crystal Reports designer
- 1657653 – Crystal Reports prompt has no drop down
- 1218588 – How to increase the number of values in a dynamic parameter list in Crystal Reports?
- 1706213 – BI4 : The list of value(LOV) size is limited to 1000 when creating / viewing a report using UNV type universe in Crystal report designer and CMC / BI launchpad
- 1652210 – List of Values in Crystal Report are displayed in batches when viewed from BI Launchpad and Crystal Report Designer
BI Servers (BI-BIP-ADM):
- 1813053 – Empty Dynamic LOV box in Crystal Reports Designer
- 1651124 – List of values does not prompt with data
- 1904814 – Master KBA: Best practices for a successful migration to BI4 using the upgrade management tool
- 1695453 – LOV prompt groups are not visible after using Promotion Management to promote Crystal Reports along with Business View dependencies
- 1927209 – BusinessViews and LOV’s do not work after being migrated using the Upgrade Management Tool (UMT)