How do I use the Set Location and Verify Database features for Crystal reports or subreports that use an ODBC DSN?
Description
Cause
Resolution
[BCB:5:Third-party support:ECB]
Using Set Datasource Location and Verify Database for Crystal reports that use an ODBC DSN

At the workstation or server where you installed both Sage 300 CRE and the Crystal Reports application, upgrade your reports as follows:

Section I: Verify that the Timberline Data Source DSN is set up correctly.

Notes:

  • Default reports and subreports that pull information from the accounting database use the Timberline Data Source DSN. Your custom reports may also be using the Timberline Data Source DSN in order to utilize some of the built-in features and make the report easily portable between users and databases.
  • If you are upgrading a Crystal report for Billing (BL), refer to DocLink: How do I set up an ODBC TS Billing Field Names DSN to modify or create Billing invoice formats? instead of following the steps in Section I. After you complete the steps in the article, continue with Section II below.
  1. Start the 32bit ODBC Administrator using one of the following methods:
    • Click Windows Start and then select Settings, Control Panel, Administrative Tools, Data Sources(ODBC)
    • Click Windows Start and then select Settings, Control Panel, ODBC Data Sources(32bit)
    • Click Windows Start and then select Programs, ODBC, 32bit ODBC Administrator
  2. Select the User DSN tab.
  3. Select Timberline Data Source.
  4. Click Configure.
  5. Click Select Folder.
  6. Select the accounting data folder then click OK.
  7. Click Options.
  8. From the Table and Field Naming list, select Standard descriptions.
  9. Select the Use maximum table segment size check box.
  10. Click OK to exit the Timberline Data ODBC Setup window.
  11. Click OK to exit the ODBC Data Source Administrator window.

Section II: Set Datasource Location and Verify Database in Crystal Reports

To Set Datasource Location:

  1. In Crystal Reports, open the report.
  2. From the Database menu, select Set Datasource Location.
  3. In the Current Data Source section, expand report.
  4. Select Timberline Data Source (or TS Billing Field Names for BL invoice designs).
  5. In the Replace with section, expand My Connections.
  6. Select Timberline Data Source (or TS Billing Field Names for BL invoice designs).
    • Note: If Timberline Data Source (or TS Billing Field Names) does not exist, proceed with these steps:
      1. Expand Create New Connection.
      2. Double-click ODBC (RDO).
      3. In the Data Source Name section, select Timberline Data Source (or TS Billing Field Names).
      4. Click Next.
      5. If you are using Sage 300 CRE security, you will be prompted to enter the User ID and Password. This must be a user that has ODBC Read access. Note: Record Security and File Security access, if activated in Security Administration, applies to the User ID entered so be sure to use an appropriate user ID for the access needed.
      6. Click Finish.
      7. In the Replace with section, select Timberline Data Source (or TS Billing Field Names).
  7. Verify that the data source is still selected in the Current Data Source section.
  8. Click Update.
  9. Repeat these steps for the Subreports section, if present.
  10. After all data sources are set, click Close.

Note: In some cases, using Update on the Timberline Data Source connection is not sufficient to update all the individual tables. If errors occur when running the report or using Verify Database that a table is not found, you will need to update the individual tables as follows:

To Set Datasource Location on individual tables:

  1. From the Database menu, select Set Datasource Location.
  2. In the Current Data Source section, expand report.
  3. Expand Timberline Data Source.
  4. Select the first table listed.
  5. In the Replace with section, expand My Connections.
  6. Expand Timberline Data Source.
  7. Select the same table name that you selected in step 4.
  8. Click Update.
  9. Repeat steps 2 through 8 for each table.
  10. Repeat these steps for the Subreports section, if present.
  11. After all tables are updated, click Close.

To Verify Database:

  1. From the Database menu, select Verify Database.
  2. If you receive the message "The database file "[table name]" has changed. Proceeding to fix up the report.", click OK. Then click OK to the message "The database is now up to date." These messages will appear for each table that was changed since the report was created or last updated. You may see some table names repeat for subreports.
  3. If you receive the message "The database table "[table name]" cannot be found. Proceed to remove this table from the report?", select No to each message. Then perform the steps above in the To Set Datasource Location on individual tables section. You can do this for just the table names that were referenced in the errors. Tip: You can do Verify Database again once you completed updating the individual tables to verify this error no longer occurs.
  4. Save the report (File, Save).
DocLink: How do I set up an ODBC TS Billing Field Names DSN to modify or create Billing invoice formats?

[BCB:156:Chat 300 CRE US:ECB]
Defect ID
Steps to duplicate
Related Articles