data combined from different data sources

Hi,

I need to create a report based on queries from 2 different data sources. I'd like to display a line for each row returned from the first data source combined with associated information returned from the second data source if any.

I figure this will have to be done in event scripts using javascript and/or java. Can anyone provide a rough overview of the steps I need to take?

I'm using BIRT Designer Version 2.3.2

Kind Regards,
scowsailor

Find more posts tagged with

Comments

bhanley

Do you have a way to join the two data sources together through some common element? Creating a joint data source will give you all your data in one set and save you doing any scripting at all.

scowsailor

Do mean via a natural key like order number? Yes.

I already do some scripting to set the schema(?) for the FROM clause. I'm using MS SQL Server '05. At least one data source's db is configured with multiple schemas that all have identical tables, and the schema choice is passed in as a report parameter.

scowsailor

The Joint Data Set is a cool feature. Thanks for pointing that out. My join however is on at least three fields, and I'm struggling to figure that out along with passing the schema argument in for one of my data sets. Still seems like I'll need to script this some how.

SailRCG

Hi ScowSailor,

It's possible to join joined data sets to other 'base datasets', 'joined datasets', etc... .

scowsailor

Thanks, SailRCG. I was going to try messing around with that. Right now I'm still trying to figure out the multi schema delema. The schema is a report parameter that equates to a facility id. The way I'm using this is to override my dataset sql during the beforeOpen event by scripting the sql like:

schema = reportContext.getParameterValue("p_facility");

this.queryText = "select T1.col1 from "+p_facility+".table1 T1";

This works great, but I'm not sure how I'll be able to use the dataset join feature if I have to do the above.

I wonder if there's a different way to do the schema thing such that I don't have to actually specify it in the query string of the dataset.