* How to Implement dynamic data source in Jasper?

 

Jasper doesn’t support dynamic data sources directly. Instead, it lets users define a custom data source using API by setting connection properties, such as JDBC URL, user name and password, dynamically according to the specified condition. For example:

 String userName = userDetails.getUsername();
 // obtain a connection based on the username.
 String dataSourceURI = "";
 if (userName.equalsIgnoreCase("admin")) {
 dataSourceURI = "/datasources/ds_1";
 userName = "xx";
 …
 }else if{…}
 connection = getRepositoryDatasource(dataSourceURI);
 try {
 parameterValues.put(JRParameter.REPORT_CONNECTION,
 connection.getDataSource().getConnection());
 } catch (SQLException sqle){
 sqle.printStackTrace();
 }

API succeeds in doing that, in a complicated way. A byproduct is that the user-defined class is tightly coupled with the application, which makes the code difficult to maintain.

Implementing dynamic data sources would be rather easy if you had used esProc to do it. Let’s look at an example. A company with a huge number of transactions stores business data by year in a number of databases on an independent server. To search data (the orders table, for example) in a certain year, it needs to connect to the proper database dynamically:

And esProc SPL (Structured Process Language) gets it done with a two-line script:

A B C
1 =connect(dataSource) /get dataSource connect
2 =A1.query(“select * from orders”) /Execution query

connect(dataSource)” establishes database connection; “dataSource” is the esProc parameter that receives a passed-in year value to enable connection to the proper database.

The question is basic data retrieval. It’s more difficult or impossible for Jasper to handle computations involving multiple or different data sources. esProc’s step-in can make the handling of the situation easier, even effortless. Read Aggregate Queries after Database Split to find more examples.

esProc provides JDBC driver to easily integrate into a reporting tool. See How to Call an SPL Script in JasperReport to learn details.

About esProc installation, free license download and relevant documentation, see Getting Started with esProc.