Operational reporting (real time reporting) is an important part of the company software. The cost involved on Real Time Reporting can be high as it affects the existing system transactional capabilities.
On the other hand client reporting is usually done with data stored in a data warehouse. The data in there has certain delay and gets populated sometimes even just daily.
So the first task when designing reports is identifying which data can simply be refreshed from time to time and which one must be done in real time.
Once we have identified the data sets that must be generated in real time then we run into a new issue, some data must be joined but they are in a non linkable sources or even worst some of the data to be joined comes from an external application for example a Web Service. In those cases you will need a custom reporting solution.
A good custom reporting solution must provide the the best trade between high data availability for reporting and good application performance.
Resources are not unlimited and it is crucial that we use those we have at highest as possible percentage. The MVC pattern is to be applied to any software with a User Interface (UI) and I say UI and not GUI because even in the case of Console applications you still have a View.
If your needs are just pulling information from one existing database and you can live with just SQL then any Report Designer will be able to easily use any report utility like iReport to generate even the more complex reports you can imagine.
Of course real world is far from that. You need to pull data from different databases, some of them data warehouses and some of them real time application databases. You need data from other sources like Web Services, excel, text files and even (God forbid) pdf documents.
Only a high level language can come to your rescue to get Real Time Reporting in place.
The architecture
The purpose of this post is to document one implementation using Jasper Reports. I will show how real time reporting can be done while still separating the concerns of visualization, data and logic.
Take a look at the below diagram
Regular users see a list of reports generated from files in the file system. The files follow a convention
_name.jrxml. They are Jasper XML files. The user selects a report and a form shows up asking for parameters or if not parameters are needed a pdf will be returned with the contents of the report.
Report Designers use iReport tool. They build reports containing subreports. They use parameters to communicate from the main report to the subreport or to customize dynamically the necessary data sources connections. They use a connection to a local database that is built following the indications from a Java Developer (from files daily.sql and daily_data.sql for example). When they are satisfied with the result they copy reports and subreports in a specific file system path. For example rt_daily.jasper will be generated from rt_daily.jrxml. The jrxml is maintained in SCM of course.
Java Developers build code that (look at the numbers in the diagram):
1. Decides which connection to supply to the main report. The data source comes as part of the report name. For the example it is “rt” which means realtime and so a local sqlite database will be populated using the same metadata iReport designers used for their tests (daily.sql). It can be a connection to a non realtime database for example a CRM database. Regardless of what connection we supply the “realTimeDbPath” will be passed to Jasper in the case any subreport needs a realtime connection.
2. Runs a Service#populateDb() method following a convention like for example “DailyReportService”. This service is in charge of preparing the local sqlite with all needed datasets for either the main report or subreports.
3. Resolves the name of the compiled report file to pull from the file system (rt_daily.jasper)
4. Supplies connection, parameters and jasper file to the Jasper Report Engine to get a pdf file with the results of the report. All parameters supplied as part of the form are supplied to the Jasper Engine BTW. This allows to minimize coding but also imposes a security concern. Be sure you do not rely just on parameters but on internal security at services layer. Spring Security with the help of AOP is ideal for this.
MVC pattern respected
View: (Report writting)
Ideally someone that knows how the report should look will take care of this layer. This person just cares about the organization and layout of the data. One important assumption should be made at this point: The report writer shouldn’t necessarily be a DB developer, a High Level Language (HLL) developer (like Java, C# developers) or any other technical person. The report writter should have available certain datasources to visualize his report. The writer should be familiar with basic SQL concepts. The writter can be and should be IMO a Business Analyst (BA)
Model: Data
To get the report your company needs you will probably need to dig into Excel, SOAP services, text documents, databases, XML you name it. It makes sense though that this data gets translated to fixed tables from where the person in charge of the View could easily build the report. A DB developer is the best fit for this layer. A HLL programmer is a good fit as well. A BA can definitely build a reporting model as well. After all nobody better than him knows the dataset he will need per subreport.
Controller: Logic
An HLL developer will be needed for this layer. This is the layer in charge of all the plumbing between View and Model:
1. Uses a Services layer that in turn uses a DAO layer.
2. Implements security to determine which users have access to run which stored reports.
3. When the report is run it looks for the need of any real time data and if needed it populates it.
4. It invokes the Jasper Engine to run the particular report.
Local Environment
It is easier when everything is in a simple database and better when the data is de-normalized. For this document we are starting from three tables that will be de-normalized into just one. I am using MySQL here. Note that this is an example to illustrate complicated scenarios when you need to get data from different sources in just one data set. In reality if you have all you need in three different tables from the same database and you must provide real time reporting you are fine just pointing to the real database from ireport for developing the report and later naming the report with a proper datasource to be sure the needed connection is available at runtime.
Let us say that our original DB has three tables
CREATE TABLE `office` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`department_id` int(11) DEFAULT NULL,
`office_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_employee_office_id` (`office_id`),
KEY `FK_employee_department_id` (`department_id`),
CONSTRAINT `FK_employee_department_id` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`),
CONSTRAINT `FK_employee_office_id` FOREIGN KEY (`office_id`) REFERENCES `office` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
Let us say it has this data:
INSERT INTO `office` (name) VALUES ('Buenos Aires');
SET @office_id = last_insert_id();
INSERT INTO `department` (name) VALUES ('Engineering');
SET @department_id = last_insert_id();
INSERT INTO employee (first_name, last_name, office_id, department_id) values ('Pablo', "Cardone", @office_id, @department_id);
INSERT INTO `office` (name) VALUES ('Sao Paulo');
SET @office_id = last_insert_id();
INSERT INTO `department` (first_name) VALUES ('Marketing');
SET @department_id = last_insert_id();
INSERT INTO employee (first_name, last_name, office_id, department_id) values ('Ronaldo', "Gomes", @office_id, @department_id);
Our good practices sense tell us that for a report we are better providing just the whole dataset denormalized (just one table). We use sqlite3 for the local database. We must provide the necessary metadata and some initial data for iReport Designers. We will package metadata in a file called sampleEmployee.sql. This file will be accessible in the classpath of our application as we want to share it with report designers and ensure the same version is used from the application:
CREATE TABLE `employee` (
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`office_name` varchar(50) NOT NULL,
`department_name` varchar(50) NOT NULL
) ;
In a different file sampleEmployee_data.sql (not to be in the classpath of the application) we expose some sample data so the iReport users can start designing the layout:
INSERT INTO employee (first_name, last_name, office_name, department_name) VALUES ('Nestor','Urquiza','Buenos Aires','Engineering');
INSERT INTO employee (first_name, last_name, office_name, department_name) VALUES ('Pablo','Cardone','Sao Paulo','Sales');
INSERT INTO employee (first_name, last_name, office_name, department_name) VALUES ('Ronaldo','Gomes','Sao Paulo','Sales');
So locally our report writers can use the below command to get their testing data locally:
cd ~/
sqlite3 sampleEmployee.db < sampleEmployee.db
sqlite3 sampleEmployee.db < sampleEmployee_data.db
Designing the report with iReport
iDesiner is a visual tool that allows to design JasperReports. This is then your tool to create the Report Framework View side. I have tested this using iReport-3.7.4.
1. Open iReport. Go to preferences and be sure in classpath and point to sqlite3 driver file: sqlitejdbc-v056.jar. Of course you need to download that file if you do not have it. Just Google it.
2. Select File | New | Report | Blank | Launch Report Wizard. In Connections pick a new Database JDBC connection and use the below settings (You can always reconfigure this from the Designer toolbar clicking on the Report Datasources icon):
name: sampleEmployee
JDBC Driver: org.sqlite.JDBC
JDBC URL: jdbc:sqlite:/Users/nestor/sampleEmployee.db
3. Hit Test button (username/password both are blank) and you should get a successful message. Hit Save. 4. In Query(SQL) paste this:
SELECT first_name, last_name, office_name, department_name FROM employee
4. Expand “Fields” on the left and drag each of them to the Detail portion of the report.
5. From the reports element palette on the right drag labels to the Column Header section.
6. Click on Preview and the report should show the two records we added initially to the employee table. When you hit “Preview” Jasper compiles the .jrxml file into a .jasper file. This .jasper file will be deployed in a reports folder that the application reads so the expensive report compilation will be done by the iReport user and we will reuse it later from our backend.
Implementation
We use Spring for dependency injection however for Jasper Reports we do not use Spring. Jasper provides enough simplicity, that is all. First you will need to include some dependencies. As I use Maven all I have to do is to include the below in my pom.xml:
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
<version>3.7.4</version>
</dependency>
Real time reporting needs a local database but concurrency makes it prohibited to have just one. Ideally User A running the sampleEmployee report should not be affected by User B who is running the same report. This is not a problem if the database is of a warehouse type meaning it gets populated asynchronously. In our case though we need to be sure we create tables per user for those cases where the data must be populated synchronously (real time) We address this using an individual database per user session. So we will have several local databases like:
...
report_17A1B49C645D39C2F2BE4CD12B54AF75.db
report_E33819D245A598EDA01D1E3FC468EFE8.db
...
Each database will have a dataSource associated to it which is built on the fly by a DataSourceService. As you can see the local per user BD convention is “report_” + JSESSIONID + “.db” Given a URL like report/{dataSourceName}/{reportName} for example report/rt/sampleEmployee we can determine by conventions that the data source is to be built on the fly (instead of using one already injected by Spring). That is what real time (rt) stands for. The Controller can then instantiate a service following conventions (SampleEmployeeReportService) and then call a method populateDB() on it (of course it is implementing a custom ReportService interface) The service will use a DAO that will access the local sqlite DB, it will drop the table representing the report data set and it will populate with certain data that follows some business rules. The power here is unlimited as we play with a high level language like Java. Data can literally come from any place. The DAO uses Spring JDBC template to connect to the specific local user DB. It creates the metadata and fill out the table as well. Finally the Controller will invoke JasperReports to render the table content with the help of the sampleEmployee.jasper file created from iReport. Below is what I think is relevant from Java perspective. This is just a typical Controller class relying on injected Services.
@Controller
public class ReportController extends RootController {
@Autowired
Properties applicationProperties;
@Autowired
ReportDataSourceService reportDataSourceService;
/**
* A filename for reports is composed of two tokens
* <datasource>_<description> If datasource == 'rt' the datasource will be a
* local to the server sqlite db built on the fly
*
*
* @param request
* @param response
* @param result
* @param reportType
* @param dataSourceName
* @param reportName
* @param model
* @return
* @throws IOException
*/
@RequestMapping("/report/{dataSourceName}/{name}")
public ModelAndView run(HttpServletRequest request,
HttpServletResponse response,
@ModelAttribute("report") Report report, BindingResult result,
@PathVariable("dataSourceName") String dataSourceName,
@PathVariable("name") String name, Model model) throws IOException {
// Initialize the context (mandatory)
ControllerContext ctx = new ControllerContext(request, response);
init(ctx);
String sessionId = request.getSession().getId();
// Bind to path variables
report.setDataSourceName(dataSourceName);
report.setName(name);
report.setSessionId(sessionId);
report.setParams(ctx.getParameterMapWithEncoding());
// If real time type get the Service bean and populate DB
try {
// name = name.substring(0, 1).toLowerCase()
// + name.substring(1);
ReportService reportService = (ReportService) applicationContext
.getBean(name + "ReportService");
reportService.populateData(report);
} catch (Throwable e) {
e.printStackTrace();
}
// Get proper parameters for jasper
Map<String, String> jasperParams = getParamsForJasper(report
.getParams());
// insert parameters commonly used by most reports
String realTimeDbPath = reportDataSourceService
.getRealTimeDatabasePath(report);
jasperParams.put("realTimeDbPath", realTimeDbPath);
// Get the master report datasource
DataSource dataSource = reportDataSourceService.getDataSource(report);
// Get reports path
String path = getReportsPath();
File reportFile = new File(path + "/" + dataSourceName + "_" + name
+ ".jasper");
byte[] bytes = null;
Connection connection = null;
try {
connection = dataSource.getConnection();
bytes = JasperRunManager.runReportToPdf(reportFile.getPath(),
jasperParams, connection);
response.setContentType("application/pdf");
response.setContentLength(bytes.length);
response.getOutputStream().write(bytes);
} catch (Exception e) {
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(stringWriter);
e.printStackTrace(printWriter);
String stackTrace = stringWriter.toString();
result.addError(new ObjectError("report", stackTrace));
return getModelAndView(ctx, "report/error");
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return null;
}
@RequestMapping("/report/list")
public ModelAndView list(HttpServletRequest request,
HttpServletResponse response, Model model) throws IOException {
// Initialize the context (mandatory)
ControllerContext ctx = new ControllerContext(request, response);
init(ctx);
// Get report path
String path = getReportsPath();
// Get all available reports
File inFolder = new File(path);
FileFilter fileFilter = new FileFilter() {
public boolean accept(File file) {
return !file.isDirectory()
&& file.getName().endsWith(".jasper");
}
};
File[] files = inFolder.listFiles(fileFilter);
TreeMap<String, String> reports = new TreeMap<String, String>();
for (File file : files) {
String fullName = file.getName();
String baseName = fullName.substring(0, fullName.length() - 7);
String[] tokens = baseName.split("_");
if (tokens.length == 2) {
String url = tokens[0] + "/" + tokens[1];
String name = tokens[1];
reports.put(name, url);
}
}
model.addAttribute("reports", reports);
return getModelAndView(ctx, "report/list");
}
private String getReportsPath() throws IOException {
return (String) applicationProperties.get("jasper.reports.path");
}
/**
* Jasper will not accept more than one parameter named with the same name.
* We most likely will not need to pass complex objects to Jasper so we
* should be OK
*
* @param requestParams
* @return
*/
private Map<String, String> getParamsForJasper(
Map<String, List<String>> requestParams) {
Map<String, String> jasperParams = new HashMap<String, String>();
for (String key : requestParams.keySet()) {
jasperParams.put(key, requestParams.get(key).get(0));
}
return jasperParams;
}
Sub Reports
To illustrate subreports let us create a report that accepts a parameter, the full name of an employee. We can run for example a Bing query to see some public pages that might be related to each employee (like a custom and proprietary background check ;-).
1. Establish the dataset you will need and script metadata. and data. Here is sampleCheck.sql:
CREATE TABLE `links` (
`full_name` varchar(50) NOT NULL,
`title` varchar(250) NOT NULL,
`url` varchar(50) NOT NULL
) ;
2. Script the data. Here is sampleCheck_data.sql
INSERT INTO links (full_name, title, url) VALUES ("Nestor Urquiza", "Nestor Urquiza", "http://www.bing.com:80/search?q=nestor+urquiza");
INSERT INTO links (full_name, title, url) VALUES ("Nestor Urquiza", "Nestor Urquiza Resume", "http://www.nestorurquiza.com/resume");
INSERT INTO links (full_name, title, url) VALUES ("Pablo Cardone", "Pablo Cardone", "http://www.bing.com:80/search?q=pablo+cardone");
INSERT INTO links (full_name, title, url) VALUES ("Pablo Cardone", "Pablo Cardone Resume", "http://www.pablocardone.com/resume");
3. Build a local DB to be used to design the report:
sqlite3 sampleCheck.db < sampleCheck.sql
sqlite3 sampleCheck.db < sampleCheck_data.sql
4. At this point table “links” exists inside sampleCheck.db so open iReport and create a report called rt_sampleCheck. A file with extension .jrxml will be created. Use as data source the newly created DB. So:
name: sampleCheck
JDBC Driver: org.sqlite.JDBC
JDBC URL: jdbc:sqlite:/Users/nestor/sampleCheck.db
Query: select title, url from links where full_name = '$P!{fullName}';
5. Open iReport and include title and URL. Create a parameter called “fullName” Run the report. When asked for the parameter value use “Nestor Urquiza” The jasper file (rt_sampleCheck.jasper) is generated in the same directory where the jrxml file is.
6. Create the DAO implementation (SampleCheckReportDAO) that accepts a List of Objects to persist
7. Create a Service (SampleCheckReportService) that queries Bing for all users full names.
8. As we have follow conventions now it will be enough to call /report/rt/sampleCheck?fullName=Nestor+Urquiza from the browser. Note that we need a parameter this time. The parameter is inserted as is as a report param that is why the convention is so important here. Note also we could have live without a parameter in this simple example as we can restrict from java what we populate in the table however this is needed for our next final example. We will use sampleCheck as a subreport of sampleEmployee
Report plus Subreport
In reality both of the reports we have built so far use the same connection as they are both real time reports and so they use the same local sqlite database. However there are more complicated cases and I want to be sure I show here how a subreport can use a different connection than the main report. Once you add the subreport using the ireport GUI you will need to edit the XML as shown below. Note how I use a parameter to provide the location of the real time database. Of course I do so because I want to be sure I can inject that value later from Java.
<subreport>
<reportElement x="14" y="35" width="200" height="100"/>
<subreportParameter name="fullName">
<subreportParameterExpression><![CDATA[$F{first_name} + " " + $F{last_name}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlite:" + $P{realTimeDbPath}, "", "")]]></connectionExpression>
<subreportExpression class="java.lang.String"><![CDATA[$P{SUBREPORT_DIR} + "rt_sampleCheck.jasper"]]></subreportExpression>
</subreport>
The report when run will ask for the parameter and we will provide locally a value like below:
/Users/nestor/sampleCheck.db
The parent report must have empty path to look for the subreport. The reason is that we will drop all reports in the same folder to avoid another parameter to be passed (the subreport path)
All that is left now is to run our main report from Java. So we need to provide as a parameter “realTimeDbPath”. From Java we will need to populate of course the subreport data source and that is why we call the sampleCheckService from sampleEmployeeService. We can check still /report/rt/sampleCheck?fullName=Nestor+Urquiza works. Now /report/rt/sampleEmployee is rendering the sampleCheck subreport as well. Note that sampleCheck subreport gets the fullName from the master report so there is no need to insert it from Java. Still this works both from iDesigner with no Server in the middle (ideal for report writters) and from Java which is of course needed to present dynamic real time data. It would be great if JasperReports would allow for the use of connectionExpression element at the main report level and not only at subreport level. Unfortunately that is not the case and that is why we need to supply the main report connection as part of the URL (Jasper Report engine will not be able to dynamically based on a parameter discover which connection to use for the master report) So for the master report we provide a connection object whereas for the subreports we use “connectionExpression”. The expression will need to use the “realTimeDbPath” parameter when the report uses a real time data set or will be completely hardcoded when using any SQL database. Note that datasources do not necessarily have to be hardcoded in subreports “connectionExpression” elements. We can always use parameters to build them on the fly as already explained.
Below is a snapshot of the pdf report obtained from the web request:
And here a snapshot of the same from iReport. Same JRXML used from the backend and the frontend without stepping on each other toes.
A Check List
Below is a check list for both iReport Designers (BA) and Java Developers. As a developer you will need to provide the iReport Designer with:
1. script.sql
2. script_data.sql
3. Path where to put the compiled report file so it shows up from the web interface
4. Agree on a name for the report. Use Spring Resources for internationalization so when showing the name of the report it can show a more descriptive name but try hard to get a name that makes sense to everybody.
As a report Designer you will need to:
1. run the below commands:
sqlite3 local.db < script.sql
sqlite3 local.db < script_data.sql
2. Create a report and use local.db from above as data source. Naming conventions is important. The report must be named using the data source name followed by underscore and then the name of the service agreed with the developer. Case matters, so be aware.
3. Configure subreport "subreportParameter" and "connectionExpression" nodes. Here is a list of useful connection expressions: realtime sqlite:
<connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:sqlite:" + $P{realTimeDbPath}, "", "")]]></connectionExpression>
mysql: <connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/mySQLDatabaseName", "myUserName", "myPassword")]]></connectionExpression>
sqlserver: <connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/sqlServerDatabaseName;prepareSQL=3", "myUserName", "myPassword")]]></connectionExpression>
4. Put the results in the server reports directory.
You can download the Jasper sources and sqlite databases from
here.
