One report - multiple JDBC datasources

[mdaleiden]

We have four MySQL databases with identical structures and we need to consolidate the data from each of these into a single, one-page report.

 

Tips/suggestions for implementing this would be greatly appreciated.

[javydreamercsw]

Tried making a sub report which only difference is the data source?

Post edited by: javydreamercsw, at: 2006/08/07 20:24

[mdaleiden]

I am not that familiar with JasperReports and subqueries, so I do not quite understand your response. How do I do what you are suggesting?

[javydreamercsw]

A subreport is basically a report inside a master report. Each report can run on different datasources.

[javydreamercsw]

A subreport is basically a report inside a master report. Each report can run on different datasources.

[pcvito09]

Hi,

I have a similar question: I have 3 databases (dev, acc and live) and I want to run one and the same report 3 times but with the different DB connection. Is there a simple solution for this ?

I do not want to make 3 copies where only the data source is different.

Any ideas ?

Thanks.

 

[szaharia]

Hi,

You could use a report parameter to specify the database type (dev, acc and live), and depending on this parameter value you could switch between related connections at fill time.

HTH,

sanda

[pcvito09]

Hello.

 

Thanks for the answer.

I was also thinking about using a parameter but the problem is I do not know how to use the value of the parameter to specify which database to use.

There are 2 parameters REPORT_CONNECTION and REPORT_DATASOURCE but they are read only.

Regards,

Patrick

[szaharia]

Hi Patrick,

Using JR builtin parameters has no meaning here. In such a case you only need a method/procedure to collect the parametrized user input before filling the report. Then you can choose your connection depending on this param value.

Hope this helps,

sanda

Code:

public String getUserConnectionType(){  String user_option = "dev";    // detect the option chosen by the user here   return user_option;}at fill time:Connection conn = getUserConnectionType().equals("acc") ? getAccConnection() : (getUserConnectionType().equals("live") ?  getLiveConnection() : getDevConnection())JasperFillManager.fillReportToFile(the_report, parameters_map, conn);

[pcvito09]

Thanks.

As far as I understand is this only possible when calling the report from Java code, is that right ?

I want to set the connection within iReport, maybe in a scriptlet ?

The strange thing is that I have to use fillReportToFile since I am in the report file (in iReport).

I am not so familiar with scriptlets.

Patrick

[szaharia]

Patrick, your last reply added a new and interesting element to our discussion: the iReport tool. If you are running reports from within iReport only, hardcoding connections in java is no more necessary.

Until you wrote about the iReport here, I was convinced that your report is probably running from within a java/web application that has to distinguish between various user input. In this case, in my opinion, establishing different db connections at design time is not possible, so the most common solution relies on writing a piece of java code in the java app. At the moment I'm not sure if it's possible to choose between data sources using a scriptlet, but maybe someone else could provide a clue here.

Things are a lot simplified if you are running the report from iReport only. In this case you have to define 3 different data sources in iReport (the dev, acc and live ds) and, when running the report, just select the desired data source from the available data sources list before clicking the preview button. That's all. There's no java code to be written, no parameters to be set, because iReport does it for you.

Kind regards,

sanda
 

[pcvito09]

Hi Sanda,

 

I am afraid I did not explain it too well, sorry.

First of all I understand what you mean with the example code by using the FillReport method.

Whenever I want to start the reports from a Java program I can use it but that is not the case.

I want to run the reports from a web server but I want to have 1 report for all the three environments (dev, acc, live) where I can select the required DB.

Now I have 3 copies of the reports on my Jaspersoft server and I specify the DB at the properties level of each report.

I want to have for instance the possibility at startup of the report to select the environment and this selection causes to set the correct DB, that is what I meant within iReport.

Regards,

Patrick