Using Scriptlet in jasper :

This blog will teach you how to use Scriptlet in jasper report.

Scriptlets :

Scriptlets act as an interface between Jasper reports and java

Using Scriptlet we can use user defined functions in jasper reports .

Scriptlet is a java class that extend one of the two following classes :

  • JRDefaultScriptlet
  • JRAbstractScriptlet

Using JRAbstractScriptlet , we should implement all the abstract metods.

Using JRDefaultScriptlet , we only need to implement the methods which we need for our project.

Since Scriptlet is a java class , so to add a sciptlet(user defined class) in jasper reports ,

  1. first step is to create a java class implementing user requirement.
  2. Then create its jar file
  3. In the iReport , go to Tools à Options àiReport à classpath ,

Add the jar files created by you in this location

  1. Then in the Scriptlet node of iReport , add the Scriptlet Name and fully qualified name of the jar that earlier you have created .


Here , in the Scriptlet Class , Reporting_Time is my class name


When we are done with creating a new scriptlet under Scriptlet node in iReport , We can see a new parameter created under parameter node ,

Named as {Name_Of_Scriptlet}_SCRIPTLET

In my case it is “Report_SCRIPTLET”

Using the above parameter we can call any methods declared inside the class.



Rupam Bhardwaj

Sorting Data In Mysql Based On Field Value Of a column

This blog will teach you how to sort Data In Mysql Based On Field Value Of a column

We can sort data based on field value of a column by using ORDER BY FIELD () in mysql.

Database : world

Table name : country

If i will execute the below query :

“select distinct Continent from country” then i will get the output as :


Let say that we want to sort the data in a specific order by “Asia,Africa,North America,South America,Antarctica,Oceania,Europe”.

So in this case we can not perform simply order by because either by ascending or descending , we can not get our required output.

So let’s use the below query :

“select distinct Continent from country

order by field (Continent,’Asia’,


‘North America’,

‘South America’,




And we will get the output as required:



Rupam Bhardwaj



Creating HTML Table with JSON Data dynamically in HDI(Helical Dashboard Insights)

This blog will teach you how to create HTML Table with JSON Data dynamically in HDI(HelicalDashboard Insights) :

To create html table with json data dynamically in hdi , we need two files

  1. Demo.EFW
  2. template.html

In “Demo.EFW” file , we have to call template.html file


<?xml version=”1.0″ encoding=”UTF-8″ ?>


<title>HDI Demo</title>


<description>Demo Dashboard</description>






In “template.html” file ,

  1. we have to declare in which portion of the dashboard we want table to be shown
  2. JSON data
  3. Script that will automatically add the json data to table


Template.html : –

<div id = “myTable”></div>

<style type=”text/css”>

td, th {

padding: 1px 4px;




var data = [{“id”:”1″,”Name”:”Rupam”,”address”:”Hyderabad”}];

var peopleTable = tabulate(data, [“id”, “Name”,”address”]);

function tabulate(data, columns) {

var table =“#myTable”).append(“table”)

.attr(“style”, “margin-left: 250px”),

thead = table.append(“thead”),

tbody = table.append(“tbody”);


// append the header row






.text(function(column) { return column; });


// create a row for each object in the data

var rows = tbody.selectAll(“tr”)





// create a cell in each row for each column

var cells = rows.selectAll(“td”)

.data(function(row) {

return {

return {column: column, value: row[column]};





.attr(“style”, “font-family: Courier”)

.html(function(d) { return d.value; });

return table;




Rupam Bhardwaj

Helical IT Solutions

Learn Scheduling using Quartz

Why do we need scheduling?
Scheduling is needed if you want to automate the repetition of a task at specific intervals or        particular date. You could of course manually watch the time and execute your task, albeit an inefficient task. If anyone wants to do particular task after every 3 seconds, then who wants to watch the computer every 3 seconds just to hit the Enter key? No one.

History of the Quartz Framework

Quartz was created by James House, who envisioned the first conceptual pieces of the framework in 1998. These included the concept of a queue of jobs and a pool of threads to process the jobs, although probably in an unrecognizable form by most of today’s Quartz users.



Before you can use the scheduler, it needs to be instantiated. To do this, you use a SchedulerFactory. Some users of Quartz may keep an instance of a factory in a JNDI store, others may find it just as easy (or easier) to instantiate and use a factory instance directly (such as in the example below).

Once a scheduler is instantiated, it can be started, placed in stand-by mode, and shutdown. Note that once a scheduler is shutdown, it cannot be restarted without being re-instantiated. Triggers do not fire (jobs do not execute) until the scheduler has been started, nor while it is in the paused state.

Here’s a quick snippet of code, that instantiates and starts a scheduler, and schedules a job for execution:

SchedulerFactory schedFact = new org.quartz.impl.StdSchedulerFactory();


Scheduler sched = schedFact.getScheduler();







// define the job and tie it to our HelloJob class

JobDetail job = newJob(HelloJob.class)

.withIdentity(“myJob”, “group1”)





// Trigger the job to run now, and then every 40 seconds

Trigger trigger = newTrigger()

.withIdentity(“myTrigger”, “group1”)







// Tell quartz to schedule the job using our trigger

sched.scheduleJob(job, trigger);



By Prashansa Kumari

Helical IT Solutions

Steps to migrate oracle with pentaho

Step 1:-

Run script as DB admin.

Script is available in location:- biserver-ce\data\oracle10g.

Modify configuration file:-




original code:-






Modified code:-






  1. hibernate-settings.xml

Location:- pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code


Modified code:-




pentaho-solutions\system\hibernate\ oracle10g.hibernate.cfg.xml

Do not need to change any code in this file.. Just need to check everything is perfect or not


   <!– Oracle 10g Configuration –>

<property name=”connection.driver_class”>oracle.jdbc.driver.OracleDriver</property>

<property name=”connection.url”>jdbc:oracle:thin:@localhost:1521/sysdba


<property name=”dialect”>org.hibernate.dialect.Oracle10gDialect</property>

<property name=”connection.username”>hibuser</property>

<property name=”connection.password”>password</property>

<property name=”connection.pool_size”>10</property>

<property name=”show_sql”>false</property>

<property name=”hibernate.jdbc.use_streams_for_binary”>true</property>

<!– replaces DefinitionVersionManager –>

<property name=””>update</property>

<!– load resource from classpath –>

<mapping resource=”hibernate/oracle10g.hbm.xml” />





Original Code

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

Modified Code:-

org.quartz.jobStore.driverDelegateClass =




Original Code

<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”hibuser” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/hibernate

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES” />


<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”pentaho_user” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/quartz

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES”/>


Modified Code:-

<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”hibuser” maxWait=”10000″ maxIdle=”5″ maxActive=”20″


type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Hibernate”/>


<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”quartz” maxWait=”10000″ maxIdle=”5″ maxActive=”20″


type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Quartz”/>

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

     <param name=”path” value=”${rep.home}/repository”/>


Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

   <param name=”url” value=”jdbc:oracle:thin:@localhost:1521“/>

   <param name=”user” value=”jcr_user”/>

   <param name=”password” value=”password”/>

   <param name=”schemaObjectPrefix” value=”fs_repos_”/>

   <param name=”tablespace” value=”pentaho_tablespace”/>


ii)                  DataStore part

Comment this code

<DataStore class=””/>

Active this code:-

<DataStore class=””>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”databaseType” value=”oracle”/>

<param name=”minRecordLength” value=”1024″/>

<param name=”maxConnections” value=”3″/>

<param name=”copyWhenReading” value=”true”/>

<param name=”tablePrefix” value=””/>

<param name=”schemaObjectPrefix” value=”ds_repos_”/>


iii)                Security part in the FileSystem Workspace part

Comment this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${wsp.home}”/>


Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

<param name=”url” value=”jdbc:oracle:[email protected]:1521/sysdba”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schemaObjectPrefix” value=”fs_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>


iv)       PersistenceManager part

Comment this code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager”>

<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${}_”/>


Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”${}_pm_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>


v)       FileSystem Versioning part

Comment This Code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${rep.home}/version” />


Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”pm_ver_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>




Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)




<param-value>[email protected]/../data/hsqldb/sampledata,[email protected]/../data/hsqldb/hibernate,[email protected]/../data/hsqldb/quartz</param-value>




Also comment this code






You have done with integrating pentaho 5.0.1 CE with Oracle

Now login to the Pentaho server .

URL:  http://localhost:8080/pentaho

Username/Password : Admin/password

Using MDX Query in Pentaho CDE

This blog will talk about Using MDX Query in Pentaho CDE

In Layout Section:

Design the layout of your Dashboard, from scratch or from a template – while defining the layout you can apply styles and add HTML elements as text or images.

Pentaho CDE MDX Query

In Component Section:

In side Component layout we are using different types of chart and table or other things. According to component we will provide Data source name and Html Object and some other properties you can also use if you needed.

Pentaho CDE MDX Query

In Data source Panel:

In side data source panel we are we are using driver like jdbc or jndl according to requirement. In My example I am using Jndl data source and Mondrian Schema like Sample data and query like:

Select NON EMPTY {[Measures].[Actual]} ON COLUMNS,

NON EMPTY Crossjoin(Hierarchize(Union(Union(Crossjoin({[Region].[All Regions]},
{[Department].[All Departments]}), Crossjoin({[Region].[All Regions]},
[Department].[All Departments].Children)), Crossjoin([Region].[All Regions].Children,
{[Department].[All Departments]}))), {[Positions].[All Positions]}) ON ROWS

from [Quadrant Analysis]

Note: Below Image you can see the Process.

Pentaho CDE MDX Query

Preview Panel:

After Applying all those thing what I mentioned in above information the output will show like these. This output is same as OLAP creation output in MDX query. You can compare both output.

Pentaho CDE MDX Query

Pentaho CDE MDX Query

Note: When you are start working in MDX Query in Pentaho CDE. You have to first know how to use OLAP Kettle -MDX OLAP. After that you can use that MDX query inside Pentaho CDE (Pentaho Community Dashboard).


Helical IT Solutions

Define JNDI for Pentaho BA server 5.0

Define JNDI for Pentaho BA server 5.0

Note: For illustration I’m showing Oracle 11g configuration. Replace the Resource name, username, password, driverClassName, and url parameters, or any relevant connection settings.

Add Driver

Place appropriate driver for Oracle 11g which is ojdbc6- to this directory for the BA Server: /pentaho/server/biserver-ee/tomcat/lib/.

Note: There should be only one driver for one database in this directory. Ensure that there are no other versions of the same vendor’s driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts.

Specify JNDI connections

  1. Stop server & BA server.
  2. Edit the /tomcat/webapps/pentaho/WEB-INF/web.xml file.
  3. At the end of the <web-app> element, in the same part of the file where you see <!– insert additional resource-refs –>, add this XML snippet.
  4. myDatasource
    jdbc/ myDatasource

  5. Save & Close web.xml
  6. Open & modify this file /tomcat/webapps/pentaho/META-INF/context.xml.
  7. Add this XML snippet anywhere in context.xml
  8. Save & Close context.xml
  9. Open simple jndi
  10. Open & Modify this file biserver-ce/pentaho-solutions/system/simple-jndi/
  11. Add these lines
  12. myDatasource /type=javax.sql.DataSource
    myDatasource /driver= oracle.jdbc.OracleDriver
    myDatasource /url= jdbc:oracle:thin:@//localhost:1521/sysdba
    myDatasource /user=dbuser
    myDatasource /password=password

  13. Save & Close
  14. Delete the pentaho.xml filed located in the /tomcat/conf/catalina/directory.
  15. Start tomcat & BA server.

KPI used by Ecommerce Companies in Business Intelligence

Keeping a tab of KPI is a very good exercise and can help entrepreneurs in understanding the current state of business and the way forward.The general KPI used by Ecommerce companies are listed below (ofcourse there could be variations and a lot will also depend on the companies business goal as well). These KPI, in different charts and tables and visualizations, can be built using any of the Business Intelligence software (BI). This blog will be talking about KPI used by Ecommerce Companies in Business Intelligence


Ecommerce KPI

Ecommerce KPI

Generic Reports are
– Average number of items per purchase
– Percentage (%) of new customers to existing customers
– Frequency of sales transactions
– Average lifetime value of customers
– Shopping cart abandonment rate
– Checkouts per cart
– Average Days to Purchase
– Order session Percentage (%)
– Average Visits to Purchase
– Coupon conversion percentage
– Cart conversion rate
– Percentage (%) of returning customers
– Percentage (%) of canceled checkouts
– Average order size

Operational reports
– Stock Week Cover: Giving information about how much units we have in stock and for how long this will last
– Rate of Stock Turns
– Stock at the COGS value
– Stock at the SRP value
– Dispatch costs per warehouses

Performance Related Reports:-
– Gross Profit
– Gross Margin Percentage (%)
– Return Rate and Return Rate Percentage (%)
– AVG Order Value
– AVG Item per order
– Traffic: number of visits or visitors, it depends what do you like to measure
– Conversion Rate = Number of Dispatched orders / Visitors
– Price Point

Helical IT Solutions

Helical Dashboard Insight 1.0

Jaspersoft is world’s most commonly used Open Source BI software and boasts of more than 4 lakh community user. But many a times we come across situations when some kind of dynamic dashboard needs to be built, some custom input controls needs to be created, some java script or AJAX etc needs to be added, some real time charting needs to be done, or when we need to customize the dashboard with new logo or color or font or size etc. But the same is not possible using Jaspersoft out of the box components. Don’t you worry, keeping all these in mind, Helical has developed its own plugin working on top of Jaspersoft which removes all these limitations, allows the developers to develop dynamic dashboard which are highly developer friendly. Known as “Helical Dashboard Insight (HDI) 1.0”, the plugin allows you to create friendly, powerful and fully featured Dashboards on top of the Jaspersoft Business Intelligence server. Former Jaspersoft Dashboards had several drawbacks from a developer’s point of view.

“Helical Dashboard Insight (HDI)” emerged as a need for a framework that overcame all those difficulties. The final result is a powerful framework.

Heading Jaspersoft Helical Plugin
Dashboard Feature Availability Jaspersoft community version is not having dashboard features Using “Helical Dashboard Insight (HDI)”, even Jaspersoft community version can also have dashboard features.
Dashboard Feature Availability Jaspersoft Enterprise version is having dashboard feature but still dashboard designer is still having lot of limitations as discussed in details below. Using “Helical Dashboard Insight (HDI)” all the limitations can be removed. (point by point details listed below)
Adding new HTML There is no places in Jaspersoft where we can add our own HTML scripts. Thus we don’t have a lot of control on the dashboard and its difficult to control the UI of the same. Using the plugin, HTML code can be added. Hence the entire look and feel of the dashboard, layout changes can be made, color, font, size, images etc can be added/edited/changed at will. Hence the entire look and feel of the dashboard can be controlled using “Helical Dashboard Insight (HDI)”
Using Bootstrap Themes Not possible to use Bootstrap theme within community or enterprise edition of Jaspersoft. Bootstrap themes can be used to create dashboard and infoGraphics.
New Input Control Let’s say we want to create new input control into the dashboard like slider, multi-select dates, last 7/15/30 days selection, YTD etc Most of the commonly used input controls are already built into the “Helical Dashboard Insight (HDI)”. If the input control is not present then the same can be added very easily by merely adding into the html document.
Real Time Charting It’s not possible to develop real time report/dashboard with Jaspersoft. Maximum using setinterval predefined time can be set and at those times the chart will get updated. Thus not only this is inefficient and inflexible, but also at the setinterval database is being queries again and again, thus leading to lower performance Very easily possible using “Helical Dashboard Insight (HDI)”. The plugin takes care of the same using websockets. The chart updation will happen on realtime as soon as the same happens in the database. Also this will result in absolutely no load on the database.
Plugin Designing Plugin designing in most of the cases not possible in Jaspersoft. Most of the things are not developer friendly in this case. There are two kind of plugin designing we can do using our plugin. One is frontend plugin which can be done using HTML. The development of backend plugin can be done using Java Scripts. Hence a developer is having the flexibility to do almost anything he wants.
Adding new chart /visualization Adding a new chart or visualization which is not out of the box is very tedious job in Jaspersoft with an effort of multiple days involved. Adding a new chart is extremely easy with just adding of the code in one of the file.
Adding new AJAX AJAX is often used to update only certain section of a website which undergoes changes rather than entire page. Again adding of AJAX in Jaspersoft dashboard is not possible, hence anything like auto-refresh in case of data change is not possible. Usage of AJAX is very much possible in our case. Thus it’s possible to create dynamic self loading dashboards/charts in case of any changes. Also we can create chart which will fetch data and load on clicking of any other button without refreshing the page. Calling of multiple dashboards on any click event is a cake walk now.
Adding Java Script Javascript which is often used to add dynamicity like hover information, making a panel appear/disappear, rollover, crossover etc. Adding java scripts is not possible in Jaspersoft. Using our plugin, it’s very easy to add any new java scripts and thus we can make the dashboard extremely dynamic.
Adding new Javascripting Charts Jaspersoft does not support adding new javascript chart. Many of the leading market available charts like D3, Protovis, Raphael etc which are java charting libraries, it’s just not possible to use those charts inside Jaspersoft. This is also very much possible using our plugin. All we need to do is add the code inside one of the files and thus any new chart can be easily added.
Adding new resource Only those resources can be used which have been provided by Jaspersoft. There is no flexibility to add our own resources. Using java scripts, we can easily create and add our own new resources. Resources could be images, css, javascript, input control, external css, etc.
Managing and maintenance of internal resource It’s also possible in Jaspersoft to some extent but with some limitations and also there is hardly any flexibility in managing those resources. With the plugin, we can map directly input controls with various panels, can hide them, enable and disable, on click of submit button triggering/rendering of selected panels and not the entire dashboard is possible
Creation of dynamic dashboard In Jaspersoft, a dashboard can only contain a report or adhoc report or a custom URL. Nothing much can be done. With ability to add new charts, add java scripts, add HTML, add ajax, use bootstrap themes, design new plugins etc, extremely intuitive and dynamic dashboards can be created using “Helical Dashboard Insight 1.0”
Implementing Cascading parameters Cascading is possible in Jaspersoft, but there is no flexibility to selectively use them for selected panels. Implementation of cascading parameters within plugin is a cakewalk.
Creating a new button Not possible inside Jaspersoft Button creation is very easily possible, and further actions can be defined what should happen with button click.
Intra Dashboard Communication (IDC) This point means clicking on one panel, the drill down or the details of chart in another panel should change. The communication should happen between panels of dashboard, thus having more flexibility in designing dashboard and more optimal usage of screenspace of dashboard. This thing is not possible inside Jaspersoft. This function can be easily achieved using our plugin “Helical Dashboard Insights (HDI)”.

Please get in touch with us at [email protected] to know about the features in detail, firstlook of the plugin, its integration with Jaspersoft, building dashboard in Jaspersoft community version, some of the feature rich dynamic dashboards demo etc.