Unable to Preview report “%Report Name Here%”.

External exception E06D7363

Crystal Report (System)

System Report ID: (xxxx,17000)

Although the error looks to be generated by Control, the error is actually being returned by Crystal Reports. This error is returned when the report encounters a problem either querying the data, compiling the results, or performing the coded tasks within the report. In some cases the problem may be the actual report, however in other cases the error is likely caused by a data problem within the SQL tables.

Normal

You are still able to operate within the program however your ability to run certain reports will be impeded. This may have a direct impact on your ability to analyze data. This is an issue that you should contact Technical Support to get resolved if you are not knowledgeable on the steps to resolve yourself.

• Incorrect or missing SQL Server Indexes on the tables utilized by the report.
• Duplicate records in the SQL table with the same primary ID.
• NULL values in critical fields that are utilized by the report.
• SystemData.mdb file was not updated during an update installation
• Data returned is too large for the size of the field. (ex: Address is 60 characters however, the field on the report is nvarchar(5))

1. Test the report in the latest version of Control.
2. If there are no issues, save the report as a Crystal Report (rpt file)
3. Create a on drive report on the system with the error using the saved rpt file.
4. Preview the on drive report to see if the error occurs.

Note: If there is no error, it is safe to assume that the issue lies in the report itself and is fixed in a later version.

Note: This resolution is a continuation of Resolution 1 to further troubleshoot the specific field with the error.

1. Obtain the SQL Command from previewing the report on another order, database, etc.
2. Run the SQL command in SQL Server Management Studio.
3. The results should indicate a string was truncated. If so, this means that a field on the report is too small for the data it's trying to load.
4. Comment out each field one-by-one until the error doesn't happen in order to find the problem field.
5. To fix the issue, increase the size of the field on the report or decrease the size of the data, in the field, in the database.

Go to RunImage to find a copy of the current SystemData file and compare it to what is in the customer's system. After you've created a backup try replacing the SystemData file and restarting the SSLIP. Once you're in Control you should be able to preview Estimates and Orders again.

One recent cause of this issue was that the C drive on the server was out of space. Even though the StoreData.mdf file was on a different drive, SQL Server could not grow its own Master database causing an access issue to the StoreData database. Clearing up space on the C drive resolved the issue in this case.

In the case where this issue was discovered, the user was attempting to run financial reports that relied heavily on the SQL table Ledger. It was later found in troubleshooting that certain records where missing values, there were some duplicate values, and some missing indexes. The steps below are the resolution to that specific problem however these steps may prove useful in troubleshooting other problems similar to this one.

• Find any NULL values in the DivisionID field within the Ledger Table and set them to the default DivisionID.

UPDATE Ledger
SET DivisionID = 10
WHERE ID > 0 AND DivisionID IS NULL

• Find any Duplicate ID's within the Ledger Table and re-sequence them accordingly.

-- This query will provide you with a list of duplicate ID's.
SELECT ID, COUNT(ID) AS IDCNT FROM Ledger
GROUP BY ID
HAVING COUNT(ID) > 1
ORDER BY ID
;

-- This query should be modified as required to remove or re-sequence the duplicate ID's.
DELETE FROM Ledger
WHERE ID = -1 AND StoreID  0
;
UPDATE TOP(1) Ledger
SET ID = ( SELECT MAX(ID)+1 FROM Ledger )
WHERE ID = 999
;
UPDATE TOP(1) Ledger
SET ID = ( SELECT MAX(ID)+1 FROM Ledger )
WHERE ID = 1000
;
UPDATE TOP(1) Ledger
SET ID = ( SELECT MAX(ID)+1 FROM Ledger )
WHERE ID = 1000

• Insert a new set of SQL Table Indexes to prevent Duplicate ID's from being written to the table in the future.

ALTER TABLE [dbo].[Ledger] ADD CONSTRAINT [Ledger_PK] PRIMARY KEY  CLUSTERED
      (
            [ID]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO
CREATE  INDEX [Ledger_AccountID] ON [dbo].[Ledger]([AccountID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_ClassTypeID] ON [dbo].[Ledger]([ClassTypeID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_DepositJournalID] ON [dbo].[Ledger]([DepositJournalID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_DivisionID] ON [dbo].[Ledger]([DivisionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_EntryDateTime] ON [dbo].[Ledger]([EntryDateTime]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_GLAccountClassTypeID] ON [dbo].[Ledger]([GLAccountClassTypeID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_GLAccountID] ON [dbo].[Ledger]([GLAccountID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_GLClassificationType] ON [dbo].[Ledger]([GLClassificationType]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_GoodsItemID] ON [dbo].[Ledger]([GoodsItemID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_GroupID] ON [dbo].[Ledger]([GroupID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_ID] ON [dbo].[Ledger]([ID], [EntryDateTime]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_TransactionID] ON [dbo].[Ledger]([TransactionID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE  INDEX [Ledger_TransDetailID] ON [dbo].[Ledger]([TransDetailID]) WITH  FILLFACTOR = 90 ON [PRIMARY]

• Reported : 07/15/2010
• Version(s): Control 4.4, Control 4.5, Control 5.7

This listing is provided to show related variations of the same error:

Unable to Preview report “Financial Summary”.

Unable to Preview report “Account Register”.

Unable to Preview report “A/R Summary”.