Cognos: Dynamic database selection

Sometimes we may need to retrieve data from similar tables created in different database systems. Cognos allow us to select tables dynamically.

In this example we are going to retrieve data from tables created in different databases, Netezza and MySQL. 

1) Create two ODBC data sources, one for Netezza and the other for MySQL.

2) Create a data model in Framework Manager. select either Netezza or MySQL  data source.

3) Select the tables which you want to include in your model.

4) Now select the Data Source and set the below properties:

Content Manager Data Source:  #prompt('cm','token')#

Catalog:  #prompt('db','token')#

5) Create and Publish the package.

6) Create a report in Report Studio. 

When you run the report it will ask to enter the values for the parameters 'cm' and 'db'. Here 'cm' is the name of datasource created in Cognos and 'db' is the name of the database.

Please note that the table name and structure should be same in both databases, i.e., Netezza and MySQL.