Overview

1. Create a 32-bit ODBC connection called a System DSN on each computer that will be accessing the reports.
   
2. Make a one-time adjustment to each report file in the Crystal Reports Designer to connect it to the ODBC connection.
   

Create ODBC System DSN

1. From your start menu (Windows icon) type ODBC
   
2. Choose ODBC Data Sources (32 bit)
   
3. Select the System DSN tab and click the Add... button
   
4. Choose SQL Server as shown and click Finish
   
5. In Name enter a name such as SageERP which should be the same on any other computers which will access the same reports, Description can be anything you like (e.g. 'Sage 300 CRE'), finally in the Server section select or type in the name of the SQL Server instance
   
6. It is recommended that Windows authentication is used in most cases (when you have centrally managed logins), if all users in the organization will share a single account to read data then SQL Server authentication can be selected (and you will need to enter the credentials below and make sure they are saved so you are not prompted when running reports). 
   
1. Using SQL Server authentication will require that the password for the SQL account be stored in the data connection itself, or alternatively the users will need to manually fill in the password each time a report is run.  Storing the password in the data connection is considerably less secure as this connection is stored as plain text.
   
2. Using Windows authentication requires that the Windows user has at minimum a Read Only connection to the SQL Server database.  This can be accomplished through either directly giving users access to a database in SQL Server Security Logins, or can be done by creating an Active Directory security group and assigning the permissions in SQL Server Security to the Active Group. 
   
3. For more information on setting up SQL Server authentication, please consult Microsoft documentation: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver16#SSMSProcedure
4. 
7. Change the default database to your anterraDataCenter database (often this is ADCData)
   
8. Don't change any settings on this screen:
   
9. Click the Test Data Source button and confirm that success is shown
   

Adjusting Sage 300 CRE Crystal Reports

It is our recommendation that you make a backup copy of any RPT file prior to modifying it. You can do this by clicking File and then selecting the Save As... option. This will open the Save As prompt allowing you to select where to save the copy of your report. Once you select where you want it to be saved you can enter the name of the report to be saved and then click the Save Button.

1. From your start menu (Windows icon) type Crystal Reports and select SAP Crystal Reports XXXX for Sage
   
2. Open the File menu and open the report you want to modify to use SQL Server.
   *If the report you want to load is not on the list, please click the Open... selection from the dropdown and find your saved file to load from your PC.
   
3. Click on the Database menu and select Set Datasource Location
   
4. Only the first time you're editing a report:
   
1. Select Create New Connection > ODBC > Make New Connection
   
2. Select new DSN in Data Source Selection.
   
3. Checked Trusted Connection (unless you used  SQL Server authentication in step 6 during ODBC setup)
   
5. Date fields in the report do not automatically map across from Pervasive to SQL Server and must be mapped manually.  If the 'Map Fields' dialog box comes up after making the change to the data source location, uncheck the 'Match Type' dialog box, and manually select the same field names as the field in the Unmapped Fields section, and then click the Map button.  Each unmapped field will need to have this step performed for both the main report, and subreport tables if they exist. 
   
6. If you have multiple data folders in ADC these steps are also required:
   
1. Verify all tables used in the report are shown under Selected Tables then click on the Links tab
   
   
2. ADC adds a column called DBID which contains the name of each ADC Connection / data folder. If you have multiple Sage Data folders set up in ADC, It is strongly recommended that the DBID field be added to all other linked tables that have the DBID field. This allows your report to work across data folders and allows you to add the DBID column to the report so you can see the company the data originated from.
   
   
3. To link tables which already have a relationship drag and drop DBID from the parent table (Source) and drop it on the child (Target) to link it. Validate that the DBID field appears in blue and a return arrow appears on your mouse cursor.
   
4. Click the OK button to save.
7. Test Report in Crystal Reports Designer.
   

Add New Report to Sage

1. Open the New Report dialog in Sage as your normally would, then select Crystal report design ( Important: do NOT check the 'Use SQL' check box as this is a specialized option that only works with the Sage data replicator).
   
   
2. Select the report you created in the earlier step
   
3. Test the new report by selecting it in the Sage menu.