Support Article
Alternate database option in Report Definition is not working
SA-31677
Summary
The alternate database option in report definitions does not work. When an alternate database is used, the data is pulled from the default database instead of the alternate datasource.
Error Messages
Not applicable
Steps to Reproduce
1) Configure two different database connections for a ORACLE DB.
2) Specify the DB Names for Main and Reports DB in the Data-Admin-Database-Table for the class.
3) Run a report definition with "Alternate Database" checkbox enabled.
Notice that Tracer also the DB query running against the main database.
Root Cause
User is using ORACLE database, which uses "username" as the schema name.
Alternate schema works only when schema of the main and reports database match.
Resolution
Note that the "Alternate database" option works:
1. When the main and the reports database use the same schema.
(OR)
2. When the schema for the reports database is specified in its database definition.
Data-Admin-Database-Table provides option to specify schema for the main database, no such option is provided for the reports database.
Hence the schema for the reports database is picked from its Database rule's JDBC definition.
In case of ORACLE database the schema name defaults to the username. Since the user is using the same database (ORACLE) and two different schemas (usernames) this option does not work. User can address it by creating another database instance with the same schema name.
Published December 27, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.