nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Union of datasets and Data from multiple datasources

gsateesh

Hi,We have a requirement where we have to collect same table data from two datasources and need to display it in a sigle table. I mean need to combine both results into a single. I have checked in birt design for UNION option but did find it. I have read that we can achieve it by creating scripted data set by combining these two datasets. But I am not sure of how to do as I am new to BIRT. Can you please help me out with some sample code with default BIRT Database?

Find more posts tagged with

Comments

micajblock

A simple search on the DevShare came up with this: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/devshare/_/designing-birt-reports/simple-union-dataset-r1557'>http://developer.actuate.com/community/devshare/_/designing-birt-reports/simple-union-dataset-r1557</a>

gsateesh

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="126572" data-time="1393850928"><div>A simple search on the DevShare came up with this: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/devshare/_/designing-birt-reports/simple-union-dataset-r1557'>http://developer.actuate.com/community/devshare/_/designing-birt-reports/simple-union-dataset-r1557</a></div></blockquote>Thanks a ton for your quick reply as I am struggling to find it from last one week. This is what I am looking for. But we are using oracle database. I will use this and try to make it with Oracle database.

micajblock

P.S. This is an OOTB feature of Actuate BIRT (commercial)

gsateesh

Hi thanks for the information. The above said suggestion has worked for me and I am able to generate merged data in a single table. Now I have another situation where I need to collect data from both data sets (each from each DB instance) and perform aggregations on the resulted data (something like groupings, sum etc). Can you please guide me in this. Thanks in advance.

micajblock

I am not sure what you mean. If you mean one aggregate per database, then add an extra column to eache data set with a hard coded string, so you know which database each row came from. If you mean aggregates across the databases, this should be OOTB.

gsateesh

I have to do the aggregations across the databases. For e.g. groub by department. This needs to be applied on the final dataset (UNION of both datasets from both database instances).

micajblock

So what happens when you try to group by department? It should work.

gsateesh

Let me clear my confusion first so that you can better understand my problem. We have two database instances. Now I have two tasks to do.1) Generate report with data (say employee table) from both instances. For this as you suggested, I have created two datasources ,one for each instance, and two datasets (say DS1, DS2) correspondingly. I have created one scripted datasource and dataset (for making UNION of two datasets) and generated the report. I have 50 employee records in one instance and 100 in other. I am able to generate report with 150 records. Its working fine. Thanks a lot for your help in this.2) Now my second problem is with aggregations on the combined dataset (scripted dataset). For e.g. Applying group by department. I can add this group by on individual datasets (DS1 or DS2). But I want to apply this group by on the combined dataset of these two, DS1 and DS2. Or for e.g. need to calculate sum of employee salaries. I can do it for individual instance, but don't know how to do it across the database instances, i.e. on the combined dataset(UNION of DS1 and DS2)

micajblock

Do not group on the data set. Group in the report. Create a table, group the table on the department, then put your aggregations on the table.

gsateesh

Great!!! Perfect... Thank you very much Mica. It solved my problem of applying groupings.