Thursday, July 15, 2010

Using html2ps

html2ps is a perl script available from http://user.it.uu.se/~jan/html2ps.html that can be used to convert html to postscript [and then you can convert the postscript to pdf].

Prerequisites
Perl
Ghostscript
GSView [for viewing postscript files]
ImageMagick

Versions used
html2ps – 1.0 beta7
ActivePerl – 5.8.8.820
Ghostscript – 8.71
GSView – 4.9
ImageMagick - ImageMagick-6.6.2-3-Q16-windows-dll


Installation
  • Download the zip package from http://user.it.uu.se/~jan/html2ps.html
  • Extract the zip package to c:\
  • Rename the extracted folder to c:\html2ps
  • Download ghostscript from http://pages.cs.wisc.edu/~ghost/
  • Run the ghostscript installation file
  • Add ghostscript to the system path. Add both the bin and lib directories
  • Download the windows binary release exe of InstallMagick from http://www.imagemagick.org/script/index.php
  • Install InstallMagick and specify that the imagemagick installation path should be included in the system path
  • Ensure the perl bin directory is in the system path
  • Open a command prompt window
cd c:\html2ps
perl install
  • Accept the installation script defaults. When asked to enter the name of this directory, type c:\html2ps
  • Once the install script is finished, edit the file c:\html2ps\html2ps. Replace the line $tmpname=$posix?POSIX::tmpnam():"h2p_$$"; with
$tmpname=$posix?POSIX::tmpnam():"h2p_$$";
if($^O =~ m/win/i) {
$tmpname="h2p_$$";}
  • Ghostscript and ImageMagick aren't required for html2ps to work, but some configuration parameters and documents may need them, or other additonal libraries.


Converting a html file to postscript
Example converting the html2ps user guide
cd c:\html2ps
perl html2ps -d -D -f sample -o test.ps html2ps.html


Converting the postscript file to pdf
Use ps2pdf that comes with the ghostscript installation
ps2pdf test.ps test.pdf


Locating the table of contents at the beginning of the document
You can modify many aspects of the postscript file generated by html2ps. This would involve creating and modifying a configuration file. The file "sample" is one such configuration file. You can make a copy of it and add your own modifications to customize the file generated. Review the html2ps user guide html document for configuration options. As an example, you can set the table of contents to be generated at the start of the document instead of the end. Modify the file sample, editing the toc line to the following

option {
toc: hb;

Run html2ps and ps2pdf to confirm that the table of contents is now at the beginning of the document.

perl html2ps -d -D -f sample -o test.ps html2ps.html
ps2pdf test.ps test.pdf


Correcting display of euro signs in pdf bookmarks
If generating a table of contents, after converting the postscript file to pdf the pdf bookmarks may be displayed with two euro signs at the beginning of the bookmark text. To correct this, edit the file html2ps. Replace the line $dh.="/h$nhd [($hind\\240\\240)($htxt)] D\n"; with

$dh.="/h$nhd [($hind\\56\\40)($htxt)] D\n";

Replace the line $toc.="$hv NH le{$nref($hind\\240\\240)$hv C($htxt)$nref 1 TN()EA()BN}if\n"; with

$toc.="$hv NH le{$nref($hind\\56\\40)$hv C($htxt)$nref 1 TN()EA()BN}if\n";


Processing a file with images
If an html file contains links to images held locally and referenced with relative links e.g. src="images/sample.png", use the base option when calling html2ps providing the base url to be appended to all relative links for images.

perl html2ps -d -D -b file:///c:/some/path/ -f sample -o example.ps c:\some\path\example.html


Changing the default look of hyperlinks
By default, text for all links, both to internal document sections and to external web locations, are rendered in a final pdf surrounded by boxes. To have them rendered without the boxes, add a definition to the style sheet definitions in the configuration file used.

A:link { color: blue }
The color must be something other than black. In addition, when running html2ps, you'll need to add the -U parameter.

perl html2ps -d -D -U -f myconfig.txt -o example.ps example.html


Left aligning H1 elements
The example configuration file "sample" provided specifies that H1 elements are centred. If left aligning is required, remove the text-align: center portion of the H1 style rule.


Undesired blank pages
By default, an extra (empty) page is printed, when necessary, to ensure that the title page, the table of contents, and the document itself will start on odd pages. This is typically desirable for double sided printing. If this is not desired, add the extrapage flag to the @html2ps block of the configuration file, setting it to 0.

@html2ps {
extrapage: 0;


Starting new pages
You can have a page break inserted anywhere in the html text, e.g. before H1 elements. To do this, modify the source html and insert <!--NewPage-->


Using CSS
html2ps ignores css contained in the html document. You can define styles in the configuration file. Only a subset of css is supported by html2ps. This subset is outlined in the user guide, in the CSS2 blocks section.


Using custom colours
By default html2ps only recognizes 16 colours. These are defined in the colour block of the html2ps file. To use additional colours, e.g. in css rules, edit your configuration file. In the @html2ps block, add a colour block with the custom colours you use in the style rules. e.g.
Colour{
brown: A52A2A;
}


The user guide that comes with html2ps has explanations for all possible options for modifying the look of the generated postscript, and possibly eventual pdf document.

Tuesday, May 11, 2010

Using Quartz

Quartz is an open source job scheduling library that can be used within a Java application.

Using quartz in a Java EE application

Prerequisistes
JRE
Servlet engine
RDBMS + JDBC driver for storing jobs in a database

Versions used
JRE 1.6.0_20
Apache Tomcat 6.0.20
MySQL 5.0.45
MySQL connector/J 5.1.10
Quartz 1.8.0

Steps
  • Download the package from the quartz website, http://www.quartz-scheduler.org/
  • Unzip the package to a temporary location e.g. C:\temp
  • Add the quartz tables to your application's database schema. The scripts with the quartz table schemas will be in c:\temp\quartz-1.8.0\docs\dbtables.
C:\> mysql -h localhost --database=mydb --user=dbuser --password=dbpassword
mysql> \.  c:\temp\quartz-1.8.0\docs\dbtables\tables_mysql_innodb.sql
mysql> quit
  • Create indexes on the quartz tables just created by running the following additional script
create index idx_qrtz_t_next_fire_time on qrtz_triggers(NEXT_FIRE_TIME);
create index idx_qrtz_t_state on qrtz_triggers(TRIGGER_STATE);
create index idx_qrtz_t_nf_st on qrtz_triggers(TRIGGER_STATE,NEXT_FIRE_TIME);
create index idx_qrtz_ft_trig_name on qrtz_fired_triggers(TRIGGER_NAME);
create index idx_qrtz_ft_trig_group on qrtz_fired_triggers(TRIGGER_GROUP);
create index idx_qrtz_ft_trig_n_g on qrtz_fired_triggers(TRIGGER_NAME,TRIGGER_GROUP);
create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(INSTANCE_NAME);
create index idx_qrtz_ft_job_name on qrtz_fired_triggers(JOB_NAME);
create index idx_qrtz_ft_job_group on qrtz_fired_triggers(JOB_GROUP);
  • Copy the file c:\temp\quartz-1.8.0\quartz-all-1.8.0.jar to your application's web-inf\lib folder e.g. tomcat\webapps\myapp\web-inf\lib
  • Copy all the jar files in c:\temp\quartz-1.8.0\lib to tomcat\webapps\myapp\web-inf\lib
  • Create a file named quartz.properties in myapp\web-\classes with the following details
# quartz configuration

# jobstore
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX

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

# datasource
org.quartz.jobStore.dataSource = anyString

org.quartz.dataSource.anyString.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.anyString.URL = jdbc:mysql://localhost/mydb
org.quartz.dataSource.anyString.user = dbuser
org.quartz.dataSource.anyString.password = dbpassword
org.quartz.dataSource.anyString.validationQuery=select 1

# thread pool
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 5

# disable quartz version update check
org.quartz.scheduler.skipUpdateCheck=true
  • Create a file named log4j.xml in myapp\web-\classes with the following details
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

  <appender name="default" class="org.apache.log4j.ConsoleAppender">
    <param name="target" value="System.out"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="[%p] %d{dd MMM yyyy HH:mm:ss.SSS} %t [%c]%n%m%n%n"/>
    </layout>
  </appender>
    
 <logger name="org.quartz">
   <level value="info" />     
 </logger>

  <root>
    <level value="warn" />
    <appender-ref ref="default" />
  </root>
  
</log4j:configuration>
  • Create a servlet class to be running the scheduler
package my.app;

import javax.servlet.*;
import javax.servlet.http.*;

import org.quartz.impl.StdSchedulerFactory;
import org.quartz.utils.*;

import org.quartz.*;

public class TestScheduler extends HttpServlet
{
Scheduler myscheduler;
public void init(ServletConfig config) throws ServletException
{
try
{
//start scheduler and run a test job
// Initiate a Schedule Factory
SchedulerFactory schedulerFactory = new StdSchedulerFactory();
        // Retrieve a scheduler from schedule factory
        myscheduler = schedulerFactory.getScheduler();
                        
        // Initiate JobDetail with job name, job group, and executable job class
        JobDetail job1 = new JobDetail("myjobDetail", "myjobDetailGroup", MyJob.class);
        // Initiate SimpleTrigger with its name and group name
        SimpleTrigger simpleTrigger = new SimpleTrigger("mysimpleTrigger", "mytriggerGroup");
        
//example setting int parameter for the job
job1.getJobDataMap().put("int-parameter-name",5);

//schedule the job and start the scheduler
myscheduler.scheduleJob(job1, simpleTrigger);
        
        // start the scheduler
        myscheduler.start();
}
catch(Exception e)
{
System.err.println(e);
}
}

public void destroy()
{
//shut down the scheduler
try
{
myscheduler.shutdown(true);
}
catch(Exception e)
      {
      System.err.println(e);
      }
}
}
  • Include the scheduler class in the application's web.xml file so that it runs on startup
 <servlet>
  <servlet-name>TestScheduler</servlet-name>
  <servlet-class>my.app.TestScheduler</servlet-class>
  <!-- Load this servlet at server startup time. Number not special. Just indicates the sequence of loading servlets -->
  <load-on-startup>3</load-on-startup>
 </servlet>
  • Create a class that will be doing the work. The job class. This class needs to implement the org.quartz.job interface
package my.app;

import org.quartz.*;

public class MyJob implements Job
{

//no-argument public constructor
public MyJob()
{
}

//execute method of job interface that does the work
public void execute (JobExecutionContext context) throws JobExecutionException
{
//do anything here.

//you can take parameters passed by the scheduler and use them e.g
JobDataMap dataMap=context.getMergedJobDataMap();
int myIntVariable;
myIntVariable=dataMap.getInt("int-parameter-name");

if (myIntVariable==1)
{ 
//do something
}
else
{
//do something else
}
}
}
  • Instead of creating your own class to start the scheduler, you can use one provided by quartz. Modify the web.xml to have the following
<servlet>
    <servlet-name>
        QuartzInitializer
 </servlet-name>
    <display-name>
        Quartz Initializer Servlet
 </display-name>
    <servlet-class>
        org.quartz.ee.servlet.QuartzInitializerServlet
 </servlet-class>
    <load-on-startup>3</load-on-startup>    
</servlet>
  • A default scheduler instance will now be automatically created and started when the application starts, and automatically shut down when the application is stopped.

  • To access this scheduler within the application e.g. In a jsp page, you can retrieve the scheduler instance from the servlet context. You can have the following
<%@ page import="org.quartz.*,org.quartz.impl.*,org.quartz.utils.*,org.quartz.ee.servlet.QuartzInitializerServlet" %>

<%
  StdSchedulerFactory factory = (StdSchedulerFactory) getServletConfig().getServletContext().getAttribute(QuartzInitializerServlet.QUARTZ_FACTORY_KEY);
  Scheduler scheduler=factory.getScheduler();
  
  // Initiate JobDetail with job name, job group, and executable job class
        JobDetail job1 = new JobDetail("myjobDetail", "myjobDetailGroup", MyJob.class);
        // Initiate SimpleTrigger that will fire immediately
        SimpleTrigger simpleTrigger = new SimpleTrigger("mysimpleTrigger", "mytriggerGroup");        
        job1.getJobDataMap().put("int-parameter-name",5);                
        
        scheduler.scheduleJob(job1, simpleTrigger);
  %>
  • You can create jobs and triggers according to the application's logic and user inteface components used and then schedule using the scheduler object.


Deleting jobs and triggers
You can't add a trigger or job if another one with a similar name and group exists. Use methods of the scheduler object to do the deletion. An exception is not raised if the job or trigger doesn't exist.
scheduler.deleteJob("job name","job group");
scheduler.unscheduleJob("trigger name","trigger group");

Checking if a cron expression is valid
If using a cron trigger, and the expression provided isn't valid, an exception will be raised when creating the trigger. To avoid this you can check whether the expression is valid before creating the trigger object. There's a static method in the CronExpression class for this.
if (CronExpression.isValidExpression(myCronString)){

Determining next run job run time
You can determine the next time a job will run using the trigger's getFireTimeAfter method
java.util.Date nextRunDate=myTrigger.getFireTimeAfter(new java.util.Date())

Or within the job implementation's execute method,
public void execute(JobExecutionContext context) throws JobExecutionException {

java.util.Date nextRunDate=context.getTrigger().getFireTimeAfter(new java.util.Date());

}

Setting job end date
You can set the date on which a job should start or end using the associated trigger's setStartTime and setEndTime methods. By default, a trigger's start time is the time the object is instantiated with no end date. One can set the end date without specifying the start date and vice versa. The end date can't be before the start date, else an exception will be thrown.

Setting quarz properties in code
Instead of having the quartz configuration properties residing in a properties file, you can create a scheduler instance with the properties defined from code. For instance if you don't want to have the database username/password in clear text in the properties file. You'll need to create a java.util.Properties object, populate it with all the relevant quartz properties and then pass the properties object to the StdSchedulerFactory constructor e.g.
import java.util.*;
import org.quartz.*;
import org.quartz.impl.*; 

props=new Properties();
props.setProperty("org.quartz.threadPool.threadCount","10");
//...set other properties

//create scheduler instance 
SchedulerFactory schedulerFactory = new StdSchedulerFactory(props);      
org.quartz.Scheduler scheduler = schedulerFactory.getScheduler();
scheduler.start(); 

//if doing this from a servlet that's loaded on startup, you can put the scheduler instance in the servlet context so that you can access it from anywhere within the application

//save scheduler in the servlet context, to make it accessible throughout the application
getServletConfig().getServletContext().setAttribute("myscheduler",scheduler);

//to access the scheduler elsewhere in the application e.g. to schedule new jobs
Scheduler scheduler=(Scheduler) getServletConfig().getServletContext().getAttribute("myscheduler");
scheduler.scheduleJob(someJobObject, someTriggerObject);

//make sure to call the scheduler's shutdown method in the servlet's destroy method
If creating the scheduler instance like this, you won't need the QuartzInitializerServlet entry in the web.xml file.

Friday, April 16, 2010

Installing Open Source Job Scheduler 1.3.6

Open Source Job Scheduler is an open source application for scheduling and monitoring tasks.

Prerequisites
JRE
RDBMS + JDBC driver
Web server with PHP configured
PHP

Versions used
Apache 2.2.8
MySQL 5.0.45
MySQL Connector/J 5.1.10
PHP 5.2.8
JRE 1.6.0_18

Steps
  • Download the zip package from the job scheduler website, http://jobscheduler.sourceforge.net/
  • Create a temporary directory e.g c:\temp\scheduler
  • Unzip the file to the temporary directory e.g. C:\temp\scheduler
  • Open a command prompt
  • Type mysql -h localhost -u root -p
  • Enter the root password
mysql> create database scheduler;
mysql> grant all on scheduler.* to scheduler@localhost identified by "scheduler";
mysql> quit
cd c:\temp\scheduler
java -jar scheduler_win32.jar
  • The setup program will start
  • Change the installation path from program files e.g. to c:\scheduler
  • Be sure to include the managed jobs, database and web interface components when installing
  • Enter other required details and create a copy of the install script at the end.
  • Edit the file apache\conf\httpd.conf and add the following
Alias /scheduler/logs "c:/scheduler/logs"
Alias /scheduler "c:/scheduler/web"

<Directory "c:/scheduler/web">
    AllowOverride None    
    Options Indexes FollowSymLinks ExecCGI
    Order allow,deny
    Allow from all
</Directory>

<Directory "c:/scheduler/logs">
    AllowOverride None    
    Options Indexes FollowSymLinks
    Order allow,deny
    Allow from all
</Directory>
  • Restart apache
  • Using a browser, navigate to localhost/scheduler/index.htm
  • In the login screen enter sos for the unit, admin for username and leave the password blank
  • Review the documents in the c:\scheduler\doc\en folder for information on how to create and manage jobs. The quickstart is a good place to start.

If you get an error after logging in e.g. Error while connecting to the scheduler... or a message like "It could not commit any Job Scheduler to this site for monitoring", try the following.
  • Edit the file c:\scheduler\web\packages\scheduler\sos_scheduler_network.inc.php. Modify the normalize_host function to look as follows.
  function normalize_host( $host ) {
    return gethostbyname($host);
    /*
    $ip = gethostbyname($host);
    if( ereg( '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$', $ip ) ) {
      return gethostbyname(gethostbyaddr($ip));
    } else {
      return '';
    }
    */
  }
  • In the same file also edit the line that sets the ip host to
$this->network_schedulers[$ip_port]['host']= $this->normalize_host(APP_SCHEDULER_HOST);
  • Edit the file c:\scheduler\web\scheduler_monitor.php. Edit the line that sets the ip to
$ip=gethostbyname($spooler_host);


Scheduling Pentaho Data Integration (Kettle) jobs
If scheduling kettle transformations or jobs, the scheduler will think the job has encountered an error because some text is written to stderr and a warning email is sent as a result. To avoid this, when configuring the job, after settting the command parameter, set the ignore stderr parameter to Yes.

Another way to avoid the warning email would be to modify the batch file that runs the job or transformation to redirect stderr to the same file as stdout [using 2>&1]. e.g.
cd C:\Kettle
kitchen.bat /file:"C:\path\job.kjb" > "C:\path\job.log" 2>&1

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.

Installing OpenI 2.0 RC2

OpenI is an open source business intelligence application. It's main emphasis is on providing OLAP analysis, although it can also do regular reporting by allowing publishing of jasper report files.

Prerequisites
Tomcat 5.5+ [with JDBC driver in the tomcat\lib directory]
JRE 1.5+
OLAP server
RDBMS + JDBC driver

Versions used
Tomcat 6.0.20
JRE 1.6.0_18
Mondrian 3.1.5
MySQL 5.0.45
MySQL Connector/J 5.1.10

Steps
  • Download the openi-tomcat package from sourceforge, http://sourceforge.net/projects/openi/files/
  • Extract the zip package to a temporary folder e.g. C:\temp
  • Edit the file tomcat\conf\tomcat-users.xml file and add some details for openi users.
<role rolename="openi"/>
<role rolename="app_admin"/>
<role rolename="foodmart_user"/><role rolename="foodmart_admin"/>
<user username="openiadmin" password="password" roles="openi,app_admin"/>
<user username="foodmartadmin" password="password" roles="openi,foodmart_admin"/>
<user username="foodmartuser" password="password" roles="openi,foodmart_user"/> 
  • Edit the tomcat\webapps\mondrian\web-inf\datasources.xml file. Modify the default datasource [with datasourcename of Provider=Mondrian;DataSource=MondrianFoodMart;].
  • Edit the DatasourceInfo element to put the appropriate details for the foodmart sample database i.e database url, username and password. e.g.
<DataSourceInfo>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
  • Start tomcat
  • Copy the file c:\temp\openi\openi.war to the tomcat\webapps folder. This will cause the application to be deployed
  • Using a browser, navigate to localhost:8080/openi
  • Login with username/password of openiadmin/password
  • Openi needs a folder in which to place project details. You'll be prompted to create one e.g .in c:\openi-projects. Click on the create sample project button to do this.
  • This will create the folder with a sample project that's made for the foodmart sample database.
  • Click on the Logout button
  • Edit the file c:\openi-projects\foodmart\project.xml and modify the jdbc_default string entry with the details for the sample foodmart database e.g. Put the correct database url, username and password. The details for the mondrian data source entry should match those in the tomcat\webapps\mondrian\web-inf\datasources.xml file, if this wasn't changed when installing the mondrian web application.
  • Create a new environment variable called PROJECTS_DIR with the value c:\\openi-projects.
  • Using a browser, navigate to localhost:8080/openi
  • Login with username/password of openiadmin/password
  • You can now view the analyses and reports of the sample foodmart project.

Installing Mondrian 3.1.5

Mondrian is an open source ROLAP server. It uses the ROLAP scheme of storing data i.e. It doesn't have a data storage engine of its own. The data is stored in an external relational database.

Prerequisites
JRE 1.4+
Tomcat 5.5+ [with JDBC driver in the tomcat\lib directory]
RDMBS + JDBC driver

Versions used
JRE 1.6.0_18
Tomcat 6.0.20
MySQL 5.0.45
MySQL Connector/J 5.1.10

Steps
mysql> create database foodmart;
mysql> grant all on foodmart.* to foodmart@localhost identified by "foodmart";
mysql> use foodmart;
mysql> \. c:\temp\foodmart_mysql.sql
mysql> quit
  • Start tomcat
  • Copy c:\temp\mondrian-3.1.5.13307\lib\mondrian.war to the tomcat\webapps directory. This will cause the mondrian application to be deployed.
  • Edit the the fourhier.jsp, mondrian.jsp, colors.jsp and arrows.jsp files in the tomcat\webapps\mondrian\web-inf\queries directory. Edit the following line
<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml">
  • Replace it with details of the mysql foodmart database you've just created
<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml"> 
  • Edit the the testrole.jsp files in the tomcat\webapps\mondrian\web-inf\queries directory. Edit the following line
<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml" role="California manager">
  • Replace it with details of the mysql foodmart database you've just created
<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml" role="California manager">
  • Edit the tomcat\webapps\mondrian\web-inf\datasources.xml file. Edit the datasourceinfo tag of the default data source to have the details of the sample foodmart database. e.g.
<DataSourceInfo>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
  • Edit the tomcat\webapps\mondrian\web-inf\mondrian.properties file. Edit the connectionstring property to have the details of the sample foodmart database. e.g.
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;
  • Edit the tomcat\webapps\mondrian\web-inf\web.xml file. Modify all occurrences of the string @mondrian.webapp.connectString@, replacing it with details for connecting to the sample foodmart database. This will be required for the connectString context parameter and the MDXQueryServlet servlet sections of the file e.g
<context-param>
    <param-name>connectString</param-name>
    <param-value>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;</param-value>
  </context-param>

  <servlet>
    <servlet-name>MDXQueryServlet</servlet-name>
    <servlet-class>mondrian.web.servlet.MdxQueryServlet</servlet-class>
    <init-param>
      <param-name>connectString</param-name>
      <param-value>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;</param-value>
    </init-param>
  </servlet>
  • Stop tomcat
  • 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
  • Delete the file tomcat\webapps\mondrian\web-inf\lib\xalan.jar
  • Copy the files xalan.jar, xercesImpl.jar, xml-apis.jar and serializer.jar from c:\temp\xalan-j_2_7_1 to the folder tomcat\webapps\mondrian\WEB-INF\lib\
  • Start tomcat
  • Using your browser, navigate to localhost:8080/mondrian
  • You can now get a feel of the examples using the links provided.


Using the schema workbench
In order to create OLAP cubes, you can use the mondrian schema workbench. The schema workbench is a Java desktop application allows you to
  • Visually create and test Mondrian OLAP cube schemas, validating the schema against the cube database
  • Run example MDX queries using the schema and the database
  • Browse the cube database
Steps
  1. Download the schema workbench zip package from sourceforge, http://sourceforge.net/projects/mondrian/files/
  2. Extract the zip file to c:\
  3. Place jdbc drivers to connect to the source database in the c:\schema-workbench\drivers folder
  4. Double-click the file c:\schema-workbench\workbench.bat to start the schema workbench. You'll need to have defined a JAVA_HOME or JRE_HOME environment variable pointing to the java location.
  5. Click on the Tools | Connection menu
  6. Enter the connection details for the database
  7. You can then open the xml file that contains a mondrian schema definition for editing, or create a new schema definition.

Installing Pentaho BI Server 3.5.0

The Pentaho BI Suite Community Edition [CE] is an open source business intelligence package that includes ETL, analysis, metadata, and reporting capabilities. The suite is composed of server components and client tools. The server components [also referred to as the BI Platform] consist of the BI Server, the administration console and the user console. The BI server is responsible for management and delivery of reports.

Some of the Pentaho client tools include:
  • Report Designer: Used to create formatted reports. Report Designer offers far more flexibility and functionality
    than the ad hoc reporting capabilities of the Pentaho User Console.
  • Design Studio: An Eclipse-based tool that enables you to hand-edit a report or analysis view
    xaction file. Generally, people use Design Studio to add modifications to an existing report that
    cannot be added with Report Designer. Design Studio is also required to enable report scheduling, e-mail distribution, dynamic and static prompting, and more.
  • Metadata Editor: Enables you to add a custom metadata layer to an existing data source.
    Usually you would do this for a data source that you intend to use for analysis or reporting; it's
    not required, but it makes it easier for business users to parse the database when building a
    query.
  • Pentaho Data Integration: Also known as Kettle. An extract, transform, and load (ETL) tool, which enables
    you to access and prepare data sources for analysis, data mining, or reporting.
  • Schema Workbench: A graphical tool that helps you create ROLAP schemas for analysis.

These steps are for setting up the server components.

Prerequisites
JRE or JDK 1.5+

Versions used
Pentaho BI Server 3.5.0
Pentaho Report Designer 3.5.0
JRE – 1.6.0.17

Steps
  • Download the business intelligence server zip file from SourceForge, http://sourceforge.net/projects/pentaho/files/
  • Download the report designer zip file from the same SourceForge location
  • Create a folder c:\pentaho\server and extract the BI server zip file here.
  • Create a folder c:\pentaho\design-tools and extract the report designer zip file here.
  • 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.
  • Note that the BI server package seems to come with it's own JRE which will be located in C:\Pentaho\server\biserver-ce\jre.
  • Edit the file C:\Pentaho\server\biserver-ce\tomcat\bin\setclasspath.bat. Modify the first four lines of the gotJreHome label so that they appear as follows. [tools.jar comes with JDK, not JRE]
:gotJreHome
if not exist "%JRE_HOME%\bin\java.exe" goto noJavaHome
if not exist "%JRE_HOME%\bin\javaw.exe" goto noJavaHome
REM if not exist "%JRE_HOME%\lib\tools.jar" goto noJreToolsJar
REM set CLASSPATH=%JRE_HOME%\lib\tools.jar
  • Edit the files start-pentaho.bat and stop-pentaho.bat located in C:\Pentaho\server\biserver-ce\. Comment out the line setting the value of the JAVA_HOME variable so that it looks as below
REM set JAVA_HOME=%_PENTAHO_JAVA_HOME%
  • The preceding two steps are only required if you only have JRE and don't have JDK
  • Edit the file C:\Pentaho\server\biserver-ce\pentaho-solutions\system\publisher_config.xml. Enter a password [in clear text] in the element. This is the password that will be used when publishing reports from the report designer to the BI server. Don't leave it blank.
  • Open a command prompt window and navigate to c:\Pentaho\server\biserver-ce. Type start-pentaho and hit enter. This will start the HSQLDB database the BI server uses internally in a new command prompt window and start the tomcat server bundled with the BI server also in a new command window.
  • Wait for the tomcat server to finish starting up. When finished the tomcat command window will display something like INFO: Server startup in 77980 ms
  • Open a command prompt window and navigate to c:\Pentaho\server\administration-console. Type start-pac and hit enter. This will start the pentaho administration console.
  • Open a browser window and navigate to http://localhost:8099 to access the pentaho administration console. If prompted for login credentials, use “admin” for the username and “password” for the password.
  • Open a browser window and navigate to http://localhost:8080/pentaho to access the pentaho user console
  • If port 8080 is already in use, for instance by another web server, modify the file C:\Pentaho\server\biserver-ce\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. Restart the server and include the new port number in the URL to access the user console.
  • Click on the login button in the user console and select the sample user Joe (admin) to login. The username and password are automatically filled in the form. This user has a username of “Joe” and a password of “password”.
  • If you'll need to access the user console from different machines, edit the file C:\Pentaho\server\biserver-ce\tomcat\webapps\pentaho\WEB-INF\web.xml. Change the base-url parameter, replacing “localhost” with the server's hostname or IP address.
  • To start the report designer, open a command prompt window and navigate to C:\Pentaho\design-tools\report-designer. Type report-designer and hit enter.
  • To stop the BI server, open a command prompt window and navigate to c:\Pentaho\server\biserver-ce. Type stop-pentaho and hit enter. OR double click on the stop-pentaho.bat file.
  • To stop the administration console, double click the file c:\Pentaho\server\administration-console\stop-pentaho.bat file.
  • Note that if you use IE8 for the browser, intranet settings may not be enabled for localhost, and some features of the user console will not work properly. To correct this, select the Tools | Internet Options menu. If the main menu [File, Edit etc] isn't displayed on IE, press the alt key to get it to display. Select the security tab, then the Local intranet zone and click on the sites button. Uncheck the Automatically detect intranet network box and click on the Advanced button. Type in http://localhost and click on the Add button.
  • To enable emailing of reports, edit the file C:\Pentaho\server\biserver-ce\pentaho-solutions\system\smtp-email\email_config.xml, adding the details of the email server.