How to union two data sets with BIRT open source?

Hi,

i have 2 scripted data source. How can I union these data sets?

In sql it would be trivial:

SELECT * from DataSet1
UNION
SELECT * from DataSet2

Thx + bye
NewInBirt

Find more posts tagged with

Comments

mwilliams

Hi NewInBirt,

You can create a joint dataSet in BIRT that will do the same thing.

NewInBirt

mwilliams

You could write computed columns to merge the broken columns into a single column. Or, you could probably just make one scripted dataSet that runs both vehicle types rather than making them separately.

NewInBirt

Thx, have you got an easy example in your storage?

Or is it possible that I send you this simple example in form of a rptdesign-file and where you add your proposal?

For me it isn't easy to understand, how it is possible with computed columns.

thx, bye
NewInBirt

mwilliams

If you send the simple example, I'll modify it. You can use a computed column to see if there is a value in "vehicle: tableA", if not, then there is one in "vehicle: tableB" for you to use. Same for the others. You'd just do if/else statments.

mwilliams

If you send the simple example, I'll try to modify it in both ways I stated above. You can then choose between them.

NewInBirt

Hi,

thank you in advance, Michael.

An essential requirement is, that I don't know the content of the tables in reality.
In the example I know it

The best way would be if there is a UNION feature

I read with Actuate 11 there is an union data set feature. Do you know when this feature
will be implemented in OpenSource BIRT?

Bye
NewInBirt

mwilliams

I don't think that feature will be in the open source version anytime soon, but who knows! You don't know the content, but you know the columns, correct? So, you'd know what columns to join/stack?

Also, what is your BIRT version?

NewInBirt

Yes, correct, the both tables have the same count of columns and the same names and I know what columns have to be joined.

My BIRT version is 2.6.0

mwilliams

Your example was almost too simple to show the idea of joining just using a scripted dataSet, but maybe you'll get the idea from what I did. There is also just a joint data set using the birt joint dataSet functionality. Let me know if you have questions.

NewInBirt

Hi Michael,

thank you very much for your help.
I will test it tomorrow if it suits with my use case,
but your solution is looking good.

I will inform you.

Thx and have a nice day
NewInBirt

mwilliams

Not a problem. Let me know if you need a more complex example of doing a single scripted dataSource out of two sources.

ManInBlack

That is really interesting.

NewInBirt

Have you got a complex example with the single scripted data source?
In the last example you write the data direct within the loop of the fetch-script of the data set.

How can I access the data of the both data sets?

Thx+bye
NewInBirt

mwilliams

NewInBirt,

You would essentially just have to bring in both arrays or objects of data in the open script and check their size. Then, you'd figure out how large the combination of the two is, when you hit that size, you'd return false in the fetch method. Until you reach the length of the first data object, you'd keep filling your dataSet with that data. Once you've met the size of that data object with your incremented variable, you'd move onto the next data object until you hit the total size that you found and then you'd be done. Hope this helps. If not, I'll try to set up a simple example using two string arrays that I'll create in the open method. Let me know.

NewInBirt

Dear Michael,

thank you again. It is really great, that you support me.
All right, I understand what your approach is.

But I have one big problem, my data will be build in the both data sets with complex queries to different databases and over some loops the data set will be build. And in the open-script there are to many business logic in order to generate the data objects. This business logic is different in both data sets.
(My example was rather too easy).

The data exists in each scripted data sets. Is it possible to create a third scripted data set where I get the data form the other two data sets? Or is it only possible to create the UNION over the java POJO?

I am attaching a better example where the data comes over POJOs.

Best regards
NewInBirt

mwilliams

NewInBirt,

Another possibility would be to set a hidden text element for each of your 2 initial dataSets at the top of the report (to be sure they are ran first). Then, in the onFetch of each of those dataSets, you can store the values into a new array. Then, you should be able to use this array to create a third scripted dataSet, I think.

Jenny

Hello NewInBirt,

I can't also find a solution to get access the data set over scripted data sets.
I also created a new thread:
(http://www.birt-exchange.org/org/forum/index.php/topic/21734-how-to-access-a-data-set-over-a-scripted-data-set/)
Maybe somebody here in forum can find a solution.