This page describes how to create an ODBC connection, which will allow you to modify reports through Crystal Reports.This will also allow you to access the database from other tools and software.

Concept

In order to create a custom report or modify a standard report in Crystal Reports, you will need to establish a link between your report and your Control database. This link is called an ODBC (Open Database Connection) link. It allows you to communicate with your database so you can pull tables and fields directly out of your database onto your report. The ODBC is stored in a .dsn file that gets created everytime you preview a report in Control. If you open your .dsn file in Notepad, you'll see that all of the necessary information needed to connect to your database is there - server name, database name, user name, password, etc…

Cautions

As with any report changes, be sure to make a backup of your report before making changes to it. The only other caution would be to make sure you are using the correct version of Crystal Reports. If your reports will be used in Control, you'll need to use Crystal Reports version XI to modify reports. Note that these versions are not forwards or backwards compatible.

Creating an ODBC Connection

This is the connection from your system to the database and this can be done in several ways which are outlined below.

Method 1:

  1. Use the .dsn file that Control creates every time you open a report in Control
  2. Open Control and open and report in Control.
  3. Keep Control open and open your My Computer icon.
  4. In the address bar, type “%temp%” (no quotes). This will open up the local Temp folder.
  5. You should see a folder called “Control….”. Inside of that folder, you should find a file called “DNSFile….DSN”.
  6. Copy that file to a location that your computer with Crystal Reports installed on it can access. Feel free to rename the file.

Method 2:

  1. Request a GenericDSN.DSN file from the Cyrious Consulting team.
  2. Once you receive it, copy the file to a location that your computer with Crystal Reports installed on it can access.
  3. Right click on the file. Click Edit.
  4. Enter your server name where it asks you.
  5. Save the changes.

Method 3:

  1. Go into Control Panel > Administrative Tools > ODBC Data Sources (32-bit)
  2. Click on User DSN (file level) or System DSN (system level) - This is really your preference on whether you want a file level DSN or a system-wide one.
  3. Click the Add button.
  4. Scroll to the bottom of the driver list and select SQL Server or SQL Server Native Client
  5. Specify the name of your connection, this can be any name you want. i.e. CyriousControl
  6. Under Server, you'll need to type SQL instance you want to connect to from your local computer then click Next.

i.e. MyServer\CYRIOUS

Where MyServer is the computer name of your server, and CYRIOUS is the SQL instance name

  1. For SQL Server authenticity, select SQL Server authentication then specify the appropriate Login ID and Password then click Next.

**Note:** You'll need to contact Cyrious Support or Consulting for this information. If you are familiar with adding SQL users, you can add your own instead if you prefer.

  1. Under Change the default database to: you'll need to select your Control database then click Next. i.e. StoreData
  2. Click Finish, then click OK
Using the OBDC Connection in Crystal Reports

The next step is to use the DSN file to link to your database.

  1. Open your report in Crystal Reports.
  2. Click Database > Set Database Location
  3. On the ODBC (RDO) screen, select Find File DSN and navigate to your DSN. Click Next.
  4. You will need to set up a SQL Server Login on your server PC. If you are not familiar with this and need assistance, please contact Cyrious Tech Support.
  5. You should then be able to add and remove tables and/or fields as needed.

Source

Contributor: LoriM Cyrious Software

Version: Control 3.1+

You could leave a comment if you were logged in.