How to troubleshoot Crystal Report® errors

  • Article

This article describes how to troubleshoot common Crystal Report® errors in Solomon.

Applies to:   Microsoft Dynamics SL
Original KB number:   844751

  1. Microsoft Dynamics SL provides support for the installation of Crystal Reports®, the contents of its standard reports, the performance of its standard reports and responses to general how-to questions about basic functionality within Crystal. Microsoft Dynamics SL isn't obligated to provide debugging support for modified or newly developed reports, but will follow normal Technical Support troubleshooting techniques(outlined below) to verify that no Microsoft Dynamics SL error exists.

  2. The primary goal of the Report Troubleshooting process is to identify the cause of the reporting error. To do so, the following questions should be asked to help understand the details of the problem:

    • What report is being printed?
    • What format of that report is being printed?
    • Is the report customized?
    • Is a template, sort, and/or a select being used?
    • Has this report ever worked? What changed?
    • Does the report work in other databases (for example, Demo database)?

  3. The next step is to determine if the problem is related to the database or the application by trying to recreate the error. If the same error can be recreated in other databases (Demo databases), Dynamics SL Technical Support will then document and submit the test steps to a Dynamics SL Development group so that a correction may be delivered. If however, the error seems to be specific to only that database, then it may be an indication of a problem with the actual data rather than an application error.

For complete instructions on troubleshooting a Crystal Report®, see the following steps.

Note

If any questions arise during the troubleshooting process, contact Technical Support for clarification prior to attempting the recommended action.

  1. Determine which report is being printed by reviewing the Report Name and Screen Number that appears in the title bar of the ROI (return on investment) screen.

  2. Determine which Report Format is selected when printing the report. For example, if troubleshooting the General Ledger Detail Report determine which of the two Report Formats is being used: Standard or Multi-Currency.

  3. Using Query Analyzer, determine the Report Filename corresponding to the Report Format, noted in Step 2. To do it, access the appropriate System database and execute the following statement:

    SELECT * from RPTCONTROL where ReportNbr = '01620'

    Note

    Where theReportNbr = the first 5 digits of the Report screen. The value should be entered in quotes and without the decimal. For this example 01620 is representative of the GL Detail General Ledger Report (01.620.00).

    Review the information stored in the ReportFormat00 - ReportFormat07 fields. Locate the desired Report Format name and note which ReportFormatXX field it's found in. The corresponding Report Filename will be found in the matching ReportNameXX field,which is the *.RPT filename used by Dynamics SL when printing the report. Based on the example, the Standard format of the Detail General Ledger report is stored in ReportFormat00 and the corresponding *.RPT Report Filename - 01620 is found in ReportName00.

    Note

    All information is important, as it will be required in later steps.

  4. Determine if the report is a Standard Dynamics SL report.

    1. Using Explorer, review the contents of the DYNAMICSSL\USR_RPTS directory and determine if *.RPT file noted in Step 3 is found there. If so, it's a possible indication of a customized report. Rename the file.

    2. Using Explorer, locate and note the *.RPT file located in the module's subdirectory (that is, \SOLOMONIV\GL\01620.RPT). If the date differs from the date found on Dynamics SL CD that corresponds to the currently installed product, it's a possible indication of a customized report or a report installation failure. Rename and replace this file with one from the appropriate product CD or Service Pack. Remove the Read-Only attribute if using the CD. Retest the report.

  5. Remove any Templates, Sorts, or Selects and retest the report. If report prints successfully, recreate the Sort Select using the Demo Database and compare the results. If an error is received by both, then the issue may be related to usability or a potential defect. If an error isn't received when using the Demo Database, continue with Step 6.

  6. Delete any orphaned temporary records from RPTRUNTIME and RPTCOMPANY using the following statements in Query Analyzer. All users must exit Dynamics SL before executing the statements.

    Note

    RPTRUNTIME is found in both the System and Application databases in Dynamics SL. RPTCOMPANY is found only in Solomon IV Version 4.x Application databases. These tables are used to identify users and workstations printing the report, allowing multiple users to print the same report at the same time.

    1. Access the appropriate System database and execute the following statement:

      DELETE from RPTRUNTIME

    2. Select appropriate Application database and execute the following statement:

      DELETE from RPTRUNTIME

DELETE from RPTCOMPANY

    3. After executing the statements, if errors are still reported, but unable to be recreated in the Demo Databases, continue with the following steps to examine actual data.

  7. Review the SQL Query used by the report.

    1. Access the report in Crystal Reports® Designer.

    2. Select Database, Show SQL Query.

    3. Select the Database Source Name from the list of Machine Data Sources. Select OK.

    4. Select OK again at the SQL Server Login Screen to sign in as sa.

      The following example is a SQL Query used by the Detail General Ledger report. This statement is executed against the database and the FROM clause used by the Query contains the Database Tables or Views used to print the report. Reports dependent on a Work Table will display an object name(s) beginning or ending with WRK in the FROM clause. Reports dependent on a View will display an object name(s) beginning with vr.

      SELECT vr_01620."Acct", vr_01620."Sub", ..... FROM "DemoApp0060"."dbo"."vr_01620"

      vr_01620 Record any Tables or Views listed in the FROM section of the Query statement. If a Work Table is listed, continue to Step 8. If a View is listed, continue to Step 9. If neither a Work Table nor a View is listed, continue to Step 10.

  8. Delete any orphaned records remaining in a Work Table using the following statements in Query Analyzer. All users must exit Dynamics SL before executing the statements.

    Note

    A Work Table is an empty table used only when a report is being printed or a process is being run. Work Tables should be empty unless a report, dependent on a particular Work Table, is currently being generated in Dynamics SL. For example, AR Customer Statements (08.600.00) uses a Work Table named AR08600_wrk. AR08600_wrk will appear in the FROM clause referenced in Step 7.

    Access the appropriate Application database and execute the following statement:

    DELETE from AR08600_WRK

    Continue with Step 9.

  9. Rebuild the Views, Stored Procedures, and Indexes for both the System and Application databases. Retest the report.

  10. If errors continue after deleting orphaned records from Work Tables, recreating View, or in cases where neither Work Tables nor Views are used in the Query Statements, it will be necessary to review the actual data being returned from the View, Stored Procedure, or Query. Execute the Crystal SQL Query statement, reviewed in Step 7, in Query Analyzer.

    1. Access the appropriate Application database and copy and paste the SQL Query statement reviewed in Step 7 into the Query Window.

    2. Remove the quotations using Edit, Replace Find What: " Select Replace All. Which will remove all the quotations. The Replace With is left blank.

    3. Remove the qualified database name, owner, object name from the query.

    4. Using the example provided in Step 7, delete the DemoApp0060.dbo.vr_01620, leaving only vr_01620

    5. From within Dynamics SL, Print Preview the report from the ROI screen. Leave any error messages on the screen without selecting OK. Which will insert the necessary temporary record into the RPTCOMPANY.

    6. Execute the SQL Query statement in Query Analyzer and review the data for invalid or missing data.

    7. If after reviewing the data, it's determined that the cause of the report problems is missing or corrupted data, use the corresponding module Integrity Check (Verify Options only) to verify supporting records exist. For more information about running the Integrity Checks, see Dynamics SL On-line help or User Guides.

  11. If further assistance is needed, contact Dynamics SL Technical Support.