Multiple datasource in one report

[HBK32]

Hi All,

I come across requiremt in which I need to connect to the multiple data sources in one report.

Can some one give me example/code on how to pass/connect to multiple datasources in one report?

I will really appreciate the response.

Please reply me ASAP. It really urgent.

Thanks.

[lshannon]

Using subreports is a good way to query more than one datasource since the datasource expression for the subreport can be a JDBC Connection (which can be passed in as a parameter to the main report at fill time) or a JRDataSource object (which can be created in a separate java class).

[HBK32]

lshannon wrote:

Using subreports is a good way to query more than one datasource since the datasource expression for the subreport can be a JDBC Connection (which can be passed in as a parameter to the main report at fill time) or a JRDataSource object (which can be created in a separate java class).

 

Hello,

 

Thanks a lot for your response.

If possible can you please give an example for passing datasource connection as a parameter to main report?If possible do attach sample jrxml file for the same.

 

I will really appreciate your response.

 

Please reply me ASAP. Its really urgent.

 

Thanks.

[szaboaz]

In index.jsp lines 35-43 and lines 52-57 are for the usage of datasources in relation of a subreport.
I used datasources for both the master (masterreport_ds.jrxml) and the subreport (subreport_ds.jrxml).
Subreport's datasource is put into the parameters of the master report, and this very same parameter is set as Data Source Expression in the subreport element of masterreport_ds.jrxml.

Lines 59-64 (now commented out) demonstrates the usage of subreports without datasources (using only the Connection object). This time, masterreport_sql.jrxml and subreport_sql.jrxml contain their own sql query string.

If you watch the two result pdfs carefully, you can notice one important thing about datasources:

result_with_connection.pdf contains three records from master report, and every record has its own subreport section containing several records from the subreport.

However,

result_with_datasources.pdf contains the three records from the master report also, but only the first record has its own subreport section, the second and third one doesn't.

This is because the subreport datasource is consumed first time.
If you want to give a datasource to a subreport, you have to be careful, to assure that your subreport will appear only once.

Best regards,
Arpad [file name=subreport_with_datasource.zip size=16476]

Post edited by: szaboaz, at: 2008/07/16 09:30
Post edited by: szaboaz, at: 2008/07/16 09:32

[HBK32]

szaboaz wrote:

In index.jsp lines 35-43 and lines 52-57 are for the usage of datasources in relation of a subreport. I used datasources for both the master (masterreport_ds.jrxml) and the subreport (subreport_ds.jrxml). Subreport's datasource is put into the parameters of the master report, and this very same parameter is set as Data Source Expression in the subreport element of masterreport_ds.jrxml. Lines 59-64 (now commented out) demonstrates the usage of subreports without datasources (using only the Connection object). This time, masterreport_sql.jrxml and subreport_sql.jrxml contain their own sql query string. If you watch the two result pdfs carefully, you can notice one important thing about datasources: result_with_connection.pdf contains three records from master report, and every record has its own subreport section containing several records from the subreport. However, result_with_datasources.pdf contains the three records from the master report also, but only the first record has its own subreport section, the second and third one doesn't. This is because the subreport datasource is consumed first time. If you want to give a datasource to a subreport, you have to be careful, to assure that your subreport will appear only once. Best regards, Arpad [file name=subreport_with_datasource.zip size=16476] Post edited by: szaboaz, at: 2008/07/16 09:30 Post edited by: szaboaz, at: 2008/07/16 09:32

Hello,

Thanks a lot for the response.

In my case, master report query is against SQL server DB & subreport query is against Oracle DB. So in that case I need to open two DB connections in JSP.

SQL server DB connection will go to master report at fill time & Oracle DB connection for subreport need to pass as a parameter to master report at fill time.

I have defined one parameter with type of java.sql.Connection in master report for the oracle DB connection for subreport. this very same parameter is set as Data Source Expression in the subreport element of master report.

Master report & sub report will have their own queries & there will be a common field with same data type & value from master report which will connect query in the subreport.
e.g subreport query will be select * from test where dept_id = $P{MasterReportDept_ID}

Please find attached JSP & JRxml files for the same & let me know where I am doing wrong in it.

I will really appreciate your response.

Please reply me ASAP. Its really urgent.

Thanks. [file name=Test-3819e8ab74998b012570d0135bdcbb72.zip size=3533]

[HBK32]

lshannon wrote:

Using subreports is a good way to query more than one datasource since the datasource expression for the subreport can be a JDBC Connection (which can be passed in as a parameter to the main report at fill time) or a JRDataSource object (which can be created in a separate java class).

Hello,

Thanks a lot for the response.

In my case, master report query is against SQL server DB & subreport query is against Oracle DB. So in that case I need to open two DB connections in JSP.

SQL server DB connection will go to master report at fill time & Oracle DB connection for subreport need to pass as a parameter to master report at fill time.

I have defined one parameter with type of java.sql.Connection in master report for the oracle DB connection for subreport. this very same parameter is set as Data Source Expression in the subreport element of master report.

Master report & sub report will have their own queries & there will be a common field with same data type & value from master report which will connect query in the subreport.
e.g subreport query will be select * from test where dept_id = $P{MasterReportDept_ID}

Please find attached JSP & JRxml files for the same & let me know where I am doing wrong in it.

I will really appreciate your response.

Please reply me ASAP. Its really urgent.

Thanks. [file name=Test-f1b9402295be099b552ffbb9f739ad2a.zip size=3533]