Installing OpenReports 3.2.0

OpenReports is an open source web reporting solution that supports a variety of open source reporting engines, including JasperReports, JXLS, and Eclipse BIRT. You create reports externally using these tools and then OpenReports can be used to run, schedule and deliver them. OpenReports also supports QueryReports and ChartReports which enable you to easily create reports and charts by only specifying the required SQL. No report design is required for these.

OpenReports 3.2.0 supports JasperReports 3.5.1, Eclipse BIRT 2.3.2 and JXLS 0.9.8.

Prerequisites
JRE or JDK 1.6

Versions used
JRE – 1.6.0.17

Steps

• Download the zip file from SourceForge, http://sourceforge.net/projects/oreports/files/
• Unzip the file to C:\. Don't rename the extracted folder, but leave the extracted path as C:\openreports-tomcat.
• Go to Start > Control Panel > System > Advanced System Settings. Select the Advanced tab and click on the Environment Variables button. Click on the new button and create a variable named JRE_HOME with the value set to the path of the JRE directory e.g. C:\Program Files\Java\jre6. If you have JDK installed, create a variable named JAVA_HOME instead with the value set to the path of the JDK directory.
• Edit the file c:\openreports-tomcat\startup.bat to have the following contents

cd database
start start-database.bat
cd ..\tomcat\bin
call startup

• Create a new file in c:\openreports-tomcat named shutdown.bat with the following contents

cd database
start stop-database.bat
cd ..\tomcat\bin
call shutdown

• Create a new file in c:\openreports-tomcat\database named stop-database.bat with the following contents

@echo off

FOR %%b IN (openreports,sample) DO call :runCommand %%b
goto :end

:runCommand
java -cp hsql/hsqldb-1.7.3.jar org.hsqldb.util.ShutdownServer -url "jdbc:hsqldb:hsql://localhost/%1" -user "SA" -password ""

:end

• Double-click on the file c:\openreports-tomcat\startup.bat. This will start the HSQLDB database OpenReports uses internally and the tomcat server bundled with OpenReports.
• Wait for the tomcat server to finish starting up. When finished the original command window will display something like INFO: Server startup in 78676 ms
• Open a browser window and navigate to http://localhost:8080/openreports
• If port 8080 is already in use, for instance by another web server, modify the file C:\openreports-tomcat\tomcat\conf\server.xml. Search for 8080 and replace the value for the HTTP/1.1 connector with another port number that's not in use and that's greater than 1024. Restart the server and include the new port number in the URL you navigate to.
• Login using the username admin and password admin.
• You can now select and view the sample reports that come with the installation.
• To add new reports, create the reports using the reporting engines supported by OpenReports e.g. JasperReports and deploy the report files to the OpenReports server. Add and configure them from OpenReports to make them available.
• Copy the JDBC drivers for the databases you'll be reporting from to the C:\openreports-tomcat\tomcat\common\lib folder.
• Copy the JDBC drivers for the databases used by BIRT reports to the folder C:\openreports-tomcat\tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.r232_v20090212\drivers
• To have emails sent to a mail server with SMTP authentication turned on, add the following to the file C:\openreports-tomcat\tomcat\webapps\openreports\WEB-INF\applicationContext.xml. Setting the smtp authentication details in the OpenReports administration panel doesn't work.

<bean id="mailAuthenticator" class="org.efs.openreports.util.SMTPAuthenticator">
 <constructor-arg index="0" value="email account here" />
 <constructor-arg index="1" value="password here" />
 </bean>

 <bean id="mailSession" class="javax.mail.Session" factory-method="getInstance">
 <constructor-arg index="0">
 <props>
 <prop key="mail.smtp.host">smtp server hostname or IP here</prop> 
<prop key="mail.smtp.auth">true</prop> 
 </props>
 </constructor-arg>
 <constructor-arg index="1" ref="mailAuthenticator" />
</bean>

• To get OLAP report samples to work when using JRE 1.6+, delete the file xalan-2.6.0.jar from the WEB-INF\lib folder and then restart tomcat.
• To stop OpenReports, double-click on the file C:\openreports-tomcat\shutdown.bat
• Installation is complete

Setting up OpenReports to use an independent Tomcat server and MySQL for its database
OpenReports comes bundled with a tomcat server and preconfigured HSQLDB database. To use a pre-existing tomcat server and MySQL for the database, use the following additional steps.

Prerequisites
Tomcat
MySQL
Apache Ant
JDK 1.6
MySQL JDBC driver

Versions used
Tomcat 6.0.20
MySQL 5.0.45
JDK 1.6.0_17
Apache Ant 1.8.0RC1
MySQL JDBC driver 5.1.10

Steps

• Download Apache Ant from http://ant.apache.org/ if you don't already have it. Download the binary distribution zip archive.
• Unzip the file to c:\
• Edit the PATH environment variable and add ;C:\apache-ant-1.8.0RC1\bin at the end.
• Copy the MySQL JDBC driver [jar file] to the tomcat\lib folder of the tomcat installation
• Ensure you have a JAVA_HOME environment variable that points to the JDK folder e.g. C:\Program Files\Java\jdk1.6.0_17
• Edit the file C:\openreports-tomcat\database\schema\or_ddl_mysql.sql. Add semicolons at the end of each command, and add the following lines in addition.

INSERT INTO REPORT_USER (NAME,PASSWORD,PDF_EXPORT_TYPE) VALUES('admin','admin',0);
INSERT INTO USER_SECURITY(USER_ID,ROLE_NAME) VALUES (1,'ROOT_ADMIN_ROLE');

• Open a command prompt window and type mysql -h localhost -u root -p [assuming mysql\bin is included in the PATH environment variable]

• Supply the root password in the mysql window that comes up

mysql> CREATE DATABASE openreports;
mysql> GRANT ALL ON openreports.* TO oreports@localhost IDENTIFIED BY 'oreports';
mysql> use openreports
mysql> \. C:\openreports-tomcat\database\schema\or_ddl_mysql.sql
mysql> \. C:\openreports-tomcat\database\schema\quartz\tables_mysql.sql
mysql> quit

• Edit the file C:\openreports-tomcat\openreports\src\openreports.properties to have the following contents

# properties used by the Spring configuration

hibernate.dialect=org.hibernate.dialect.MySQLDialect

hibernate.jdbc.driver=com.mysql.jdbc.Driver
hibernate.jdbc.url=jdbc:mysql://localhost:3306/openreports
hibernate.jdbc.username=oreports
hibernate.jdbc.password=oreports
hibernate.jdbc.maxIdle=2
hibernate.jdbc.maxActive=5

quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate  

# use the following line for HSQLDB demo Quartz databases
#quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

# use this for all other Quartz databases
quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS WHERE LOCK_NAME = ? FOR UPDATE
   
quartz.jdbc.driver = com.mysql.jdbc.Driver
quartz.jdbc.url = jdbc:mysql://localhost:3306/openreports
quartz.jdbc.username=oreports
quartz.jdbc.password=oreports
quartz.jdbc.maxIdle=2
quartz.jdbc.maxActive=5

#number of threads that are available for concurrent execution of jobs
org.quartz.threadPool.threadCount = 5

• Edit the file C:\openreports-tomcat\openreports\WebRoot\WEB-INF\applicationContext.xml and add the following items to enable sending mail with SMTP authentication.

<bean id="mailAuthenticator" class="org.efs.openreports.util.SMTPAuthenticator">
 <constructor-arg index="0" value="email account here" />
 <constructor-arg index="1" value="password here" />
 </bean>

 <bean id="mailSession" class="javax.mail.Session" factory-method="getInstance">
 <constructor-arg index="0">
 <props>
 <prop key="mail.smtp.host">server hostname or IP here</prop> 
<prop key="mail.smtp.auth">true</prop> 
 </props>
 </constructor-arg>
 <constructor-arg index="1" ref="mailAuthenticator" />
</bean>

• Edit the file C:\openreports-tomcat\reports\datasources.xml and delete the sample mondrian datasource so that the file has the following contents.

<?xml version="1.0"?>
<DataSources>

</DataSources>

• Delete the following files from C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\.xalan-2.6.0.jar, xercesImpl-2.0.2.jar and xml-apis-2.0.2. These will be replaced with files from the xalan 2.7.1 distribution.

• Download the binary distribution of xalan 2.7.1 from http://xml.apache.org/xalan-j/downloads.html
• Unzip the package to a temporary folder e.g. c:\temp
• Copy the files xalan.jar, xercesImpl.jar, xml-apis.jar and serializer.jar from c:\temp\xalan-j_2_7_1 to the folder C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\
• If you'll be using a jasper reports version other than 3.5.1, delete the files jasperreports-3.5.1.jar and jasperreports-3.5.1-javaflow.jar from C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\. Place the files for the required jasper engine in the folder instead e.g. jasperreports-3.7.0.jar and jasperreports-3.7.0-javaflow.jar.
• Put the mysql JDBC driver in the folder C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib. You can skip this step if a copy of the driver is available in the tomcat\lib folder

• If you are using tomcat 6 and JRE 1.6, put a copy of the olap4j jar file in C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib. You can download this file from http://sourceforge.net/projects/olap4j/files/. This step is important for the environment described. If omitted, the application will deploy successfully on tomcat 5.5 but fail on tomcat 6 with an error “cannot create jdbc driver...java.lang.NoClassDefFoundError: org/olap4j/OlapWrapper...”.

• Edit the C:\openreports-tomcat\openreports\WebRoot\WEB-INF\applicationContext.xml so that the environment bean section looks something like this

<bean id="environment"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
    lazy-init="false">
    <property name="ignoreResourceNotFound" value="true" />
    <property name="locations">
      <list>        
            <value>/WEB-INF/openreports.properties</value>        
      </list>
    </property>
  </bean> 

• Move the file openreports.properties from C:\openreports-tomcat\openreports\src to C:\openreports-tomcat\openreports\WebRoot\WEB-INF.

• Edit the file C:\openreports-tomcat\openreports\build.xml to change the deployment path of the openreports.properties file. Add an entry to the “war” target fileset for this file

<target name="war" depends="jar">
 <war destfile="${deploy}/openreports.war" webxml="${web-inf}/web.xml">
  <fileset dir="${WebRoot}">
   <include name="**/openreports.properties" />

• These last 3 steps will allow you to change the openreports database at a later time, without having to recompile the application.
• Open a command prompt window and navigate to C:\openreports-tomcat\openreports
• Type ant war and hit enter.
• Wait for the task to complete with a BUILD SUCCESSFUL message
• Start the tomcat service
• Navigate to localhost:8080/ and login to the web application manager.
• Deploy the updated war file found at C:\openreports-tomcat\tomcat\webapps\openreports.war
• Nagivate to localhost:8080/openreports
• Login using username admin and password admin.
• Click on the Administration button, then the Settings link and set the following properties

Base directory – C:\openreports-tomcat\reports [where jasper or birt report files will reside]
Report generation directory – C:\openreports-tomcat\reports\generated
Temp directory – C:\openreports-tomcat\temp
Query report max rows – 1000

• If you would like to use an independent tomcat server with the sample hsqldb database that comes with openreports, make sure that the version of the hsqldb driver referenced in the C:\openreports-tomcat\database\start-database.bat file used to start up the database is the same one used in the tomcat installation e.g. in the tomcat\lib folder. Otherwise you may get a “connection is broken...” error when starting the application if the openreports.properties file points to the sample database.

Using BIRT reports
OpenReports 3.2.0 supports BIRT 2.3.2.

• Copy the JDBC drivers for the databases used by the BIRT reports to the folder tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.r232_v20090212\drivers
• When creating a new report in OpenReports, when adding a report template, add the .rptdesign report file.
• To design birt reports, use the BIRT RCP designer 2.3.2. You can download it from http://download.eclipse.org/birt/downloads/index2.3.2.php

When exporting to xls, BIRT uses Excel 2003 xml format [birt uses office 2003 xml for all formats but OpenReports doesn't have an option for exporting to doc or ppt]. This doesn't open properly by default in OpenOffice. To generate binary xls files, you can use the Tribix xls emitter for BIRT.

• Download xls-emitter-bin_2.3.1.zip from http://sourceforge.net/projects/tribix/files/
• Download the Apache POI library. Download the file poi-bin-3.2-FINAL-20081019.zip from http://archive.apache.org/dist/poi/release/bin/
• Extract xls-emitter-bin_2.3.1.zip to a temporary location e.g. C:\temp
• Extract poi-bin-3.2-FINAL-20081019.zip to a temporary location e.g. C:\temp
• To use the tribix emitter in the Eclipse RCP Designer, copy the contents of c:\temp\xls-emitter-bin_2.3.1\plugins to birt-rcp-report-designer-2_3_2\plugins. [a jar file and a folder]
• Copy c:\temp\poi-3.2-FINAL\poi-3.2-FINAL-20081019.jar to birt-rcp-report-designer-2_3_2\plugins\org.uguess.birt.report.engine.emitter.xls_2.3.1.200812291511\lib
• Rename the file birt-rcp-report-designer-2_3_2\plugins\org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_v20090601.jar e.g to not-used-org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_v20090601.jar
• Open the BIRT designer and run a report as XLS.

 

• To use the tribix emitter in OpenReports, stop tomcat and copy the contents of c:\temp\xls-emitter-bin_2.3.1\plugins to tomcat\webapps\openreports\WEB-INF\platform\plugins.
• Copy c:\temp\poi-3.2-FINAL\poi-3.2-FINAL-20081019.jar to tomcat\webapps\openreports\WEB-INF\platform\plugins\org.uguess.birt.report.engine.emitter.xls_2.3.1.200812291511\lib
• Rename the file tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_20090202.jar e.g. to not-used-org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_20090202.jar
• Restart tomcat
• When you export BIRT reports to XLS, they will now use the tribix xls emitter.

Using JasperReports
OpenReports 3.2.0 supports jasperreports 3.5.1 by default.

• To design reports using the jasperreports engine, install and use iReport. You can download it from http://sourceforge.net/projects/ireport/files/ 
• If you use an iReport version later than 3.5.1 to design the reports, reports you deploy to OpenReports may not display any data. This is because the same version of jasperreports used to compile a report should be used to run it.

To use a later version of jasperreports e.g. 3.7.0, use the following additional steps.

• Download the files jasperreports-3.7.0.jar and jasperreports-javaflow-3.7.0.jar from http://sourceforge.net/projects/jasperreports/files/
• Copy these two files to the tomcat\webapps\openreports\WEB-INF\lib folder
• Delete the files jasperreports-3.5.1.jar and jasperreports-3.5.1-javaflow.jar from tomcat\webapps\openreports\WEB-INF\lib. Stop tomcat if the files are in use and can't be deleted.
• When creating a new report in OpenReports, when adding a report template, add the .jasper report file rather than the .jrxml file. To get a .jasper file, preview the report from within iReport.

If you need to compile a jrxml file to a particular jasper version, e.g. a report to be used with the jasperreports 3.5.1 engine in OpenReports, download and use the iReport version corresponding to the required jasper version.