How To Troubleshoot Crystal Report Errors
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 regarding basic functionality within Crystal. Microsoft Dynamics SL is not 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 objective of the Report Troubleshooting process is to identify the cause of the reporting error. In order 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?
Are a template, sort, and/or a select being used?
Has this report ever worked? What changed?
Does the report work in other databases (i.e. 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.
4. For complete instructions on troubleshooting a Crystal Report®, see resolution 13127.
Resolution 13127 - Troubleshoot a Crystal Report® used by Dynamics SL.
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 which appears in the title bar of the ROI 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 so, access the appropriate System database and execute the following statement:
SELECT * from RPTCONTROL where ReportNbr = '01620'
(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 is found in. The corresponding Report Filename will be found in the matching ReportNameXX field. This 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, as it will be required in later steps.
4. Determine if the report is a Standard Dynamics SL report.
A. 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, this is a possible indication of a customized report. Rename the file.
B. Using Explorer, locate and note the *.RPT file located in the module's subdirectory (i.e.. \SOLOMONIV\GL\01620.RPT). If the date differs from the date found on Dynamics SL CD which corresponds to the currently installed product, this is 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 is not 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 prior to 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.
A. Access the appropriate System database and execute the following statement:
DELETE from RPTRUNTIME
B. Select appropriate Application database and execute the following statement:
DELETE from RPTRUNTIME
DELETE from RPTCOMPANY
C. After executing the statements, if errors are still reported, but unable to be recreated in the Demo Databases, continue with the following steps in order to examine actual data.
7. Review the SQL Query used by the report.
A. Access the report in Crystal Reports® Designer.
B. Select Database, Show SQL Query.
C. Select the Database Source Name from the list of Machine Data Sources. Click OK.
D. Click OK again at the SQL Server Login Screen to login as 'sa'.
Following is an example of the 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, proceed to Step 8. If a View is listed, proceed to Step 9. If neither a Work Table nor a View is listed proceed 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 prior to 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.
A. 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.
A. Access the appropriate Application database and copy and paste the SQL Query statement reviewed in Step 7 into the Query Window.
B. Remove the quotations using Edit, Replace Find What: " Click on Replace All. This will remove all the quotations. The Replace With is left blank.
C. Remove the qualified database name, owner, object name from the query.
D. Using the example provided in Step 7, delete the DemoApp0060.dbo.vr_01620, leaving only vr_01620
E. From within Dynamics SL, Print Preview the report from the ROI screen. Leave any error messages on the screen without clicking OK. This will insert the necessary temporary record into the RPTCOMPANY.
F. Execute the SQL Query statement in Query Analyzer and review the data for invalid or missing data.
G. If after reviewing the data it is 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. Refer to Dynamics SL On-line help or User Guides for additional information on running the Integrity Checks.
11. If further assistance is needed, contact Dynamics SL Technical Support.