Filter Parent table based upon Child Data

I have a report with 2 data sources (Parent & Child), which point at different physical databases. Therefore (unfortunately!) I can't achieve what I want using SQL alone.
 
I have a Table which returns rows from the Parent data source, including a number of ID values which are used in sub-tables to retrieve data from the Child Data Source. For each Id value retrieved in the (outer) parent row, there will either be zero or 1 rows returned from the (inner) Child data source. This works fine, and I am able to see all of the required information in the report from both the Parent and Child data sources.
 
What I now need to be able to do is to filter the table based upon the contents of the data retrieved from the 'Child' Data Source. (i.e. Where <child_column> like '%Joe%') However, when I try filtering on the Parent table, then the 'child' columns aren't available. If I filter on the 'Child' tables only, then only the child data is filtered, not the related Parent row.
 
I have 'Googled' extensively, but the only answer I found was as follows:
 
"You will need to use an oncreate row script on the detail row of the inner table and an onrender script of the outer table's detail row to do this"
 
However, I don't have much experience of working with BIRT scripting, so I don't really know how to set up these scripts. Does anybody know what would need to be put into these scripts, or is there another different / better way to do this?
 
Many thanks in advance for your assistance
 
Ian

Find more posts tagged with

Comments

micajblock

Will joined data set work for you instead of parent child sections?

ibillingham

Mica,
 
Unfortunately there are multiple Ids (approx 10) in the 'Parent' data set, where I need to retrieve the detail from the 'Child' data set. Therefore, (I think) I would need to create multiple 'nested' joined data sets, as each join is only between 2 data sets:
 
[[[Parent <-> Child data for Column A] <-> Child data for Column B] <-> Child data for column C]
 
I'm sure this would work, but as each join would be an 'Outer join' (i.e. there may not be any child data), then I suspect that performance might suffer?
 
Is there an easy way to create a single joined dataset out of multiple datasets?
 
Ian

micajblock

Multiple data set join is a feature of commercial BIRT. In theory you could create the same extension point as we did with the commercial product. This is not trivial
 
I would first try using the nested join data set. If it meets your performance requirement then use that method. Most likely it will not be any slower (and maybe faster) than parent child method, as parent child will execute many smaller queries (every child row is a separate query). 
 
If it does not,provide sample data I can try and build you an example.