Reporting Server/Server Configuration

Contents 1 Pre-build instance contents 2 Main Configuration Steps 2.1 Set jaspersoft reports server super user password 2.2 Build environment: add your custom modules and do install.source 2.3 Configure single sign on - automatic login 2.4 Set reporting.properties 2.5 Set webservice connection information 2.6 Create the reporting database 2.7 Check that the license is present 2.8 Upload the System setup to the reports server repository 2.9 Upload custom reports and other modules data into the reports server repository 2.10 Generate and configure the data loader application 2.10.1 Generate the data loader application 2.10.2 Check data loader log4j2.xml 2.10.3 Run the data loader application 2.10.4 Add a cron job to load the data periodically 2.10.5 Create a separate data load which only loads a subset of the data 3 Post configuration steps 4 Openbravo Backoffice and WebPOS Integration 5 Other configurations 5.1 Email configuration & Report Scheduling 5.2 Enable view query in ad-hoc views

The Openbravo Reports Server is implemented using Jaspersoft Reports Server. This document describes the configuration of the Openbravo Reports Server, both for the database as well as for configuration of the Jasper Reports Server itself.

Note: that for our customers we will deploy a server using a standard pre-build instance. This document describes the steps from this basis configuration. The first section below describes what is included in this pre-build instance.

Pre-build instance contents

This document assumes a reporting server instance provided by Openbravo and it describes the configuration/customization steps from this basis. This document then assumes that you have a server with the following installed:

• postgresql database version 10.* or newer (note: Oracle is also supported but not delivered as default)
• postgresql database user: tad with standard Openbravo password
• jasper reports server installed in tomcat: /var/lib/tomcat/webapps
• jasper reports server license was set
• jasper reports server running with access as superuser
• apache/ssl configured
• java 11
• ant and mercurial installed
• Openbravo sources available in the folder: /opt/OpenbravoERP (as usual)
• the Reporting Integration and Reporting Tools modules present in the modules folder of the Openbravo sources.
• Openbravo.properties set, specifically:
  • Database connection information set
  • source.path set correctly
  • deploy.mode=none

Main Configuration Steps

Set jaspersoft reports server super user password

As a first step login to jasper reports server using the known default password and change the super user password to a complex password.

Build environment: add your custom modules and do install.source

The build environment is a standard Openbravo ERP install with additional modules specifically for reporting. The build environment is located in /opt/OpenbravoERP.

As a first step update the core reporting modules:

• org.openbravo.reporting.integration
• org.openbravo.reporting.tools

For example:

test-reports:/opt/OpenbravoERP/modules$ cd org.openbravo.reporting.integration/
test-reports:/opt/OpenbravoERP/modules/org.openbravo.reporting.integration$ git pull https://gitlab.com/openbravo/product/pmods/org.openbravo.reporting.integration.git

Typically you will also need the masterdata module:

test-reports:/opt/OpenbravoERP/modules$ git clone https://gitlab.com/openbravo/product/pmods/org.openbravo.reporting.tools.masterdata.git

And the sales reports:

test-reports:/opt/OpenbravoERP/modules$ git clone https://gitlab.com/openbravo/product/pmods/org.openbravo.reporting.tools.sales.git

See also other reporting modules here.

Your reports server will contain custom domains and reports which are delivered through modules. See for example the Sales Reports modules.

To make use of these modules do the following:

• Download/place the needed modules inside the modules folder of the build environment.

After all the modules are downloaded in the /opt/OpenbravoERP/modules directory then do install source:

test-reports:/opt/OpenbravoERP$ ant install.source

Configure single sign on - automatic login

Copy the file modules/org.openbravo.reporting.tools/jasper-config/ob.single-sign-on.in.template to ob.single-sign-on.in

cp ob.single-sign-on.in.template ob.single-sign-on.in

Generate a printable character random string of length 16 and place it in the top of the ob.single-sign-on.in file, remove the comments in the file so only the random string remains.

For example:

sudo apt install pwgen
pwgen 16 > jasper-config/ob.single-sign-on.in

Note: a key of length 32 will be supported in later releases.

Copy the single sign on file to the jasper webapp WEB-INF/classes folder:

 
test-reports:/opt/OpenbravoERP/modules/org.openbravo.reporting.tools/jasper-config$ cp ob.single-sign-on.in /var/lib/tomcat/webapps/jasperserver-pro/WEB-INF/classes

Set reporting.properties

Then go to the modules/org.openbravo.reporting.tools module folder and copy reporting.properties.template to reporting.properties

cp reporting.properties.template reporting.properties

Set the reporting properties. There are different sections each is described separately within the property file, the following needs to be set for the configuration:

• Reporting database connection properties: the reporting database in which the data is loaded, in the configuration step is used to create the reporting database, later is used for loading the data.

The following information is used later in the dataload step. It makes sense to already set them right away:

• client id: the client id of the data to load from the source database
• Openbravo database connection properties: the source database connection properties. Note: this is the database containing the business functional data, it is not the same as the Openbravo database used for the build step.

Set webservice connection information

Note: this section is mostly relevant if you are installing from an Openbravo instance without running the Openbravo UI. If there is a UI, the information in this section can also be set by logging into the backoffice and creating a reporting configuration record.

The system needs webservice connection information to upload the reports from the modules into the reporting server. This webservice connection information is set using the following ant command (from/in the org.openbravo.reporting.tools module folder):

ant create.update.reporting.config -Dpassword=[PWD] -Durl=https://[HOST]/jasperserver-pro

Parameters:

• PWD: the jasperreports server superuser password
• HOST: the actual host name (localhost will not work due to https)

Note: if the password has non-alphabetic characters then the system might show an error. In that case you can try to enclose the password value with double quotes "".

Replace the placeholders ([PWD] and [HOST]) with your corresponding information. By default the system will use the superuser for uploading the modules, if you want to choose a different user, then pass also the 'user' parameter.

Create the reporting database

To create the reporting database perform the following ant action from/in the org.openbravo.reporting.tools module folder:

ant create.reporting.database

Check that the license is present

If no (or an invalid) license file is used you will see the following message when accessing the https://[HOST]/jasperserver-pro url:

There should be a license file (jasperserver.license) in the location /opt/OpenbravoReporting/license. Contact your system admin to arrange/deliver this file if not present. This file is delivered in a zip file, just unzipping this zip file in this location suffices. After placing the license file restart tomcat to check:

sudo /etc/init.d/tomcat restart

Upload the System setup to the reports server repository

Make sure the jasper server is running and that the webservice properties are set in the reporting.properties file.

Then perform the following ant action from/in the org.openbravo.reporting.tools module folder:

ant upload.module.files.to.jasper -Dmodule=org.openbravo.reporting.tools

Upload custom reports and other modules data into the reports server repository

Normally you should have modules providing reports, ad-hoc views and domains. For each of these modules you need to execute the upload step to upload the reports to the reports server repository.

Note: the upload step should be done in the order of the dependencies of the modules.

For example, to upload the reports provided by the reporting sales module do the following:

ant upload.module.files.to.jasper -Dmodule=org.openbravo.reporting.tools.sales

Generate and configure the data loader application

Generate the data loader application

The reports server setup is complete now the next step is to enable data loading. The first step is to generate the data loader application itself. Execute the following ant action in the modules/org.openbravo.reporting.tools module:

ant generate.dataloader.application -DtargetDir=/opt/OpenbravoReporting/DataLoaderApp

This will generate the data loader app in the /opt/OpenbravoReporting/DataLoaderApp folder. Note: this assumes that the linux user can create that folder if it does not already exist.

Check data loader log4j2.xml

In the target data loader folder you will find a log4j2.xml file. Check if it has the appropriate settings for your logging requirements.

Run the data loader application

If relevant you can run/test the data loader app (note: depending on the size of the source database this can take from less than a minute upto 1-2 hours). To run the dataload go to /opt/OpenbravoReporting/DataLoaderApp and do 'ant load.data':

test-reports:/opt/OpenbravoReporting/DataLoaderApp$ ant load.data
Buildfile: /opt/OpenbravoReporting/DataLoaderApp/build.xml
 
load.data:
 
BUILD SUCCESSFUL
Total time: 3 seconds

If you used the default log4j settings then you can check the log in /var/log/openbravo/dataloader.log:

test-reports:/opt/OpenbravoReporting/DataLoaderApp$ more /var/log/openbravo/dataloader.log

Add a cron job to load the data periodically

There is a sample update script in the dataloader application (update_script). Check its content and make sure the cd command in it points to the correct install location.

Set the update script file (located in the target directory) to executable:

chmod +x update_script

Then enable the load through the cron by doing crontab -e. Note that some additional settings are needed to make sure that ant runs correctly from cron:

SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin
05 */3 * * * /opt/OpenbravoReporting/DataLoaderApp/update_script

Note: this is just an example setup for cron. Choose the setup/timing needed for your specific case.

Create a separate data load which only loads a subset of the data

For several reasons (e.g. performance) it can make sense to run different data loads at different intervals. This is supported.

The data load steps are defined in a specific file in the data loader app folder: data-load-definition.json.

The data load steps are easily distinguishable in this json file:

 
{"steps": [
  {
    "id": "0019D116E5D947D5A2CEB16E482E1F98",
    "name": "obrts_stock_by_date_mv",
    "identifier": "obrts_obrts_stock_by_date_mv",
    "tableName": "obrts_stock_by_date_mv",
    "className": "org.openbravo.reporting.tools.dataloader.MaterializedViewDataLoader",
    "modulePrefix": "OBRTS",
    "reloadOrder": 40,
    "materializedView": true,
    "alwaysFullyReload": false,
    "executeOnce": false
  },
  {
    "id": "01F2ED46D22D4AF7B189FB23DD6687F4",
    "name": "obrts_order_sales",
    "identifier": "obrts_obrts_order_sales",
    "tableName": "obrts_order_sales",
...

Copy the data-load-definition.json to a new file (in the data loader app folder) and remove everything in the file which is not needed, the resulting file should be correct json.

Then copy the update_script file to a new file. Open your new update_script file in an editor and replace the data-load-definition.json filename parameter with the name of your new/focused data load definition json file.

 
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin
 
set -e
 
if ps aux | grep [l]oad.data > /dev/null
then
  echo "ant load.data is still running"
else
  echo "Starting update"
  cd /opt/OpenbravoReporting/DataLoaderApp
  ant load.data -DfileName=data-load-definition.json
  echo "Ended update"
fi

Save your new update script file and add it to the crontab to run it periodically.

Post configuration steps

After configuring the system itself there are several steps remaining (add org and admin user):

• Create an organization within the Reports Server for the customer (note: prefer a short name, use all upper-case)

• Jasper will by default create some users when creating an organization, remove these users joeuser and jasperadmin:

• Add an admin user for the organization (it should be: [ORG_NAME]-admin, all lower case):

Note select the org on the left when clicking the add user button:

• set the admin role for the admin user, select the user, click edit, scroll down on the right to the roles field and select the admin role, move it to the right and click save.

The Jasper Administrator guide provides more details on these steps.

Openbravo Backoffice and WebPOS Integration

As a next step you can now set up the integration from the Openbravo backoffice and WebPOS to the reporting environment. The details are described in Reporting-Functional-Integration page.

Other configurations

Email configuration & Report Scheduling

Openbravo does not provide a SMTP server, to set your own SMTP information in the WEB-INF/js.quartz.properties file:

report.scheduler.mail.sender.host=[SMTP-HOST]
report.scheduler.mail.sender.username=[USERNAME]
report.scheduler.mail.sender.password=[PASSWORD]
report.scheduler.mail.sender.from=[FROM EMAIL]
report.scheduler.mail.sender.protocol=[smtp or smtps]
report.scheduler.mail.sender.port=[PORT, eg. 465]

Set the correct url in the WEB-INF/js.quartz.properties file:

report.scheduler.web.deployment.uri=http://[HOST]/jasperserver-pro

In the file WEB-INF/applicationContext-report-scheduling.xml add the mail.smtps.auth tag:

        <property name="javaMailProperties">
            <props>
                <prop key="mail.smtp.sendpartial">true</prop>
                <prop key="mail.smtps.auth">true</prop>
            </props>
        </property>

Do the same in WEB-INF/applicationContext.xml.

NOTE: for sendgrid (a smtp provider) we noticed that the protocol has to be set to smtps and the port to 465 to make it work.

Enable view query in ad-hoc views

Within ad-hoc views there is a button to view the underlying sql query. This button is not enabled by default. This link describes how to enable it.