How to dynamically change datasources on different databases using prompt macros

[xyz]

Hi All,

We have a requirement where we have to prompt user while running the reports to choose different data sources based on his selection report has to execute. Here we are using two sets of different data sources, where two data sources are pointing to SQL Server 2017 database and the other two data source are pointing to Oracle 2018 database and Cognos 11.1 R1 Analytics we are using. The reason for using two data sources is, we have tables from two different schema's in the Oracle as well as SQL Server databases.

For me it looks like only one option to use prompt macros in the FM to prompt the user at run time for data source selection.

In model in Query subject definitions where ever we have data source extension we have to use prompt macros
I followed the below link.
https://www-01.ibm.com/support/docview.wss?uid=swg21374654

The problem I am facing here is, as we have two data sources pointing to same database, and as I am using prompt macro it is only caching the last default value of only one schema, so the report is error out as it is unable to point to other schema details. Below is the error I am getting.

RQP-DEF-0177
An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".ORA-00942: table or view does not exist.

But, if my report points to only one data source instead of two data source to either Oracle or SQL Server, report is working fine after I provide Data source and schema information.

Can you guys please let me know your suggestions/ideas?

Thanks in advance

Thanks & Regards,
XYZ