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 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
Main Configuration Steps
Build environment: add your custom modules and do install.source
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.
- Run: ant install.source within the Openbravo source folder
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.
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.
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
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:
Update the system configuration files and restart tomcat
We use specific settings for the database connection, single sign on and templates. To copy/overwrite the relevant configuration files in the jasper webapp do the following ant action from within org.openbravo.reporting.tools module folder:
This action assumes that jasper is installed in /var/lib/tomcat/webapps/jasperserver-pro. If in your case jasper is installed in another location use the parameter jasper.web.app (pass it as -Djasper.web.app=[PATH_TO_JASPER_WEBAPP] in the above ant task).
Restart the reports server webapp:
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.
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.
Post configuration steps
After configuring the system itself there are several steps remaining:
- Reset the super user password and change it also in the reporting.properties file created earlier
- Create an organization within the Reports Server for the customer
- Add an admin user for the organization
The Jasper Administrator guide provides more details on these steps.
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]
Check that the url is correctly set in the WEB-INF/js.quartz.properties file:
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.
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.