Skip to Content

Symptom:

  1. Dynamic List of Values (LOV’s) empty in Crystal Reports.
  2. Unable to get updated LOV’s.
  3. How to increase the number of values in a dynamic parameter list in Crystal Reports?
  4. 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]

Troubleshooting Steps:

Please find the below steps for  troubleshooting on LOV Issues:

  1. 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.
  2. 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
    1. Download the database and create ODBC connection (32 bit).
    2. In Business View Manager, Create Data Connection, Data Foundation, Business Element, Business View and List of Values.
    3. Try scheduling LOV’s.
  3. Open the original report having dynamic parameter in Crystal Reports designer.
  4. Double click on the dynamic parameter from Field Explorer.

/wp-content/uploads/2015/09/1_799306.jpg

  5.  Note down the name of LOV from the option: Existing (under Choose a Data Source)

/wp-content/uploads/2015/09/2_799427.jpg

  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)

/wp-content/uploads/2015/09/3_799428.jpg

  Now we have found the problematic List of Value (LOV).

Start troubleshooting for this LOV:

  1. Click on the LOV.
  2. Click on the below icon to see referenced objects.

/wp-content/uploads/2015/09/4_799436.jpg

  3.  It will show you the referenced objects:

/wp-content/uploads/2015/09/5_799437.jpg

  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.

/wp-content/uploads/2015/09/6_799469.jpg

  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.

/wp-content/uploads/2015/09/7_799462.jpg

  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.

  /wp-content/uploads/2015/09/8_799471.jpg

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.

/wp-content/uploads/2015/09/9_799481.jpg

  8. Edit Rights window would open. Now check Rights for Data Access by clicking on Preview:

/wp-content/uploads/2015/09/10_799482.jpg

  9.  You can explicitly set Granted Rights to the User or Administrator.

/wp-content/uploads/2015/09/11_799483.jpg

  10.  Right click on LOV and select Schedule List of Values from Repository Explorer.

/wp-content/uploads/2015/09/12_799488.jpg

    11.  Check the Schedule status by double clicking on the same LOV.

/wp-content/uploads/2015/09/13_799489.jpg

  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.

/wp-content/uploads/2015/09/14_799490.jpg

  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.

Information:

  • 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.

/wp-content/uploads/2015/09/123_801583.jpg

  • 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.

Troubleshooting Steps:

  1. Make sure, the Database client or drivers installed on the BI server are able to communicate with the Reporting Database.
  2. Follow the below steps to verify the same:
    1. – Try to ping reporting database from BI server
    2. – 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)

/wp-content/uploads/2015/09/15_799571.jpg

          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.

            1813053 – Empty Dynamic LOV box in Crystal Reports Designer

Unable to get updated List of Values:

  1. Access your ‘Business View Manger’.
  2. Click ‘View’ menu and select ‘Repository Explore’.
  3. Double click on the any of the existing LOV (for report that doesn’t update).
  4. Set the ‘Oldest on-demand data given to a client (seconds)’ to 0.
  5. 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.

1927209 – BusinessViews and LOV’s do not work after being migrated using the Upgrade Management Tool (UMT)

  • You can use Business View Manager Export-Import functionality if there is no Formula defined in the Business View.

References:

Crystal Reports (BI-RA-CR):

BI Servers (BI-BIP-ADM):

Deployment (BI-BIP-DEP):

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. NAVID USMANI

    A must needed document, may help end users to look more in to the issue related with Dynamic LOVs or parameters.

    Very nicely drafted 🙂

    Keep it up Nilesh!!

    Regards

    Navid Usmani

    (0) 
  2. Kollipara Phanindra

    Good job Nilesh! Thank you for creating and sharing this article. This will be really helpful while troubelshooting issues with Dysnamic LOV’s in Crsytal Reports.

    Phani

    (0) 
  3. Prasanna Pathade

    Hello Nilesh,

    Nice document. \m/

    Questions –

    1.Could you please mention how to monitor a scheduled LOV in BVM? We can see the status as successful/failed in the BVM, but can we see the status as “running” or “in-process” ? Are there any other ways like query builder, via CMC, etc?

    2.For promoting a Crystal Report with a dynamic LOV(via BVM), do we still need to export the BVM xml and import it in target environment –OR– we check dependencies in Promotion Manager and LOVs are migrated?

    Thanks,

    Prasanna

    (0) 

Leave a Reply