best practice for creating same report for multiple databases

What is the best practice for designing a report that will be run against multiple databases.

One way is to create one design file for each database type and have the data source and query embedded in each file. But this will be a nightmare in terms of maintainability.

I can create a report and re-use the components from a library. But I am not clear on how I can avoid creating a specific report design file for each database type. Is there any way I can parameterize the data binding to each component?

Thanks for any info.

Find more posts tagged with

Comments

mwilliams

Hi ssen,

You can have multiple dataSources in a single report design. I'm not sure that you can parameterize the bound dataSet to a report item. I'd have to look into that a little more. However, you could create report items for each dataSet and use a parameter to determine the visibility of your report items. This may slow the report down though having it this way. Are you wanting to display data from multiple dataSources in a single report or would each report only include data from 1 dataSource?

ssen

Hi Michael,

The requirement is to display reports from a single data source. At present we have one report design for each datasource. Each report contains the same UI components bound to the specific data source.

I can move the UI components out to a library so that any changes to them is localized at one place.

But then I would have to have a set of n UI components each bound to the corresponding data source (so I have a set of n data sources in the library). The actual report design file would just refer to each type of these UI components. So, essentially, each report design file is a shell that points to certain elements in the library.

But this again would not be a good practice IMO - I am just taking the problem out of a report file to a library. What I really want is to use a single UI component, but somehow parameterize it with a specific datasource based on some configuration. This can either happen at run time or at design time.

I should be able to add more data sources as we add support for more databases. So all the queries and database related items are confined to the datasource items. But the UI component is not touched.

Right now, I see the UI component is tightly coupled with a datasource - unless we can de-couple that, I do not see how I can create a report without having a copy of the same UI component for a specific datasource.

Do you have any suggestions on how to achieve this?

Thanks,
Shantanu

Davolio

I don't know if this will solve your problem or this is your problem. But for one of our smaller products when we use birt we have the report data in a pre-existing view on the database so our query is just a select * from <viewname> . This of course works if you don't have a ton of datasets for each report or want to get the base information on the report (with small datasets).

So if we need data from an oracle, sqlserver, or informix database we don't have to rewrite the queries nearly as much. I would too be interested in other ideas for handling this as I doubt our practice is the 'best way' .

rick_gator

Let's say that you run a retail company with multiple store locations in different cities. You have a database server in each store that maintains the store's inventory. You also have a BIRT server at your home office.
Now you want to write a single report that can read data from a store that the user selects. So user1 may run this report for Atlanta and user2 may run the report for Seattle.

We have created similar reports using a parameter to prompt the user for the location(store). And then use their response to setup the JDBC connection to a database server. We won't know which database server until runtime.

Step 1: setup a report parameter called LOC where the user will select the location.
Step 2: setup the "dynamic" data source. Here are some values that can work for oracle.

BIRT JDBC DataSource:Driver URL:dbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=tcp)(host=servername.company.com)(port=1521)))(CONNECT_DATA =(SERVICE_NAME=atlanta.servername.company.com)))
*** this is where the magic is ***
Property Binding:JDBC Driver URL:"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=tcp)(host=servername.company.com)(port=1521)))(CONNECT_DATA =(SERVICE_NAME="+params["LOC"]+"store.servername.company.com)))"

In our case we append the users response to the first report parameter(LOC) to create the Oracle service_name.

RickC.