Reporting Server/Reporting Table Development Steps

Contents 1 Prerequisites 2 Summary 3 Development on local system 3.1 Create Reporting Database 3.2 Create Reporting Table 3.3 Export Reporting Model 3.4 Create Load Script 3.5 Export Database and commit and push to repo 4 Deployment on the Reporting Server 4.1 Pull the changed repo 4.2 Rebuild Openbravo 4.3 Stop Tomcat 4.4 Adapt or Create Reporting Database Tables 4.5 Generate Data Loader Application 4.6 Run the data loader application 4.7 Start tomcat 4.8 Login into Jasper and adapt the domain

This document discusses the steps to take for creating a new reporting table in the reporting datawarehouse, defining the load script and deploying it on a reporting server.

Prerequisites

This how to assumes that you have a fully running reporting development environment. To set up a development environment with reporting modules please checkout out this page.

Also make sure that you have a module defined for your custom work. In this howto we will use a specific module.

This howto also assumes that your Openbravo development is fully build and has a running tomcat to access its user interface.

Summary

The reporting solution consists of three artifacts which need to be developed:

1. Reporting table in the reporting data warehouse
2. Load script to load/transform data from the Openbravo Database into the data warehouse
3. Domains/reports within the Reports Server

These artifacts are all deployed on the reporting server as indicated in the architecture:

The development steps can be divided in two parts. Work to be done on your local development system and deployment to be done on the reporting server.

• Development on your local dev system

1. create reporting database (locally)
2. create the reporting table (for example pgadmin user interface)
3. export the reporting model
4. create load script in the openbravo application dictionary
5. export and commit and push to your repository

• Deployment on the Reporting Server

1. pull the changed repo
2. do an Openbravo smartbuild
3. stop tomcat
4. create reporting database
5. generate data loader application
6. run the data loader application
7. start tomcat
8. use the jasper reporting server user interface to adapt the domain

Development on local system

Create Reporting Database

Go to the following module folder: modules/org.openbravo.reporting.tools. Check that the reporting.properties file exists and has a reporting database connection information.

Note: the reporting database defined in reporting.properties is a separate database from the standard Openbravo database. Choose another database name than openbravo (e.g. reporting).

Then execute the following command:

modules/org.openbravo.reporting.tools$ ant create.reporting.database

Create Reporting Table

Then create the reporting table. Things to consider:

• the table name should use the database prefix of the module in which you want to place it
• add relevant indexes also with the dbprefix prefix
• do not use/add foreign key constraints

Then create the reporting table in the reporting database. See the example below using pgadmin4.

Export Reporting Model

Go to the following module folder: modules/org.openbravo.reporting.tools. Then execute the following command:

modules/org.openbravo.reporting.tools$ ant export.reporting.model.full

Create Load Script

The next step is to create the load script in the application dictionary.

For a standard load script you can see a good example below in the screenshot. Some note:

• Choose the correct reporting table (for which you are creating the load script)
• The script order determines the order in which scripts are executed to load data. Scripts with the same script order are executed in parallel. A guide line for the value: master data has a value of 10 or 20, transactional data load scripts normally have a sort order of 30, 40 or 50
• If the 'always do full reload' is not checked then the query should normally have filters on updated time stamp (see the screenshot in the where clause) to implement incremental loads.

For more details check out this page.

Export Database and commit and push to repo

Next step is to get the changes into your repo. First step is export database, goto the root of the Openbravo project:

ant export.database

Push the changes to the repo:

cd modules/com.openbravo.cart.reporting.tools
git add *
git commit -m "My new reporting table"
git push

Deployment on the Reporting Server

Use ssh to goto the reporting server. The following folders are relevant:

• /opt/OpenbravoERP: folder with the Openbravo install
• /opt/OpenbravoReporting/DataLoaderApp: the data loader application which loads the data from Openbravo into the reporting database

Pull the changed repo

Goto to module folder and pull it:

cd /opt/OpenbravoERP/modules/com.openbravo.cart.reporting.tools
git pull

Rebuild Openbravo

Execute:

cd /opt/OpenbravoERP
ant smartbuild -Dlocal=no

Stop Tomcat

Tomcat needs to be stopped to allow creating the reporting database: Execute:

sudo /etc/init.d/tomcat stop

Adapt or Create Reporting Database Tables

To update the reporting database with the new table or columns a choice can be made to recreate the database completely. The disadvantage of recreating the database is that all data needs to be reloaded. If the database change is smaller (a new column or table) then it can make sense to directly run a create table or alter table database statement.

To completely recreate a database: make sure the org.openbravo.reporting.tools has a reporting.properties file with all the relevant information set. Then goto the reporting tools module and execute the create.reporting.database ant task:

cd /opt/OpenbravoERP/modules/org.openbravo.reporting.tools
ant create.reporting.database

Generate Data Loader Application

In the org.openbravo.reporting.tools module there is a script (generate_app) to generate the data loader application:

cd /opt/OpenbravoERP/modules/org.openbravo.reporting.tools
./generate_app

The data loader application is generated into /opt/OpenbravoReporting/DataLoaderApp

Run the data loader application

Goto the data loader folder, change the executable flag on the update script (is not set by default) and then run the dataloader app:

cd /opt/OpenbravoReporting/DataLoaderApp
chmod +x update_script
./update_script

Note: the log of the dataload can be found here: /var/log/openbravo/dataloader.log.

Start tomcat

sudo /etc/init.d/tomcat start

Login into Jasper and adapt the domain

For the details how to work with domains and reports with modules visit this page.