How to Dynamically Point Domain to Data Sources with Different Schema Names Based on Logged in User

Use Case Overview

This approach is useful in a scenario where you have multiple databases with the same table structure but different schema names, and need to point your TIBCO JasperReports^® Server Domain to different Data Sources depending on logged in User, or depending on an Organization which the logged in User belongs to.

The Solution

Since JasperReports Server v6.1.0, there is an option of using Attributes in data source definition. An Attribute can be defined at global, Organization or User level and assigned a value (or comma separated list of values). A common use case is to create an Attribute with the same name but different values for each Organization and use it in a Data Source definition. This data source will be used for the Domain, and then will connect to different databases depending on what Attribute value corresponds to current User.

More information on defining data sources based on attributes can be found in our JasperReports Server Admin Guide:

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-administrator-guide/v640/attributes-data-source-definitions

Our case, however, is a little different, because we have different schema names. Still, it is possible to dynamically specify database schema name in Domain XML file using Attributes. The way to achieve this is as follows:

• create a Domain using any JDBC or JNDI Data Source, as long as the table structure in database is consistent with other databases

• export the design of your current domain WITH schema name as prefix:

  

• in the Domain schema XML modify the database schema entry by replacing the static value with a reference to a User's attribute. In the example below 'schema_name' is the name of User Attribute which is assigned different schema names for different users:

  

• save the changes

• now open the existing Domain or create a new one from JasperReports Server repository interface

• select to upload the schema you just modified:

  

• Submit the changes. DO NOT open the Domain Designer

Now when you are going to open Ad Hoc Designer using this Domain as a data source, the schema should be picked up from the attribute of the current user.

A Word of Caution

The drawback of this approach is that manually adding attribute reference to the XML design of a Domain is not supported by the Domain Designer, so you will not be able to modify the Domain from web interface without breaking the schema reference. If you need to modify anything in the Domain, you'll have to update the database schema reference and re-upload the XML again.