How to use 2 different database connections and SQL queries from the same report.

Question:

1) I have two databases one of SQL and other of Oracle

2) Both of the databases have the same tables and same views.

3) I have different websites for different database

Scenario 1)

If i create a report using oracle datasource how to use the same report for the SQL data source?

Scenario 2)

I have deployed the oracle report into my oracle website. Do you think I would be able to use the same report on MySQL version of my website?

Answer:

Please see the suggestions below.

Scenario 1), report that can use oracle or mssql data source:

This can be achieved with the Data Adapters.

You can:

- define a data adapter property in your jrxml report

For example:

Please refer to the screenshot:

- upload the report to repository and during uploading

procedure you have to select "No data source"

- if you would like to change this data source you should update the jrxml

report and update the aforementioned property

I have attached to the article an exported sample of the report

with this property.

Scenario 2), SQL that depends on type of your database.

The general steps to achieve this is to use a set of parameters.

- the first parameter should define a database type

- the second parameter should pass SQL to the database and depends

on the value in the first parameter

You can find a sample of the usage of cascading parameters

(second parameter is defined according

to the user selection of the first parameter)

in the folder:

/organizations/organization_1/adhoc/topics/Cascading_multi_select_topic

Please see the location of this sample in the repository in the screenshot below:

User Feedback