Connect Crystal Reports to your Acctivate data

Acctivate uses standard Crystal Report files that link directly to the Microsoft SQL Server database server.  This allows for report designers to work within Crystal Reports even when access to the Acctivate database is not available.  However, it is very helpful to have a “live connection” to the Acctivate database when working on custom reports.

Use the following procedures to 1) Connect To and 2) Re-link the Crystal Report to your Acctivate database.

1) Connecting to the Acctivate Database

When working with Crystal Reports, it will prompt you to log into the database to access your company information. This will also allow you to preview your data within Crystal Reports. Follow these steps to connect to the database:

  1. Open the report file (RPT) in Crystal Reports and press F5 to “preview”
  2. The Connection Information dialog will appear
  3. Server: Select the SQL Server/Instance from drop-down or enter it manually
    • The Server is typically ComputerName\ACCTIVATE where ComputerName is the computer running SQL Server for Acctivate.
    • The Server can be found in the Database Sources list in Acctivate Database Maintenance.  The Database Maintenance utility is typically found on the Windows Start Menu on the Acctivate server.
  4. User ID & Password: Acctivate uses the SQL Server “sa” user by default.
    • Read more about the SQL Server password for Acctivate
  5. Database: Choose your company database from the drop-down list
    • The database name is typically ACCTivate$CompanyID where CompanyID is the abbreviation of your company name.
  6. Integrated Security: This option bypasses SQL Server Authentication and requires that each Windows User Account has access to the Acctivate database.
    • This is a convenient option for developing Crystal Reports, as long as you have permission to directly access the SQL Server database.
  7. Click Finish to refresh the data in the report
    • Login failed for user ‘sa’ is a common error that indicates you either typed the Connection Information incorrectly.  Double-check the Database Source settings and try again
  8. The information displayed in our standard reports is from the ACCTivate$Demo database.
    • You can make changes against the Demo company and the report will still pull your company data.
    • Follow the instructions below to re-link the report to your company database.

2) Link the Crystal Report to your Acctivate Database

Crystal Reports supports multiple databases within a single report.  The connections to the database server and specific database(s) are actually stored within the Crystal Report (RPT) file.

Acctivate replaces the stored SQL Server and database information with your company database at run-time.  Use the Set Datasource Location tool in Crystal Reports to re-link each database view to your database.  This will allow you to make changes and review the results immediately within Crystal Reports.

  1. Click Set Datasource Location from the Database menu in Crystal Reports
  2. Double-click the Active Connection (with plug icon) under My Connections
  3. Double-click your company database (i.e., ACCTivate$CompanyID)
  4. Double-click dbo then Views to see a list of database view objects
  5. You must re-link each database view (one-at-a-time)
    1. Click/Select the first Data View from the Current Data Source list (at the top)
    2. Click/Select the corresponding Data View in the Replace with list (at the bottom)
    3. Click Update
  6. Click Close after you have updated each data view object
  7. Refresh the report (press F5) and confirm your company data is now displayed
Posted in Database, Reports - Last modified on April 28, 2016Lauren Stine
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.