Pentaho reporting - How to

How to create master report

ResourceManager manager = new ResourceManager();
manager.registerDefaults();
Resource res = manager.createDirectly("res://reportTemplates/"+ templateFileName, MasterReport.class);
MasterReport report = (MasterReport) res.getResource();

How to set Resource bundle

Locale locale = new Locale(client.language, client.country);

DefaultResourceBundleFactory bundle = new DefaultResourceBundleFactory(locale);

report.setResourceBundleFactory(bundle);

public class DefaultResourceBundleFactory implements ResourceBundleFactory {

    private Locale locale;

    public DefaultResourceBundleFactory(Locale locale) {

        this.locale = locale;

    }

    @Override

    public Locale getLocale() {

        return locale;

    }

    @Override

    public ResourceBundle getResourceBundle(String key) {

        return ResourceBundle.getBundle("messages", locale);

    }

    @Override

    public TimeZone getTimeZone() {

        return TimeZone.getDefault();

    }

}

How to set query

import org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory;

SQLReportDataFactory dataFactory = new SQLReportDataFactory(dbConnection);
dataFactory.setQuery("query", query());
report.setDataFactory(dataFactory);
report.setQuery("query");

How to show date and time the report gets generated

Define a message-field. Type:message. Value: ${report.date, date, dd MM yyyy @ hh:mm}

Spring Quartz integration

In this blog I will discuss the things you need to do for spring-quartz integration. For those of you who don't know what Quartz is please refer to the this site http://quartz-scheduler.org/.



Quartz is a full-featured, open source job scheduling service that can be integrated with, or used along side virtually any Java EE or Java SE application - from the smallest stand-alone application to the largest e-commerce system. Quartz can be used to create simple or complex schedules for executing tens, hundreds, or even tens-of-thousands of jobs; jobs whose tasks are defined as standard Java components that may execute virtually anything you may program them to do. The Quartz Scheduler includes many enterprise-class features, such as JTA transactions and clustering.Quartz is freely usable, licensed under the Apache 2.0 license.


pom.xml 


The dependency which need to be defined for Quartz is give below

<dependency>

<groupId>org.quartz-scheduler</groupId>

      <artifactId>quartz</artifactId>

      <version>1.7.3</version>

</dependency>

Please check if a later version is available.

spring configuration

In your spring configuration file you need to define this bean

<bean id="scheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean" lazy-init="false">
    <property name="applicationContextSchedulerContextKey" value="applicationContext"/>
    <property name="dataSource" ref="dataSource"/>
    <property name="transactionManager" ref="transactionManager"/>
    <property name="overwriteExistingJobs" value="true"/>
    <property name="autoStartup" value="true"/>
    <property name="quartzProperties">
         <props>
             <prop key="org.quartz.scheduler.instanceName">QuartzScheduler</prop>
             <prop key="org.quartz.scheduler.instanceId">AUTO</prop>
             <prop key="org.quartz.jobStore.misfireThreshold">30000</prop>
             <prop key="org.quartz.jobStore.class">org.quartz.impl.jdbcjobstore. JobStoreTX</prop>
             <prop key="org.quartz.jobStore.driverDelegateClass">org.quartz.impl. jdbcjobstore. StdJDBCDelegate</prop>
             <prop key="org.quartz.jobStore.tablePrefix">table_name_</prop>
             <prop key="org.quartz.jobStore.isClustered">true</prop>
             <prop key="org.quartz.threadPool.class">org.quartz.simpl.SimpleThreadPool</prop>
             <prop key="org.quartz.threadPool.threadCount">5</prop>
             <prop key="org.quartz.threadPool.threadPriority">5</prop>
         </props>
     </property>
</bean>

• Change the value of dataSource and transactionManager as defined in your spring configuration
• Change the Quartz properties accordingly.

Your job should extends org.springframework.scheduling.quartz.QuartzJobBean and override the executeInternal method.

public class ScheduleReportJob extends QuartzJobBean {

    @Override
    protected void executeInternal(JobExecutionContext context) throws JobExecutionException {
        SchedulerContext  schedulerContext = null;
        try {
            schedulerContext = context.getScheduler().getContext();
            ApplicationContext appCtx = (ApplicationContext)  schedulerContext.get("applicationContext");
            Service service = (Service) appCtx.getBean("service");
            String jobId = (String) context.getJobDetail().getJobDataMap().get("JOB_ID");
            service.generateScheduledReport(jobId);
        } catch (SchedulerException e) {
                 throw new RuntimeException(e);
            }
        }
}


There are few tables which you need to create in your database. Below is the create statements for these.



CREATE TABLE t_geo_sdl_job_details
    (
      JOB_NAME  VARCHAR2(200) NOT NULL,
      JOB_GROUP VARCHAR2(200) NOT NULL,
      DESCRIPTION VARCHAR2(250) NULL,
      JOB_CLASS_NAME   VARCHAR2(250) NOT NULL,
      IS_DURABLE VARCHAR2(1) NOT NULL,
      IS_VOLATILE VARCHAR2(1) NOT NULL,
      IS_STATEFUL VARCHAR2(1) NOT NULL,
      REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,
      JOB_DATA BLOB NULL,
      PRIMARY KEY (JOB_NAME,JOB_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_job_listeners
    (
      JOB_NAME  VARCHAR2(200) NOT NULL,
      JOB_GROUP VARCHAR2(200) NOT NULL,
      JOB_LISTENER VARCHAR2(200) NOT NULL,
      PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
      FOREIGN KEY (JOB_NAME,JOB_GROUP)
  REFERENCES t_geo_sdl_job_details(JOB_NAME,JOB_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_triggers
    (
      TRIGGER_NAME VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      JOB_NAME  VARCHAR2(200) NOT NULL,
      JOB_GROUP VARCHAR2(200) NOT NULL,
      IS_VOLATILE VARCHAR2(1) NOT NULL,
      DESCRIPTION VARCHAR2(250) NULL,
      NEXT_FIRE_TIME NUMBER(13) NULL,
      PREV_FIRE_TIME NUMBER(13) NULL,
      PRIORITY NUMBER(13) NULL,
      TRIGGER_STATE VARCHAR2(16) NOT NULL,
      TRIGGER_TYPE VARCHAR2(8) NOT NULL,
      START_TIME NUMBER(13) NOT NULL,
      END_TIME NUMBER(13) NULL,
      CALENDAR_NAME VARCHAR2(200) NULL,
      MISFIRE_INSTR NUMBER(2) NULL,
      JOB_DATA BLOB NULL,
      PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
      FOREIGN KEY (JOB_NAME,JOB_GROUP)
  REFERENCES t_geo_sdl_job_details(JOB_NAME,JOB_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_simple_triggers
    (
      TRIGGER_NAME VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      REPEAT_COUNT NUMBER(7) NOT NULL,
      REPEAT_INTERVAL NUMBER(12) NOT NULL,
      TIMES_TRIGGERED NUMBER(10) NOT NULL,
      PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
      FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES t_geo_sdl_triggers(TRIGGER_NAME,TRIGGER_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_cron_triggers
    (
      TRIGGER_NAME VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      CRON_EXPRESSION VARCHAR2(120) NOT NULL,
      TIME_ZONE_ID VARCHAR2(80),
      PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
      FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES t_geo_sdl_triggers(TRIGGER_NAME,TRIGGER_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_blob_triggers
    (
      TRIGGER_NAME VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      BLOB_DATA BLOB NULL,
      PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
      FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES t_geo_sdl_triggers(TRIGGER_NAME,TRIGGER_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_trigger_listeners
    (
      TRIGGER_NAME  VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      TRIGGER_LISTENER VARCHAR2(200) NOT NULL,
      PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
      FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES t_geo_sdl_triggers(TRIGGER_NAME,TRIGGER_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_calendars
    (
      CALENDAR_NAME  VARCHAR2(200) NOT NULL,
      CALENDAR BLOB NOT NULL,
      PRIMARY KEY (CALENDAR_NAME)
  );
  
  CREATE TABLE t_geo_sdl_paused_trigger_grps
    (
      TRIGGER_GROUP  VARCHAR2(200) NOT NULL,
      PRIMARY KEY (TRIGGER_GROUP)
  );
  
  CREATE TABLE t_geo_sdl_fired_triggers
    (
      ENTRY_ID VARCHAR2(95) NOT NULL,
      TRIGGER_NAME VARCHAR2(200) NOT NULL,
      TRIGGER_GROUP VARCHAR2(200) NOT NULL,
      IS_VOLATILE VARCHAR2(1) NOT NULL,
      INSTANCE_NAME VARCHAR2(200) NOT NULL,
      FIRED_TIME NUMBER(13) NOT NULL,
      PRIORITY NUMBER(13) NOT NULL,
      STATE VARCHAR2(16) NOT NULL,
      JOB_NAME VARCHAR2(200) NULL,
      JOB_GROUP VARCHAR2(200) NULL,
      IS_STATEFUL VARCHAR2(1) NULL,
      REQUESTS_RECOVERY VARCHAR2(1) NULL,
      PRIMARY KEY (ENTRY_ID)
  );
  
  CREATE TABLE t_geo_sdl_scheduler_state
    (
      INSTANCE_NAME VARCHAR2(200) NOT NULL,
      LAST_CHECKIN_TIME NUMBER(13) NOT NULL,
      CHECKIN_INTERVAL NUMBER(13) NOT NULL,
      PRIMARY KEY (INSTANCE_NAME)
  );
  
  CREATE TABLE t_geo_sdl_locks
    (
      LOCK_NAME  VARCHAR2(40) NOT NULL,
      PRIMARY KEY (LOCK_NAME)
  );
  
  -- Seed data for QUARTZ scheduling --
  INSERT INTO t_geo_sdl_locks values('TRIGGER_ACCESS');
  INSERT INTO t_geo_sdl_locks values('JOB_ACCESS');
  INSERT INTO t_geo_sdl_locks values('CALENDAR_ACCESS');
  INSERT INTO t_geo_sdl_locks values('STATE_ACCESS');
  INSERT INTO t_geo_sdl_locks values('MISFIRE_ACCESS');