Reporting Server/Server Configuration
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.