Changing reports Database source to new Single Database Source

Owned by wayewynn.lee
Last updated: Oct 25, 2022 by Andrew Paydon

The following article has been transferred to https://help.synergetic.net.au/s/article/Changing-reports-Database-source-to-new-Single-Database-Source

Purpose

To provide step by step instruction on how to manually assign a new Database source to an existing custom report.  

  • the report may appear to be referencing an incorrect Data Source, this is not relevant as all crystal reports will retain a reference to the database the report was originally developed on - when run through Synergetic the report will use the local ODBC connections
  • it is only when editing the actual report (in "Crystal Reports") that the Datasource location will need to be updated

Responsibilities

  • Reports Developers
  • School System Administrators

Pre-requisites

  • SA equivalent Access
  • Access to Synergetic Reports Site Folder
  • Crystal reports Editor

Procedure

  1. Open up the Customised report in Crystal reports
  2. Select Database, Set Datasource location
  3. Select the Reports Data Source location (currently SynergyOne or SynergyOneFinance)
  4. In My connections you should have a "Synergetic" Data Source (Please note in Single Database there should only be a need for one Database ODBC Connector)
    If not, select Create New Connection, ODBC (RDO), and select the Synergetic Data Source Name (DSN)
  5. Click Update
  6. Enter the Password for SA then Click Finish
  7. Repeat the process for each Subreport
  8. Once each Datasource has been corrected, ensure "Use DSN Default Properties" is set to True (Including Subreports)

Where there are objects in the report that reference multiple schemas, you may need to set the data source and a per object basis.

You may see the following error

This can be addressed by selecting the specific object in the upper and lower window such as below.

You can confirm that the object in the report is being correctly referenced by drilling into the properties in the upper window as per below.

Make sure you do this for objects in all sub reports as required!

Finally, ensure that Use DSN Default Properties is set to True in the upper window for the main report and the sub reports.

Testing

  1. Run the report as usual
  2. Possibly make some test changes to the new Single Database instance to confirm that data is been pulled from the correct source.

Known Problems

  1. SA users unable to see all Tables, Views and Stored procs

    1. This is due to a 8000 object limit imposed by Crystal Reports which can be increased by modifying a registry key as described in https://apps.support.sap.com/sap/support/knowledge/public/en/1215994 

      Increasing this to 20000 should resolve this issue.
    2. Untick the Views, Tables or Stored Procedures depending on what you want to see in Crystal Options, Database Tab
  2. Confusion of how single Database works with a single ODBC Connector.
  3. There might be a separate ODBC connector called Synergetic_Finance this is actually the same as Synergetic (this possibly adds more confusion to the problem) - DO NOT USE THIS IF IT EXISTS!