Multiple database

Hi All,

I have a requirement to generate a report which should display records from a table having database at multiple locations.

Lets say I have two different offices located geographically at different location having their own databases having a table called "table1" on both the databases and since I have located at head office and I want to generate a report which should display records from "table1" but from both the databases in a single report, is this possible to do ?

Thanks for your suggestion(s)/help.

Regards,
Sarfraz

Find more posts tagged with

Comments

ahsiang

Hi, you can create 2 different data source connecting to your 2 database. After that, again create a data set for each of the table in the 2 different database. Finally, create a union or join data set and you should be ready to go.

sarfraz

Hi, Thanks for your reply.
I had approached the way you have suggested, correspondingly I created two data sources and created two data sets using the those datasources, now created a JointDataSet using "Full Outer Join" but it will treat both data as separate and it does not solve my purpose i.e. to use the data from both the table using a single column name it means my SQL Query should server fetch data from both the databases.

I have no idea about creating Union, if there is some suggestion then please let me know about this also.

Thanks once again.

ahsiang

Hi, when you right click data set, you will see Union Data Set. Is it not there? Which release of BIRT you using? When you do union, both table must have same set of columns. Both will be merge into a single data set. Eg, table A has 100 records, table B has 200 records. Once you union, it will have 300 records. Take a look here, http://www.birt-exchange.com/be/documentation/R11SP1/BIRTRD/help/UsingABIRT/wwhelp/wwhimpl/common/html/wwhelp.htm#context=UsingABIRT&file=ds-CreatingAUnionDataSet.html

johnw

The joined data set as a union is the right approach.

If for some reason that doesn't work, then you can create a third data set as a scripted data set. In the onFetch method for each of the two database sets you would populate a global ArrayList. Drop the two tables onto your report and set the visibility to hidden. Then, for the Scripted dataset, script it to display the results of the global ArrayList (should only require code in the open event and the fetch event). Then, drop that dataset into your report, and it will display the results of the array list, which should be a merge of the two previous one. Be sure that the scripted data sets table is AFTER the two database ones. It gets executed in the order received. I can work up an example if you need.

sarfraz

Thank you very much.

The approach you suggested seems perfect to me.

Regards,
Sarfraz

sarfraz

Thanks for your suggestion.

Currently I am using BIRT 2.6, and I could not find feature called "Union Data Set". There is only two kind of data source option available i.e. 1)New Data Set and 2)New Joint Data Set

Regards,
Sarfraz

Yaytay

Don't disregard the option of pulling the data into one database first.
Depending on how often you are running the report and what the report does with the data this could be worth the extra effort - and remember, once you present a report that summarises the data from multiple locations you just know someone will want more details