Dates in Jasper iReport – Expression for default values

This post teach you how to play with default date parameters & calculations on dates in jasper iReport.
In this tutorial you learn how to make use of  “SimpleDateFormat” class and java.util.Calendar class.

I) Let us start with Report Start(Start Date) and Report End(Report End) parameters.

In some reports you need to give default parameters in your start date and end date.
Start Date = Previous month 1st day
End Date= Previous month last day
Qn :
 How to write default expression for this ?
$P{cal}  is a parameter used to reduce the expression complexity.
Start Date: ( java.util.Date)
$P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1)
? null : $P{cal}.getTime()
 End Date(java.util.Date)
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
? null : $P{cal}.getTime()
Sample output(i.e., default values in the date place holders would be)
Star Date :  2013-10-01
End Date : 2013-10-31

II) To get the individuals of a date using SimpleDateFormat class.

From Report Start  Date($P{ReportStart})
Integer.parseInt(new SimpleDateFormat(“yyyy”).format($P{ReportStart}))
Integer.parseInt(new SimpleDateFormat(“MM”).format($P{ReportStart}))
Integer.parseInt(new SimpleDateFormat(“dd”).format($P{ReportStart})) 
Similarly you can extract the parts of the date for ReportEnd parameter($P{ReportEnd} )

III) To get the current parts of the date from Calendar class in iReport


Adding dollar($) sign to X-axis lables(values) for bar charts in pentaho CDE

Hi Guys,
Using below java script one can easily add ‘$’ sign to the values of X-axis for any charts in pentaho CDE.

function f(v) { return “$” + sprintf(‘%d’, v/1000) + ‘k’; }

Write the java code in “orthoAxisTickFormatter” java script wizard.

Make sure to give orthoAxisTicks as “True”
Sample output:

This code works with 13.06 as well with 13.09 version of pentaho CDE.

Also note that in java script if one line is not executing then the remaining lines will not execute. i.e., for instance you have 10 lines of code and 4th line is not executed then the remaining lines will not execute. Check with an alert function.

Sadakar Pochampalli
BI developer


Bar chart with Target lines in pentaho CDE – making bars as lines using java script in pentaho CDE

Hi guys,
This post teach you how to make bars as lines in pentaho CDE.
Source for this post is :
Scenario : Some end users wants visualization of grouped bars as lines where one of the bar is as bar and remaining bars as targeted lines on the same bar.
Properties have to give:

Plot2 : True

Find the remaining properties in below image: version of CDE is : 13.06 but will work in higher versions also.


In “Extension points” for the chart component you need to give 3 properties from the first link.

They are

extensionPoints: {
plot2Dot_shape: ‘bar’,
plot2Dot_shapeSize: function() {
var diam = ||

return this.finished(diam);
plot2Dot_shapeAngle: function() {
return this.chart.isOrientationHorizontal() ? 0 : -Math.PI/2;

NOTE: Find the image for how to give the above code as properties in Extension points of chart  component.

 Sample output of the chart on dashboard after giving the above properties.

BI developer
(“Learning never exhausts the mind”)



Jaspersoft System Integration (SI) Partner Helical IT Solutions

Helical, a fast growing Business Intelligent (BI) organisation, offering Open source BI solutions across verticals has been appointed by Jaspersoft as a System Integrator Partner. As per the tie-up, Helical IT Solutions will be a System Integration Partner for Jaspersoft in the India/South Asia region.

As a part of this appointment, Helical IT Solutions will provide services for Jaspersoft’s BI suite including reporting, dashboards, ad-hoc and OLAP analysis, ETL/data integration. Helical IT Solutions’ knowledge of both Business Intelligence applications and the Jaspersoft BI platform is bound to ensure successful development and deployment of BI solutions.

Taking on this appointment, Mr Nikhilesh Tiwari, Founder, Helical IT Solutions shared “We are extremely happy and delighted with this tie-up and regard this as a great achievement for our organisation. We will definitely look forward to this collaboration with Jaspersoft to be beneficial for both the companies.


Mr Nitin Sahu, Cofounder at Helical IT Solutions added, “We have been working on Jaspersoft BI platform for a long time and we are happy to be their SI. With our technical strength and partnership with Jaspersoft, we are hopeful of surpassing our customers’ expectations


Mr. Royce Buñag, Vice President Asia Pacific at Jaspersoft said “Helical IT Solutions has impressed us with their knowledge of BI solutions.  They have already shown themselves to be a valuable partner with current and ongoing customer engagements.  We are delighted that they have also agreed to be one of the sponsors for the upcoming JasperWorld event in Bangalore, which is a great way for us to showcase the collaboration to customers. We look forward to a long and successful partnership.

Jaspersoft’s open source business intelligence solutions  is the world’s most widely used BI software, with more than 8 million total downloads worldwide and more than 10,000 commercial customers in 96 countries. The Jaspersoft provides a web-based, open and modular approach to the evolving business intelligence needs of the enterprise. It has 90,000 registered members working on more than 350 projects, which represents the world’s largest business intelligence community.

Helical IT Solutions is an open source DWBI company and has expertise in providing simple, practical & affordable solutions which are suitable for business users, right from CEO, CXO, line managers & to every end user of the enterprise. With a quick turnaround time, the company can provide mobile BI solutions, on premises or hosted SaaS solution, hence catering to every type of need. Helical offers services on entire BI stack, ranging from ETL, DW, Data mining, Analytics, BI solution. They also provide integration of disparate data sources and offers powerful interactive tools like balanced scorecards, personalized dashboards, key performance indicators, automated alerts, graphical mining, cross tab reporting and more.

The press release got published at many places which includes


Eletronics for You








Embedding BI Reports (Jasper Reports/ Jasper Server/ Pentaho Reports/ Pentaho Server)

There are many methods of embedding BI reports, this blog tries to explain the meaning of Embedded BI, different approaches which can be used, their respective advantages and disadvantages


A)     What is embedded BI?

Lets say you have developed your entire BI solution which includes reports, dashboards, mashups, analysis etc, the platform which is used can be anything like Jaspersoft or Pentaho. The process of integration of this BI solution with your existing application/software/portal is called Embedded BI.

Embedded BI provides a lot of advantages like ability to add more reports over and above the existing canned reports which your application might provide, the look and feel and customization of all the reports can be changed as per the software/user preference, better visualization, unified application for everything. Biggest advantage can be your product value will increase much more, hence resulting in much more sales and revenue.

a) Embedding using iFrame Method :-

iFrame method of embedding BI reports can be used for embedding Jasper server, Jasper reports and Pentaho server, Pentaho reports. The report pulled in this case is using iframe as a tag. Even though the report is external, but to an end customer he will not be able to recognize the same.

The advantages of using iFrame method is this method is fast and quick to implement. The disadvantages includes it will be difficult to manage user, also in this case there can be cross browser compatibility issues, security is a major issue here.


b) Embedding Using webservice:- Webservices can also be used to integrate reports, dashboard, interactive report or/and ad-hoc reports. We can use webservices to accept the information from the Jasper/Pentaho server, this Jasper server can be anywhere ( on Tomcat or any other server). Using webservices we will invoke the response from Jasper Server/Pentaho server. This response can be can be then embedded inside application/software. Jasper server/Pentaho server also supports RESTFUL service (JSON format responses). In this approach there are no or less security related issues.

The disadvantage of this approach is there are no interactive features available of the report which thus results in poor user experience. Also in order to handle and understand the responses from jasper server/Pentaho server, custom coding is required in the application. In case if we had put input controls in the report, the same functionality then needs to be implemented in the application via custom coding. Aside, having many webcalls at times can be resource intensive as well.

The advantage of this approach is webservices approach gives more seamless integration since there is no security issue and no thirdparty calls (like in the case of iframe). Also in this case there are no interbrowser compatibility issues (assuming it is handled by your developer team).

c) Embedding using Jasper/Pentaho Libraries:- In this method we create JRXML/Pentaho report (prpt) files using ireport/Pentaho report designer. Compile these reporting using Java API, and then use this compiled code to generate views using Jasper Server API calls

The advantage of this method is this gives the most seamless integration. Also since all the information is present as a part of the application/server, no third party calls are required.

Disadvantage of this approach is some interactivity is lost in this method. Also the input parameters are also required to be created in the own application via custom coding.


Please get in touch at for much more details about the respective approach, how to embed the same using these approaches, which was is the best suited to your needs and much more.

Change Data Capture (CDC) – Methods, Approaches, Alternatives

Change data capture :

It’s the process of tracking the data that has changed so that corresponding action can be taken. Its nothing but capturing the changes which are made on the data source and applying it at all the other places, and hence it reduces the efforts required at the ETL step.


Different strategies :

  • Push: the source process creates a snapshot of changes within its own process and delivers rows downstream. The downstream process uses the snapshot, creates its own subset and delivers them to the next process.
  • Pull: the target that is immediately downstream from the source, prepares a request for data from the source. The downstream target delivers the snapshot to the next target, as in the push model.


Usage of Change Data Capture:

Change data capture is often used in data warehousing since it involves extraction and transformation of data from one or more databases to data warehouse for analysis.

Prior to CDC, the change data was captured using other methodologies i.e. table difference or table comparison. Its like taking a copy of the source and comparing with target, spotting the difference. For limited number of records this approach might work, but for more number of records this methodology will be highly inefficient and result in immense overheads.


Table differencing: It involved copying entire table from source to staging. But since, the entire table was shifted, there were additional overheads and this method was not efficient. Also, in case if there are multiple changes in the data, it cant be captured using this methodology.


Change value selection: This involves capturing source database changed values based on the value of a specific colum. However for implementing this we need to fire queries to find out the changed data which is an additional overhead. Again, in this method also we will not be able to find or capture the intermediate values. Also the changes happening between firing of two queries will not be captured.


A slightly better approach than the ones mentioned above is by using database triggers. This triggers can be used after any Insert/Delete/Modify statement whenever there is any change, the changed information will be passed on to a table. The only disadvantage of this solution is it is database specific and migration of such a solution becomes complex since its db specific.


Another approach would be the usage of log readers. Many Enterprise DBMS have capability of transaction logs that can be used (can be switched on and off), the amount & details of information in log to be stored can be decided as well. By reading this we can come to know of the changes happening in the system. The problem with this is purchasing of new component for log reading, migration might be an issue, also some DBs might not have the logging features etc. Also sometimes some changes which are not committed, their logs are also created which could be as well an issue.


Capturing data with CDC:

CDC can capture data using two modes :: Synchronous and Asynchronous. In synchronous mode, any changed data in source system is informed immediately to the target using DML (data manipulation language) operations, whereas that’s not the case with Asynchronous mode. Synchronous uses triggers and it results in no latency or delay, though it has more overheads as compared to asynchronous.  
Benefits of CDC

–          CDC can improve performance and reduce redundancy by copying only the changed data.

–          Log based CDC also reduces load on operational systems since it only reads the logs rather than db

–          No latency, hence end business users can have immediate realization of the same in their BI

–          CDC can easily handle high volume transactions


We can implement CDC using Talend Integration Suite. More information about the same will be covered in another blog.


For any query, please reach out to me on

MariaDB to MariaDB Data Replication Strategies

Data Replication Strategies


This blog will try to explain about the different approaches which can be used for the data replication from Master DB (Maria DB) to the salve Maria DB.

Replication Benefits

Replication is having a number of benefits, some of them includes:

  • Scalability: By having one or more slave servers, reads can be spread over multiple servers, reducing the load on the master. The most common scenario for a high-read, low-write environment is to have one master, where all the writes occur, replicating to multiple slaves, which handle most of the reads.
  • Low hardware : Having a slave system db can provide the freedom to play with that data without affecting master database. We can have all the reporting etc done on this particular database. Hence, having a very high hardware requirement on the master database can be avoided.
  • Data analysis: Analyzing data may have too much of an impact on a master server, and this can similarly be handled on a slave server, while the master continues unaffected by the extra load.
  • Backup assistance:  Backups can more easily be run if a server is not actively changing the data. A common scenario is to replicate the data to slave, which is then disconnected from the master with the data in a stable state. Backup is then performed from this server.
  • Distribution of data: Instead of being connected to a remote master, it’s possible to replicate the data locally and work from this data instead.



Approach 1 : Maria DB to Maria DB replication using Master slave configuration.


Data Replication

Replication is a feature allowing the contents of one or more master servers to be mirrored on one or more slave servers.

We can exert control over which data to replicate. All databases, one or more databases, or tables within a database can all be selectively replicated as well.

The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data manipulation and data definition) are written into the binary log as binlog events. Slaves read the binary log from each master in order to access the data to replicate.

In this particular approach, masters and slaves do not need to be in constant communication with each other. We can take the servers offline or disconnect from the network, and when they come back, replication will continue where it left off. The slave in this case will be in access read only mode, the data copying from Master to slave will be on real time without any lag. Hence, on the BI and reporting front, we can have real time BI capabilities.



Approach 2 : Usage of Third party tools

The second approach which can be used for data replication from Master DB to Slave DB is by the usage of DB replication third party tools. One of the example which could be cited here is a tool like Tungsten. Tungsten is an open source third party data replicator tool. It not only works on MySQL but also on Maria DB. Tungsten can help solve problems like promoting masters easily from pools of slaves, replicating data between different database versions, replicating efficiently across sites, building complex topologies, and parallelizing data flow between servers. Tungsten Replicator runs equally well in cloud as well as locally hosted environments. Tungsten Replicator users range from tiny start-ups to the largest web properties on the planet.



Approach 3: using ETL. CDC implement, free version

The third approach which could be followed is to use ETL. We are having open source ETL options which includes Talend Open Studio or Kettle – Pentaho Data Integrator.

For having the master slave databases configuration implemented, we would be implementing CDC (change data capture). By CDC we can identify, capture and deliver only the changes which are made to the master database.  Frequency of the same can be set. Change Data Capture (CDC) is based on Publisher/Subscriber principle. The publisher captures the change data and makes it available to the subscribers. Implementing CDC will also help in reducing the workload on ETL since we would be sharing the information of only the changed data.

The advantages of implementing via ETL is security can be implemented like data encryption, while copying data we can have options like we can select which all tables data we want to replicate, how much amount of data we can to replicate, rules can be implemented while replicating etc.

start_date and end_date parameters – giving default values – iReport

Hi Guys,

This post let’s you know how to use date parameters in SQL query using between operator and using  multi select parameter.

Example Query:

column1, colum2
$X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.

Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)

Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False

$P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
($P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
? null : $P{cal}.getTime()

end_date Default Value Expression:

$P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
? null : $P{cal}.getTime()

In the above two start_date and end_date parameters you can find $P{cal} – which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

for all the input controls Parameters classs is : java.util.Calendar

(Learning never exhausts the mind)

Pentaho CDE Basics

Pentaho Community Dashboard(CDE) basics – Creating a Simple Dashboard

Pentaho Community Dashboard Editor (CDE)
What is CDE ?
Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?
Where you can find the sample examples after installing?
Designing a simple DashboardCTools Website – Community Tools

What is CDE ?
CDE is one of the plugins to the Pentaho BI Server, contributed and maintained by Pentaho Partner webdetails.
We create dashboards using this tool.
Community Dashboard Editor (CDE) was born to simplify the creation, edition and rendering processes of the CTools Dashboards.
CDE is a very powerful and complete tool, combining front end with data sources and custom components in a seamless way.

Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?
You can find the symbol “Pentaho Market place” on the menu bar..
Click on it you can see the Pentaho Marketplace editor as shown in below figure.
Find Community Dashboard Editor and Click on install.
As I have already installed it is showing up Up to Date.
These all plug-ins are updatable so every time it’s better you need to upgrade.
You must install the dependencies plug-ins before you start working with CDE.

The dependencies for CDE are:
CDF(Community Dashboard Framework)
CDA(Community Data Access)
You must restart your server to take the effect of installation.Where you can find the sample examples after installing?
* In the left side panel you can find plugin-samples folder.
* Expand it and then click on CDE
* In the Files you can find sample example CDE Sample Dashboard.
* Right Click on CDE_Sample Dashboard àThen click on Edit.
CDE has 3 major components
They are.
* Layout
* Componets
* Data Sources.

CDE has developed based on MVC-2 architecture of Advanced Java Technologies.
* Layout – View
* Components – Controller
* Data sources – Model.
Find the images below to understanding the basic of CDE.

* Scroll down the dashboard. Find “About” on the left side and find the version number of CDE installed.

* Close the version window and Just click on Preview. I’ll let you know how to work out with all the components in CDE with a good working example.
* Now, just click on Preview. You can find the output of the dashboard as shown in below figures.

* Scroll down.

* Read the points that were in boxes. You will get the basic idea of CDE dash boarding.Example:

Aim : To design a simple chart(on dashboard)

* Pentaho BI Server community 4.8 stable version.
* CDA,CDF & CDE installed in the server from Market place.
Database: SampleData (It comes along with pentaho installation.. for this artical I’m not connecting to any external database. In the upcoming artical you can find working with databases)

* This example was based upon CCC-Version1. The current Example which I’m going to explain     is based upon CCC2-Version2.
* There is a lots of changes made from CCC-V1 to CCC-V2.
* Make sure your environment of CDE supports CCC-V2 for the example.Step 1: Creating New CDE Dashboard
There are two ways to create a new CDE dashboard.
i) From the menu bar
ii) By clicking CDE icon as shown in figure

Save your dashboard
Click on Save -> Save your dashboard in your fav folder with your fav name.

After saving your dashboard  “Refresh your Repository” and then you can find the file as shown below figure.

Step 2:  Working with Layout, Componets and Data sources.
CDE is mainly works on scripting such as css, javascript.
Click on + sign as shown in below figure. (We are going to add Cascading Style Sheet code(css) code to our dashboard).
Resource type : css
External file

Resource file : click on   ^  that is highlighted using arrow in the below image.

·         * Give Styles2(do not give Styles2.css i.e, only give name but not extension).
·         * It automatically cmes with .css extension and with $ and flower braces.
·         * Save the css file externally to a folder as shown in below figure.
·         * To see the Styles2.css appearing in the folder your file must contain some css code.
·         * You must refresh your Repository to see the name of the file(Styles2.css) in the folder.
Giving Title to Dashboard:
·         CDE layout mainly works on Rows and Columns.
·         Click on Add row symbol as shown in below figure.

·         Give name of the Row and give back ground colour as shown in below figure.

·         And give Corners: Round( it is below the back ground , not shown in the image-it is not visible here in the image)

·         Now add Column(find ||) symbol on Layout Structure and click on it.

·         Give all the necessary properties as shown in the below figure.
·         Name: title
·         HTML : <h1> Sales OverView YDT <h1> ( as show in figure at arrow symbol click on button.. You will be prompted to a new window there you have to write this HTML Code)
·         Font Size: 10
v See the preview:
The preview consists of only the title of the dashboard with its layout.
It is further going to be modified.
·*          Add your .css code here…
Css code
Now see the preview.

By the time you get some idea on it.

Do as follows, I’m reducing the images now as you might get some idea..
Add another row -> Column->Html  as shown in figure.
For html give Name as title and write HTML code <h3>Sales Performance</h3>  in HTML editor
Add the following code to Styles.css to editor
Find the images for doing the above and see the preview again

Add another column as shown in below figure

Give the following properties:

Name: sales_chart
Span size: 16
Now working with Data Sources and Query part:

·         Click on Data Sources

·         Find SQL Queries in Left panel.
·         Click on sql over sqlJndi
·         In left panel give Properties
o   Name : get_sales_performance
o   Access Level : public (default)
o   Jndi: SampleData(We are working with pre defined database that comes along with pentaho software to develop this dashboard)
o   Query:
        ‘Profit’ as CATEGORY,
        ‘Measure’ as SERIES,
        YEAR_ID = 2004
        ‘Profit’ as CATEGORY,
        ‘Target’ as SERIES,
        YEAR_ID = 2003
        ‘Cost’ as CATEGORY,
        ‘Measure’ as SERIES,
        YEAR_ID = 2004
        ‘Cost’ as CATEGORY,
        ‘Target’ as SERIES,
        YEAR_ID = 2003
        ‘Revenue’ as CATEGORY,
        ‘Measure’ as SERIES,
        YEAR_ID = 2004
        ‘Revenue’ as CATEGORY,
        ‘Target’ as SERIES,
        YEAR_ID = 2003
Working with Component
·         Click on Components
·         On the left panel click on Charts
·         In properties give the following
o   Name: sales_chart
o   Datasource: get_sales_performance
o   Width: 610
o   Height:140
o   HtmlObject : sales_chart
o   SeriesInRows : False
·         I will show you how to work with Advanced Properties in the upcoming posts on CDE.
Now save your dashboard and Preview
The preview will look like as follows
That’s it we are done with the Dashboard.
Thank you for reading this post 🙂

Internationalization of jasper reports – Hello world example


Hi guys.. small work out but very use full..

Generally customers want to see data in their own language.

This example is developed using iReport 5.1.0 pro, Jasper server pro and foodmart db with postgresql.

Sources/References :

Example : Converting English to Spanish

1) From Jasper Community

2) From google translator


google_translator 3) Locale Codes



1) File->New ->Save report  to your fav directory.

2) Write a simple query and drag a field to title band( Not necessary to do but iReport requires some query and field otherwise it’ll show empty report).

3)Take Text field(Not static text) and write the following.


NOTE: $R is the special syntax used for internationalization of text in iReport.

4) Right click on report Name -> Select properties -> Give Resource bundle Name

Ex: localizationdemo

This is resource bundle name , do not give extension for this.



5) Publish your report to Jasper Server and in resources section add the bundle files.

You need to add two files..

One file contains general English text(USA) another consists internationalized text

In this example second file having spanish(B’z you are converting English to spanish)

 Right click on Resources -> Add -> Resource Bundle  id ,Name and browse for the files

You must add two files.

In this example:

File 1 ID & Name :  (You can give any of your name , extension is not mandatory). Write this text in file

localization.text1=hello world

File2 ID & Name : and add the file from your local machine. Write this text in file

localization.text1=hola mundo


In jasper server Change locale to es-Spanish


output Meet us @