CODEWITHSUNDEEP
crystal-reportsmultiple-databases
Jonathan3524
Jonathan3524

Reputation: 1

Crystal Reports Joining Tables from Different Databases

I'm trying to pull data into a crystal report from two different databases. My understanding is that its possible to pull data from two identical tables using a UNION query, however my tables aren't the same. They need to be joined on a particular column. For example, assume a table with EmployeeID and EmployeeName and a second table in a second database with EmployeeID and EmployeeSalary. Is it possible to join these tables on the ID and show the Name and Salary of each Employee?

Upvotes: 0

Views: 4571

Answers (1)

wonkybadonk
wonkybadonk

Reputation: 432

You should be able to add both databases to Crystal under the 'Database' drop-down menu -> 'Set Datasource Location' option screen.

Or if both databases already exist in 'My Connections' then when you're creating a new report, on the Data page, click each database instance that you want to build the report from and log in to each one.

For your specific case, if creating a new report. In the Data screen of the Standard Report Creation Wizard:

  1. Click Database A, log in
  2. Move TableX over to the selected tables
  3. Click Database B, log in
  4. Move TableY over to the selected tables
  5. Click 'Next'
  6. In the Links screen Crystal should SmartLink the tables based on their shared column, in your example EmployeeID
  7. If it doesn't SmartLink on EmployeeID then manually link the tables based on the EmployeeID column

It is also possible with Oracle 9 and up to create a DBLink between the two databases and in the raw sql queries you would simply call the dblink out with @dblink syntax like the following:

    select employeename,
           employeesalary
    from tablea a
      inner join tableb@dblinkname b
        on a.employeeid = b.employeeid

Upvotes: 2

Related Questions