How to connect one dataset to more than one datasources

Hi,

I have a requirement in BIRT like " In a report ,i need to create one dataset and it should connect to more than one Datasources means more than one database schemas."

That report should fetch the required data from different schemas.As per my we can add one datasource to one dataset.The no of schemas are also will keep on changing means now 3 schemas i need to connect after sometime if there is more then it should connect to all at atime and fetch the data.

I hope i am clear enough.
Will it be possible in BIRT.
Please help me out in this.

Thanks in advance
Chaitanya

Find more posts tagged with

Comments

thuston

No. It is not possible as you describe. Each DataSet can only connect to a single DataSource.

You could write your own ODA that would act as a single dataSource and internally it's code would make and manage all the connections. However, I don't understand the use case.
Why would you dynamically adjust the number of databases? What type of query would you use to produce useful data?

rihanna

You should be able to create two different datasets from two different data sources and join the datasets together.

arunkumarb

HI,
In the following data set I am using three different schemas(adm_prod,lcm_prod,hrm_prod). Can you check it once.

select a.employeesequenceno,a.employeeid,
concat(a.firstname,' ',a.lastname) as EMPNAME,
case when a.title=1 then 'Mr.' when a.title=2 then 'Ms.' when a.title=3 then 'Mrs.' when a.title=4 then 'Dr.' end as Title,
a.companyid,
LOWER(b.communicationaddress),
LOWER(b.communicationaddress2),
b.communicationlocationid,
if( b.communicationzip <> '',concat("-", cast(b.communicationzip as char ) ),'') as COMMUNICATIONZIP,
if(c.name is not null ,c.name,' ') as COMM_LOCATION,
d.name as BUNAME,
concat(e.address,',',e.address2) as COMPANYADDRSS,
e.locationid,
e.zip,
f.name as LOCATION1,
g.dateofjoin,
k.firstname as MANAGER,
h.departmentid,
i.name as DEPTNAME,
i.code as DEPTCODE,
j.name as DESIGNATION,
g.termofservice,
(SELECT adm_prod.GET_COMPANY_PARENT(a.companyid,'OU')) as OU,
(SELECT lcm_prod.GET_LOCATION_DETAILS(e.LOCATIONID,'C')) as LOCATION,
m.name as REPORTING_OFFICER_DESIGNATION
from tbl_employee_primary a
left join tbl_employee_personal_contact b on b.employeeid=a.employeeid
left join lcm_prod.tbl_location c on c.locationid=b.communicationlocationid
left join adm_prod.tbl_businessunit d on d.businessunitid = a.companyid
left join adm_prod.tbl_costcenter e on e.businessunitid = a.companyid
left join lcm_prod.tbl_location f on f.locationid=e.locationid
left join tbl_employee_profile g on g.employeeid=a.employeeid
left join tbl_employee_professional_details h on h.employeeid=a.employeeid
left join tbl_department i on i.departmentid=h.departmentid
left join tbl_designation j on j.designationid = h.designationid
left join tbl_employee_primary k on k.employeeid =h.managerid
left join tbl_employee_professional_details l on l.employeeid=h.managerid
left join tbl_designation m on m.designationid = l.designationid
where a.employeesequenceno=?
group by a.employeesequenceno

Hans_vd

So three different schema's in the same database, right?
You only need one datasource object.
As I don't see any references to the hrm_prod schema, so I suppose you wrote the query when logged on to the database with a user that has access to that schema. Can you use that user in the datasource connection?

What is your problem with this query?
Is it not working?
Are you getting any errors?

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="80702" data-time="1311836120" data-date="27 July 2011 - 11:55 PM">
So three different schema's in the same database, right? 
You only need one datasource object. 
As I don't see any references to the hrm_prod schema, so I suppose you wrote the query when logged on to the database with a user that has access to that schema. Can you use that user in the datasource connection? 
 
What is your problem with this query? 
Is it not working? 
Are you getting any errors? </blockquote>
 
HI Hans_vd, 
The above query is working fine and that query is explains that we can use more than one data base schema in a single data set. 
Thuston and Rihanna are saying that we can not use more than one data base schema in a single data set so I posted that query. 
Thanks & Regards, 
Arun

Hans_vd

Hi arunkumarb,

Just to put things right:

Thuston and Rihanna are not exactly telling you that selecting from different schemas is not possible.
They suggest solutions for your initial question, where you ask if it is possible to connect a dataset to more than one datasource. And that is not possible.

You see:
datasource <> schema

Without the query, I would have had the same questions, suggestions, remarks as they had.

Regards
Hans

thuston

'Datasource' has a specific meaning in BIRT. It is the component that defines a single Connection to the Database. That connection may allow any range of functionality, but you can only have one per DataSet.

'DataSet' is the query (SQL) that will be passed through the DataSource to be executed and return a database result set.

chaitanya1

Hi,

Thanks a lot to all of u for ur suggestions...........

Actually in my project ,w ehave 7 warehouses , each warehouse has its own database schema.
So,we have a requirement like we need to connect to the each warehouse and then we need to count the no of transactions happendin that in a consolidated format.

If i use the like databaseschema.tablename like that in the query, main problem is the no of warehouses are not static no. They are going to add more warehouses, i that case if i hardcoded the databaseschema.tablename, every tiem i need to change the query in the report, which is not recomended in our project.

I hope i am clear enough.

Please help me if there is anyother way to implement the same.

Thanks in advance,
Chaitanya.

thuston

It might work to pass the names as a parameter and use it to dynamically write your SQL.

The Metadata cannot change, but that shouldn't be a problem if you do a UNION ALL type query.