Creating a report using multiple data sources

[slau_1]

Hi there,

I've created a simple report with a single data source.

For simplicity, the query looks something like:

select last_name, salaryfrom employees_db_1where employee_id = $P{employee_id)[/code]

So the user will need to enter the employee_id and then the name and salary for that employee will be displayed.  No issue with that.

Now, I'd like the user to enter the employee's nickname as the input instead.  However, employee nicknames reside in a separate data source.

Basically, the result from the following query needs to be passed as the input parameter to the original report:

select employee_idfrom employees_db_2where employee_nickname = $P{employee_nickname}[/code]

I've read tutorials that indicate I should create a new report and then include the original report as a subreport.  But I'm not clear on how the query output from one data source can be used as the query input in another data source.

Thanks in advance for any assistance that you may provide.

[hozawa]

Instead of using subreport, you may want to look at using Scriptlets to set the employee_id parameter

[sanbez]

Solution with subreports:

Main DataSource based on second query. At this case main report contaion field $F{employee_id}
Make subreport with parameter $P{emp_id} and put subreport into detail section of main report. Subreport query like this: select last_name, salary from employees_db_1 where employee_id = ${emp_id}

Pass field of main report $F{employee_id} into subpreport as value of parameter $P{emp_id}.

...but... I think that subreport is over-powered solution of this problem. You can use one query

select db1.last_name, db1.salary from employees_db1 db1, employees_db2 db2 where employee_nick_name = $P{employee_nickname) and db2.employee_id = db1.employee_id

Subreport is not need at this case