* Join two Datasets from different DB in BIRT Report

 

You have two tables on different databases.

Database Name: DB_1 
Table Name: Production

Worker_ID   Machine_ID  Project     Good        Bad 
1188        001         Test_01     5           0  
1005        001         Test_01     6           0

and

Database Name: DB_2
Table Name: User

Worker_ID   Name        Surname
1188        John        Doe
1005        Donald      Trump

You would like to show on the table that info like this on BIRT Designer.

Machine_ID    Project    Good    Bad    Worker_ID    Worker_Name    Worker_surname
001           Test_01    5       0      1188         John           Doe
001           Test_01    6       0      1005         Donald         Trump

There are two easy ways to solve the problem.
1). Use something like a database link - Oracle Database Link. This way, you move the problem to the database level.

First of all, you need to have a database link on DB_1 or DB_2.

Assuming you have such a $ORACLE_HOME/network/admin/tnsnames.ora file for DB_2 :

DB_2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydb2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bp83.mycompany.com)
    )
  )

Let’s create a db-link on DB_1 targeting to DB_2 :

create public database link DB2_LNK
  connect to HR -- assumed schema name
  using 'mydb2:1521/bp83.mycompany.com';

and need an inner join query now (assuming you’re on DB_1 then):

select p.machine_id as "Machine_ID", p.project as "Project", p.good as "Good", p.bad as "Bad", u.Worker_ID as "Worker_ID", u.Name as "Worker_Name", u.Surname as "Worker_surname" -- with formatted titles
  from Production p inner join hr.User_@DB2_LNK u  -- a keyword "user" cannot be used as a table name("ORA-00903: invalid table name" error raises when you attempt), so i assumed table name as "User_".
    on  ( u.Worker_ID = p.Worker_ID )

2). Use esProc with BIRT.

Here is the SPL script.


A
1 =Production=DB1.query(select   * from Production)
2 =User=DB2.query(select   * from User).keys(Worker_ID)
3 =Production.join(Worker_ID,User,Name:Worker_Name,Surname:Worker_Surname)

Your BIRT reports can have a query from two data sources no matter what kind of database and go on other computations that are not convenient on BIRT. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.