How to change the table name in reports at run time?

Hi All,

I have the fresh entry into this forum.

I am developing reports for SQL server agent. In my design, i need to support for all the databases. But

some of table names are different in different databases.

In one of my report I am trying to replace the table name based on the database connection. For example, in

DB2 and SQL I am using the table namely "MS_SQL_Lock_Resource_Type_Summary". But the table name is different

in Oracle, i.e., "KOQLRTS".
In run time the table name has to be replaced based on the connection to database, that is if i connected to

Oracle the data should fetch from "KOQLRTS" table, if i connected to DB2 or SQL then the data should fetch

from "MS_SQL_Lock_Resource_Type_Summary".

Please help me to achieve this, using any sql query or java script.

Thanks in advance.
Santhosh

Find more posts tagged with

Comments

g2kuong

Hi Santhosh, 
 
You may find Jason Weathersby's post on how to 
Swap Data Sets at Runtime useful, he basically gives the following methods:: 
 
" 1. JNDI - Look up the data source. 
Property Binding - Swap the data source at runtime using property binding expression. 
2. Script ? Write a script in the beforeOpen event handler to modify the data source properties. 
Connection Profiles ? Store the connection information in a shared profile. 
3. DTP driverBridge extension ? Implement a driverBridge extension to intercept calls to a specific driver. 
4. Design Engine API (DE API) - Swap the dataset property on a specific table before running the report. " 
 
Read here: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2008_05_01_archive.html'>BIRT World: May 2008</a> 
 
Example code: 
<a class='bbc_url' href='http://www.birt-exchange.com/devshare/designing-birt-reports/366-swap-data-set-based-on-the-designer/#description'>Swap Data Set based on the Designer - Designs & Code - BIRT Exchange</a> 
 
Regards, 
Anthony Ku Ong 
 
<blockquote class='ipsBlockquote' data-author="Santhosh">Hi All, 
 
I have the fresh entry into this forum. 
 
I am developing reports for SQL server agent. In my design, i need to support for all the databases. But 
 
some of table names are different in different databases. 
 
In one of my report I am trying to replace the table name based on the database connection. For example, in 
 
DB2 and SQL I am using the table namely "MS_SQL_Lock_Resource_Type_Summary". But the table name is different 
 
in Oracle, i.e., "KOQLRTS". 
In run time the table name has to be replaced based on the connection to database, that is if i connected to 
 
Oracle the data should fetch from "KOQLRTS" table, if i connected to DB2 or SQL then the data should fetch 
 
from "MS_SQL_Lock_Resource_Type_Summary". 
 
Please help me to achieve this, using any sql query or java script. 
 
Thanks in advance. 
Santhosh</blockquote>