With the upcoming Callisto release BIRT 2.1 will support Joined Data Sets. Up to this point BIRT has supported multiple data sources, but did not present an easy way to join data from these data sources. If I were building a BIRT report that encapsulated HR data from an Oracle database and Financial data from a MS-SQL server, combining the data in a single output table was very cumbersome.
To address this problem the BIRT team has developed a new type of BIRT Data Set that allows joining of traditional data sets. The Joined Data Set allows the above scenario to be accomplished using Inner and Outer Joins between two data sets. A common key is defined, for join purposes, in the Data Set wizard.
Eg.
For simplicity I will use two CSV files as data sources. The CSV files have the following structure.
Rep_Region.csv
region, sales_repr
East, Rep_A
East, Rep_B
Central, Rep_C
Rep_Sold.csv
sales_rep,quantity_sold,quantity_purchased
Rep_A, 20, 22
Rep_B, 33, 44
Rep_D, 10, 10
The first file, lists the region and the sales rep. The second file lists the sales rep, the quantity of widgets sold and the quantity of widgets purchased from a supplier. Rep_C exists in the first file and not the second. Rep_D exists in the second but not the first. Also notice that the sales rep column name is different in the two files.
Create the first data set in BIRT using the CSV data source.
Create the second data set in BIRT using the CSV data source.
Create the new joined data set by right clicking on Data Sets and choosing “New Joined Data Set”.
In the Data Set Wizard, map the sales_rep column from our first CSV file to the sales_repr column in the second CSV file. The data sets can then be joined using an Inner, Left Outer, or Right Outer Join.
Additionally, joined data sets support Computed columns and Filters.
After dragging the new data set onto the report the following output is displayed.
Inner Join.
Left Outer Join.
Right Outer Join.
This feature is available today in the 2.1 RC3 download.
Wednesday, May 24, 2006
Joined Data Sets with BIRT 2.1
Subscribe to:
Post Comments (Atom)
11 comments:
How do you custom extend a dataset so as to restrict what a user can see. I mean, how can restrict a user from seeing data that he is not supposed to see when viewing a birt report ? All I have in the context is a username at the point of executing a report
You could modify the sql query at runtime to append a where clause using the username. Is this what you need?
Jason
Sory for this OOT question, but I got trouble with this. Can we make dynamic sql query for the dataset?
I need something like a dynamic "Where" clause... I need to make a report that ask user which field used by user to query the data.
Yes you can do that. You can modify the query using property binding or script. Take a look at this example:
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/querymod/index.php
http://www.eclipse.org/birt
/phoenix/examples
/reports/birt2.1
/querymod/index.php
Hi,
i'm working with BIRT 2.3.1 and Joint Data Sets. The designer won't let me drag/insert a Joint Data Set in my report. The preview for the joint data set works great but i can not drag it in the report neither use it to create another Joint Data Set.
Any idea? Thank you.
What os are you using? Also have you tried 2.3.2?
I'm using Windows XP Pro. I will try BIRT 2.3.2.
But in your example, you're using an older version (2.1) aren't you?
I don't understand why the designer don't let me insert a joint dataset. Do i need to modify something in the default output columns? Even when i try to use a joint data set in a new joint data set, no columns are displayed in the wizard.
Help...
Thank you..
I tried to create a joint data set (still in 3.2.1) from two data sets that don't contain parameters. I was able to insert the joint data set in my report.
It seems like the designer won't let me insert a joint data set of data sets that contains parameters. Is this a limitation?
Thank you.
Alright,
I've just tested with 2.3.2 and it works. I can drag the joint data set to insert it in the report. I can even use the joint data set for creating another joint data set. I can see all the output columns. :)
here is my post with the sample report:
http://www.birt-exchange.com/forum/designing-birt-reports/14359-problem-inserting-joint-data-set.html
Thank you.
Here is the bug link :
https://bugs.eclipse.org/bugs/show_bug.cgi?id=251178
Skandy.
Post a Comment