There are times when customers may want to create their own reports with a third-party report designer, such as Crystal Reports, using their Clarity database. Performing this action assumes that the customer has Crystal Reports expertise in some fashion. The purpose of this article is to demonstrate how to set up a connection to the Clarity database via ODBC so that a Crystal Reports developer can work with the data to create custom reports.
Important Note: These instructions pertain to version 4.19 or higher of Clarity. This will not work with lower versions. Also, it is important to note that company names cannot contain any special characters (e.g. ".", ",", "$", "@", "#").
Creating a New User to Allow Access From Crystal Reports
- In Clarity, navigate to Configuration > Security > Users.
- Click the "+" button to add a new user.
- Select "Create new user with login".
- Enter a username for the new user.
- Be sure to checkmark the "External User" checkbox. This will allow Crystal Reports to access the Clarity database. YOU WILL NOT BE ABLE TO LOGIN TO YOUR PAYROLL SOFTWARE WITH THIS ACCOUNT, ONLY EXTERNAL PROGRAMS!
- Click "Add" to finish adding the new user.
How to Set Up an ODBC Connection in Windows
- In your Windows operating system, click the start menu.
- In the Search bar, type in "Control Panel" and open them.
- In the control panel, search "data source". Click on "Set up ODBC data sources (32-bit)".
- The ODBC window will pop up. You can choose to set up either a User DSN (Data Source Name), or a System DSN. The difference between the two is that the User DSN is visible to a specific user on a specific machine, while the System DSN is visible to all users of a machine, including NT Services.
- Click “Add…”.
- Select "SQL Server" in the box.
- Click "Finish". The Create a New Data Source To SQL Server window will pop up.
- Type in a "Name" and "Description" for your connection and choose the appropriate SQL Server to connect to. Make sure that you choose the server name with the "/AKM" or "/AKMUS" instance and not just the root server name.
- Click "Next". The Microsoft SQL Server DSN Configuration window will pop up.
- Select 'With SQL Server authentication using a login ID and password entered by the user'.
- Checkmark 'Connect to SQL Server to obtain default settings for the additional configuration options' checkbox.
- For "Login ID", type in the username that you created in Clarity from step #4 in the previous section (Creating a New User to Allow Access From Crystal Reports).
- For "Password", type in the password that you created in Clarity from the previous section (Creating a New User to Allow Access From Crystal Reports).
- Click "Next >" to continue.
- Accept the default settings and click "Next >".
- Select the options that you require for your environment/project. Default settings are usually acceptable.
- Click "Finish".
- The ODBC Microsoft SQL Server Setup confirmation box will appear. You can click "Test Data Source..." to test your settings or click "OK" if you are sure the settings are correct.
- Your connection will now appear in the System Data Sources list. Click "OK" to finish the setup.
The Data Source and Crystal Reports
The newly-created system data source can now be used with any third-party reporting product, such as Business Objects’ Crystal Reports. Simply choose the connection you just created as your data source for the report and all the tables and fields will be available for you to use to create your custom reports. The scope of this document does not include how to create a connection to a database in Crystal Reports.
For more information on how to perform this option, please refer to your product reference guide or visit Business Objects on the web, http://www.businessobjects.com/solutions/crystalreports/default.asp