Inner Join with multiple fields

Hi,

I am new to BIRT. I have created two dataset say DS1 with fields f1,f2,f3, f4 and DS2 with p1,p2,p3 fields. Now I want create one join dataset where I need to map f1 and f2 with p1, p2 of both DS1 and DS2 dataset. I come to know BIRT has limitation of Single Column Equity in Join. Is there any alternative to join two dataset with multiple fields

Any help appreciated.

Regards,

Vikram

Find more posts tagged with

Comments

mwilliams

Vikram,

I haven't done it, so I don't know for sure, but you might be able to do something for this using a scripted dataSet. Would probably be quite a bit of work on the scripting end.

paulk

If your datasets are pulling from a database, you can use the database's query code to perform the join rather than the BIRT interface. 
 
An example using a JDBC SQL data source: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT ds1.f1, ds1.f2, ds2.p1, ds2.p2 FROM table1 AS ds1 INNER JOIN table2 AS ds2 ON (ds1.f1 = ds2.p1) AND (ds1.f2 = ds2.p2)
</pre>
Using this method also allows for complex joins using more than 2 tables and using OR instead of AND in the ON clause. Other database languages like Oracle allow similar statements. 
 
 
Another option to try (which I have not tested) is modifying the XML BIRT creates for the joined dataset. It creates something like this to represent the join criteria: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
<list-property name="joinConditions">
<structure>
<property name="joinType">inner</property>
<property name="joinOperator">eq</property>
<property name="leftDataSet">DS1</property>
<property name="rightDataSet">DS2</property>
<expression name="leftExpression">dataSetRow["f1"]</expression>
<expression name="rightExpression">dataSetRow["p1"]</expression>
</structure>
</list-property>
</pre>
 
Change the expressions with a custom calculation like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
<expression name="leftExpression">dataSetRow["f1"]+","+dataSetRow["f2"]</expression>
<expression name="rightExpression">dataSetRow["p1"]+","+dataSetRow["p2"]</expression>
</pre>
The middle "," is so BIRT concatenates the values rather than attempting to add them as numbers or some other thing. 
 
Maybe Michael has more time to test this potential functionality bypass. 
 
Hope this helps point to a solution.

birtseeker

Thanks all for reply.

I am developing report for EPR LN where they provide connector to retrieve data from database so I can not use database directly. Regarding second option, I tried this option but when I concatenate 2 fields in expression on Ok button it automatically select random fields from the two dataset and it won't work.
Then I creted two computed fields in two dataset by concatenating two fields and then select these two computed fields for Join condition. It is working fine with preview as I have set default values both dataset. Now when I add any field into the report layout it is giving error as "Retrieving binding data error". I am struck here.
Is it ok to join two dataset on the Computed fields?

Any help appreciated.

Regards,

VIkram