Report Store

Important: This module works only for Ontimize Boot version 3.7.0 or above. Actual release version: Ontimize Boot

Introduction

The Report Store system will allow you to store, manage and export all kinds of reports designed and implemented via the JasperReports API. This module will let you use your Ontimize application data as data sources for your reports, allowing you to fully customize its layout with tables, charts, graphs… and also visualize, export, print and download your reports.

Previous concepts

  • Report: It is the generic representation of a report. One report can have one or multiple custom report parameters, or have none.
  • Main report file: The file containing all the information regarding the report layout, along with the data sources, connections and other configuration directives. Written in .jrxml format.
  • Report parameter: It is the generic representation of a custom report parameter. They are used for defining filters, implementing pagination, or even specifying the report data source.

Prerequisites

You can follow this tutorial using your own application, although for this example we will use an application created using the archetype that can be found on this page and with a REST service.

There are 2 options to follow this tutorial, clone the repository with the initial state and follow the tutorial step by step, or download the final example and see which files are new and which have been updated.

Initial project

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-report-initial

Final example

/$ git clone https://github.com/ontimize/ontimize-examples
/ontimize-examples$ cd ontimize-examples
/ontimize-examples$ git checkout boot-report

Note: To simplify the code being written, three dots (…) may appear in some parts of the code. This indicates that there may be previous code before and after those dots.

Important: In the first step we will learn how to configure the reports system with the database engine. If you want to use the file system engine you can jump to this section.

Steps

Database

Report Tables

With the database started, we create the new tables that will store the reports information. We’re going to need to create two different tables, one for the report itself and one for the report custom parameters.

1
2

CREATE TABLE REPORTS(ID INTEGER IDENTITY NOT NULL PRIMARY KEY, UUID VARCHAR(255) NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(255), REPORT_TYPE VARCHAR(255), MAIN_REPORT_FILENAME VARCHAR(255) NOT NULL, ZIP VARBINARY(16777216), COMPILED VARBINARY(16777216));
CREATE TABLE REPORT_PARAMETERS(ID INTEGER IDENTITY NOT NULL PRIMARY KEY, REPORT_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(255), NESTED_TYPE VARCHAR(255), VALUE_CLASS VARCHAR(255));

Once the tables have been created, we add the foreign key

1

ALTER TABLE REPORT_PARAMETERS ADD CONSTRAINT REPORT_PARAMETERS_FK FOREIGN KEY(REPORT_ID) REFERENCES REPORTS(ID);

Server

Add Ontimize Report dependencies

  • ontimize-examples
    • projectwiki-api
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • api
                    • core
                      • service
                        • IUserService.java
      • pom.xml
    • projectwiki-boot
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ServerApplication.java
          • resources
            • application.yml
      • pom.xml
    • projectwiki-model
      • src
        • main
          • db
            • templateDB.tmp
            • templateDB.lck
            • templateDB.log
            • templateDB.properties
            • templateDB.script
            • templateDB.txt
          • java
            • com
              • ontimize
                • projectwiki
                  • model
                    • core
                      • dao
                        • UserDao.java
                        • UserRoleDao.java
                      • service
                        • UserService.java
          • resources
            • dao
              • placeholders.properties
              • RoleDao.xml
              • RoleServerPermissionDao.xml
              • ServerPermissionDao.xml
              • UserDao.xml
              • UserRoleDao.xml
      • pom.xml
    • projectwiki-ws
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ws
                    • core
                      • rest
                        • MainRestController.java
                        • TestRestController.java
                        • UserRestController.java
      • pom.xml
    • .gitignore
    • candidates.zip
    • pom.xml
    • README.md

projectwiki-boot/pom.xml

...
<dependencies>
	...
	<dependency>
		<groupId>com.ontimize.boot</groupId>
		<artifactId>ontimize-boot-starter-report</artifactId>
	</dependency>
	...
</dependencies>
...

projectwiki-model/pom.xml

...
<dependencies>
	...
	<dependency>
		<groupId>com.ontimize.jee.report</groupId>
		<artifactId>ontimize-jee-report-server</artifactId>
	</dependency>
	...
</dependencies>
...

Add Report DAOs

A specific DAO will be created for each of both tables in the reports system, and each of them will implement a different interface.

  • ontimize-examples
    • projectwiki-api
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • api
                    • core
                      • service
                        • IUserService.java
      • pom.xml
    • projectwiki-boot
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ServerApplication.java
          • resources
            • application.yml
      • pom.xml
    • projectwiki-model
      • src
        • main
          • db
            • templateDB.tmp
            • templateDB.lck
            • templateDB.log
            • templateDB.properties
            • templateDB.script
            • templateDB.txt
          • java
            • com
              • ontimize
                • projectwiki
                  • model
                    • core
                      • dao
                        • ReportDao.java
                        • ReportParameterDao.java
                        • UserDao.java
                        • UserRoleDao.java
                      • service
                        • UserService.java
          • resources
            • dao
              • placeholders.properties
              • ReportDao.xml
              • ReportParameterDao.xml
              • RoleDao.xml
              • RoleServerPermissionDao.xml
              • ServerPermissionDao.xml
              • UserDao.xml
              • UserRoleDao.xml
      • pom.xml
    • projectwiki-ws
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ws
                    • core
                      • rest
                        • MainRestController.java
                        • TestRestController.java
                        • UserRestController.java
      • pom.xml
    • .gitignore
    • candidates.zip
    • pom.xml
    • README.md

ReportDao.xml

1
2
3
4
5
6
7
8
9
10
11
12
13

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	table="REPORTS" datasource="mainDataSource" sqlhandler="dbSQLStatementHandler">
	<DeleteKeys>
		<Column>ID</Column>
	</DeleteKeys>
	<UpdateKeys>
		<Column>ID</Column>
	</UpdateKeys>
	<GeneratedKey>ID</GeneratedKey>
</JdbcEntitySetup>

ReportParameterDao.xml

1
2
3
4
5
6
7
8
9
10
11
12
13

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup xmlns="http://www.ontimize.com/schema/jdbc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
	table="REPORT_PARAMETERS" datasource="mainDataSource" sqlhandler="dbSQLStatementHandler">
	<DeleteKeys>
		<Column>ID</Column>
	</DeleteKeys>
	<UpdateKeys>
		<Column>ID</Column>
	</UpdateKeys>
	<GeneratedKey>ID</GeneratedKey>
</JdbcEntitySetup>

ReportDao.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

package com.ontimize.projectwiki.model.core.dao;

import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

import com.ontimize.jee.server.dao.common.ConfigurationFile;
import com.ontimize.jee.server.dao.jdbc.OntimizeJdbcDaoSupport;
import com.ontimize.jee.server.services.reportstore.dao.IReportDao;

@Lazy
@Repository(value = "ReportDao")
@ConfigurationFile(configurationFile = "dao/ReportDao.xml", configurationFilePlaceholder = "dao/placeholders.properties")
public class ReportDao extends OntimizeJdbcDaoSupport implements IReportDao {

	public static final String	ATTR_ID					= "ID";
	public static final String	ATTR_NAME				= "NAME";
	public static final String	ATTR_DESCRIPTION			= "DESCRIPTION";
	public static final String	ATTR_REPORT_TYPE			= "REPORT_TYPE";
	public static final String	ATTR_MAIN_REPORT_FILENAME	        = "MAIN_REPORT_FILENAME";
	public static final String	ATTR_ZIP				= "ZIP";
	public static final String	ATTR_COMPILED				= "COMPILED";

	public ReportDao() {
		super();
	}
}

ReportParameterDao.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

package com.ontimize.projectwiki.model.core.dao;

import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

import com.ontimize.jee.server.dao.common.ConfigurationFile;
import com.ontimize.jee.server.dao.jdbc.OntimizeJdbcDaoSupport;
import com.ontimize.jee.server.services.reportstore.dao.IReportParameterDao;

@Lazy
@Repository(value = "ReportParameterDao")
@ConfigurationFile(configurationFile = "dao/ReportParameterDao.xml", configurationFilePlaceholder = "dao/placeholders.properties")
public class ReportParameterDao extends OntimizeJdbcDaoSupport implements IReportParameterDao {

	public static final String	ATTR_ID					= "ID";
	public static final String	ATTR_REPORT_ID				= "REPORT_ID";
	public static final String	ATTR_NAME				= "NAME";
	public static final String	ATTR_DESCRIPTION			= "DESCRIPTION";
	public static final String	ATTR_NESTED_TYPE			= "NESTED_TYPE";
	public static final String	ATTR_VALUE_CLASS			= "VALUE_CLASS";

	public ReportParameterDao() {
		super();
	}
}

Modify application.yml

The application.yml file will be modified to enable the reports module, indicate the report engine type it will use and, if needed, the path where the report files will be stored. In this link you have information about the configuration of the reports system in the application.yml file.

Note: The enable property must be set to true and the engine type must be specified in the engine property before the server is started.

Important: You can only choose ONE of the two options listed below.

  • ontimize-examples
    • projectwiki-api
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • api
                    • core
                      • service
                        • IUserService.java
      • pom.xml
    • projectwiki-boot
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ServerApplication.java
          • resources
            • application.yml
      • pom.xml
    • projectwiki-model
      • src
        • main
          • db
            • templateDB.tmp
            • templateDB.lck
            • templateDB.log
            • templateDB.properties
            • templateDB.script
            • templateDB.txt
          • java
            • com
              • ontimize
                • projectwiki
                  • model
                    • core
                      • dao
                        • ReportDao.java
                        • ReportParameterDao.java
                        • UserDao.java
                        • UserRoleDao.java
                      • service
                        • UserService.java
          • resources
            • dao
              • placeholders.properties
              • ReportDao.xml
              • ReportParameterDao.xml
              • RoleDao.xml
              • RoleServerPermissionDao.xml
              • ServerPermissionDao.xml
              • UserDao.xml
              • UserRoleDao.xml
      • pom.xml
    • projectwiki-ws
      • src
        • main
          • java
            • com
              • ontimize
                • projectwiki
                  • ws
                    • core
                      • rest
                        • MainRestController.java
                        • TestRestController.java
                        • UserRestController.java
      • pom.xml
    • .gitignore
    • candidates.zip
    • pom.xml
    • README.md

application.yml

For database engine

ontimize:
   report:
      enable: true
      engine: database

For file system engine

ontimize:
   report:
      enable: true
      engine: file
      base-path: C:/applications/projectwiki/reports

Testing the reports system

Once the reports system is already configured and the server and the database are running, we will follow the next steps:

Create report template

In order to create our own report templates, it is recommended to use Jaspersoft Studio. You can download it from this link.

In this tutorial we will provide you a report template for candidates, but if you want more information about how to create and customize a template, check this tutorial.

Our template is located in the main folder of the project, it is called candidates.zip.

Note: Do not unzip the compressed file because the request explained below requires it compressed.

Add report to the reports system

To add the template we will have to execute the following REST request: http://localhost:33333/reportstore/addReport

Element Meaning
localhost:33333 Indicates the host
/reportstore Indicates the service to be queried
/addReport Indicates the method of the service that is going to be executed

With the Postman program, you will have to add a body of type form-data in which you will have to add the following values:

key type value Description
data text {“name”:”x”,”type”:”x”,”description”:”x”} Indicates the name, type and description of the template
file file candidates.zip You have to indicate the path of the candidates.zip file

Generate report

When you run the above request, if the value of the engine set in the application.yml is filesystem, you have to copy the uuid that comes in the response data and execute the following request: http://localhost:33333/reportstore/fillReport/.

Element Meaning
localhost:33333 Indicates the host
/reportstore Indicates the service to be queried
/fillReport Indicates the method of the service that is going to be executed
  Indicates the uuid that you receives of the previous request

But if the value of the engine set in the application.yml is database, you have to execute the following request to know the uuid of the report template: http://localhost:33333/reportstore/listReports.

Element Meaning
localhost:33333 Indicates the host
/reportstore Indicates the service to be queried
/listReports Indicates the method of the service that is going to be executed

Example: http://localhost:33333/reportstore/fillReport/fd656189-2158-4e84-ac5c-8379960fddbd

The authorization used for these requests is authorization of the type BASIC.

In both cases, the access must be done with a user and password example:

    User: demo
Password: demouser

Visualize report document

When you run the above request, in the body of the response you will find the key file, whose value is a Base 64 that contains the format and data of the report template. Copy it and go to this page to convert the Base 64 into a PDF file.