Working with Crosstab Component In Pentaho Report Designer

This blog will teach reader about Working with Crosstab Component In Pentaho Report Designer

Crosstab is an experimental feature in Pentaho Report Designer . By default we can’t see crosstab component in PRD.

To enable this feature we need to do some modifications in Edit – > Preferences

1

In this Panel , go to “Other Settings” Tab and check the 2nd Option i.e, “Enable (unsupported) experimental features”.

Then Crosstab component will be automatically visible in PRD.

Data Source : JDBC : Sample Data Memory

Query :

2

If we will preview this query then we will get the data like below :

3

But In the above resultset it is very difficult to analyze the data .

So , its better visualization , I decided to create report with crosstab on this query .

Steps :

  1. Go to Master Report in Structure Tab
  2. On the right click of Master Report click on Add Crosstab Group

4

  1. Then you will find the below window

5

  1. Here you need to decide which field you want to see in row and which in column , accordingly set the fields you will get the output .

EXAMPLE :

6

Now Preview the report you will find something like below :

7

 

 

Rupam Bhardwaj

Helical IT Solutions

Using JavaBeans set Datasource in Jaspersoft iReport

JasperReports provides several types of pre-defined/ready-to-use datasources which can be used to provide the data that is filled in the report. We are going to look at one such datasource, the ‘JavaBeans Set Datasource’, which allows us to use JavaBeans as data to fill reports.

The basic idea is to provide 2 important things –

1. A JavaBean Class which defines the different fields of the datasource
2. A bean factory class which essentially returns a set of objects of the JavaBean class in form of an Array or Collection. Each object inside the array or the collection will be seen as one record in this type of data source.

We will now discuss each of these classes and how are they used in the datasource with an example.

1. Creating the Java Bean

The JavaBean Class is a Java class which exposes its attributes using “getter” methods. We define a “getter” method for every attribute which looks like public getXXX() , where XXX is the attribute in the class.
The example below, shows a WeatherBean Class which holds weather information for various cities. You can see a “getter” (accessor) method defined for each of the attributes as per the specified format.

/**
* Bean Class which defines that data structure and provides the getter methods 
*/
public class WeatherBean {
	private String cityName = ""; 
	private String weatherType = "";
	private int temperature = 0;

	public WeatherBean(String cityName, String weatherType, int temperature) {
	this.cityName = cityName;
	this.weatherType = weatherType;
	this.temperature = temperature;
	}

	public String getcityName() {
	return cityName;
	}

	public String getweatherType() {
	return weatherType;
	}

	public int gettemperature() {
	return temperature;
	}
}

2. Creating the bean factory class

The bean factory class should have a static method which will return a collection or an array of objects of the bean class. In the below example the method is getWeatherBeans().

import java.util.Collection;
import java.util.Vector;

/**
* Bean Factory Class which returns a collection of beans
*/
public class WeatherBeanFactory {

public static Collection getWeatherBeans(){
Vector allbeans = new Vector();
try {
allbeans.add(new WeatherBean("Delhi","Sunny",41));
allbeans.add(new WeatherBean("Mumbai","Cloudy",36));
allbeans.add(new WeatherBean("Bangalore","Rainy",28));
allbeans.add(new WeatherBean("Hyderabad","Sunny",39));
}
catch(Exception e){
e.printStackTrace();
}
return allbeans;
}
}

Export both the classes into a JAR file.

3. Add the JAR file to the iReport Classpath

jar-add-ireport

4. Create the JavaBean Set Datasource

Select the datasource type as “JavaBeans set datasource”

jar-add

Set the Factory class name and Static method call to retrieve the collection:
In our example, it is “WeatherBeanFactory” and “getWeatherBeans”

ds-1

5. Set the Report Query
Go to the “JavaBean” datasource tab. In class name, give the name of the bean class. In our example, its “WeatherBean”. Click “Read attributes” and select the fields as per your requirement.

rq-1

The mapping between a particular JavaBean property/attribute and the corresponding report field is made by naming conventions. The name of the report field must be the same as the name of the JavaBean property as specified by the JavaBeans specifications. For instance, to retrieve the value of a report field named cityName, the program will try to call through reflection a method called getcityName() on the current JavaBean object. This is how, it “getter” methods are used.
Below report is designed using the fields from the “WeatherBean” class.

rq-2

The output is shown below.

rq-3

Though we have used a simple JavaBean class, with attributes as basic types, Nested JavaBean properties can be also accessed in a JavaBean data source.

– Shraddha
Helical IT Solutions

Creating Report In iReport using Linear Gauge as component

This blog will teach reader how to create report in ireport using linear gauge as component and publishing it on the jasper server.

Purpose : to compare the avg(salary) of male and female employee in an organization

Database server : – postgesql

Database name : foodmart

Table name : employee

Below are the steps :

# 1 : Create two datasets named “MaleSlary” &  “FemaleSalary” for calculating the avg(salary) for

Male and female respectively:

Dataset 1(MaleSalary) :  select gender,avg(salary) from employee where gender like ‘M’group by

Gender

Dataset 2(FemaleSalary) : select gender,avg(salary) from employee where gender like ‘F’group by

Gender

# 1:- Drag and drop two “linear gauge “ as widget type from WidgetPro Palette chart in ireport

#2 :- Add the above datasource for widget1 as MaleSalary and widget2 as FemaleSalary

#3:- right click on the widget chart -> Edit Widget Peoperties

linear gauge jasper report

Here for each tab in the properties we can customize our widget visualization.

Example : Suppose we need to add % symbol after the Widget Pointer value,then in that case

We need to go to the Advanced Properties of Widget Configuration and add

Property Name : number suffix and Value Expression : ‘%’.

linear gauge jasper report 2

Example 2 : Suppose we need to add the Color Ranges For the Widget then in the widget properties,

Color Range Option is there, we just only have to give our condition.

 

# 4:- After Publishing the report int jasper server , the report will look like below :

linear gauge in iReport

Rupam

Helical IT Solutions

 

Creating a Federated Tables inside MySQL

What is Federated Table?

A federated database system is a type of meta-database management system (DBMS), which transparently maps multiple autonomous database systems into a single federated database.

A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

federated table

Federated Tables Concept In MYSQL(linux) :

  1. Federated Storage Engine : Using Federated Storage Engine,we will be able to access data from tables of Remote databases rather than local databases. This Federated Engine concept is available from MYSQL 5.3.0 Onwards

Example : Suppose you want to access some data from the DataBase that is on one server1,

Also another set of data from different server2, but data on these two servers are not accessible through out the world , Rather these are available only on one server3 , so what you can do is :

Simply create “Federated” tables for all those tables from which you want to access data on server3 from server1 & server2. When using a FEDERATED table, queries on the server3 are automatically executed on the remote (federated) tables. No data is stored on the local tables.

NOTE :  By default Federated Storage Engine will not enabled on the mysql

Federated Table 2But if you want to verify whether federated engine is on or off, “SHOW ENGINES” sqlquery can help to identify the status.

federated table 2If value of Support column is “YES” then Federated Engine is Turned On, Else If it is “No” it means it is Turned Off.

To enable federated engine ,

  1. You add the line ‘federated’ to the /etc/my.cnf file after  [mysqld] section
  2. restart the mysql server

Federated Table 1

  1. Federated Table Creation : It has two elements :
    1. Remote server : Data is physically stored in this server
    2. Local Server :      No data is stored here,simply you can fire the query and you will be able to see your required output.

Example :

  1. Suppose you have a table on the Remote Server (say Master Table : “student_test”)

Data Base Name is :  STUDENT_DB that you want to   access by using a Federated table

 

CREATE TABLE student_test(

sid     INT(20) NOT NULL AUTO_INCREMENT,

sname   VARCHAR(32) NOT NULL DEFAULT ,

PRIMARY KEY  (id),

)ENGINE=MyISAM

DEFAULT CHARSET=latin1;

 

2. Next,You have to create a table on the local database server(say Federated Table :”Student_federated)” DataBase Name is STUDENT_FEDERATED to access the data on the Master Table

 

CREATE TABLE Student_federated(

sid     INT(20) NOT NULL AUTO_INCREMENT,

sname   VARCHAR(32) NOT NULL DEFAULT ,

PRIMARY KEY  (id),

)ENGINE=FEDERATED

DEFAULT CHARSET=latin1;

CONNECTION=’scheme://username:[email protected]:port/DBname/TableName;

For the above scenario , The connection String should be :

CONNECTION=’mysql://root:[email protected]:3306/STUDENT_DB /Student_test;

 

Sequence of execution:

– When a client issues an SQL statement that refers to a FEDERATED table, the flow of information between the local server (where the SQL statement is executed) and the remote server (where the data is physically stored) is as follows:

– The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.

– The statement is sent to the remote server using the MySQL client API.

– The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).

If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement.

 

Now You are able to get your required output by firing sql query

 

Thankyou

Helical IT Solutions

Best Practices when designing & using iReport /Jaspersoft

This blog talks about the best practices which should be followed when creating reports using iReport or Jasper studio, deploying the same on Jaspersoft server, nomenclature to be used etc.

 

1) Report Margins:

When you develop reports for dashboards, it is advisable to keep all the margins with 0 pixels.

By default margins will be
Left margin         20
Right margin       20
Top margin         20
Bottom margin    20

Change the values to 0

Left margin         0
Right margin       0
Top margin         0
Bottom margin  0

Why?
Because, when set to 0 report panels are well fit when designing the dashboards.

 

2) Bands to keep the components

Do not keep table component, cross tab component in Detail band. Keep all the components either in Title band or in Summary Band as per the requirement. It is advisable to create custom bands to keep the different charts if you need to develop a report with multiple charts.

Why it is not recommended to keep the components in Detail band?

Details band falls into loop till the end of the row/data for fields hence if you keep any other component it will fall in a loop and will give you unexpected behaviour of iReport with bad output.

3) Parameter Naming conventions

It is advisable to give good naming conventions for parameters. For example parameter name could be param_paramName or p_paramName

Eg : 1)  p_startDate 2) p_endDate

Other Naming conventions
The same thing you can apply when you create input controls /Data source Names/Custom band names/Data Set names in iReport & Jasper Repository respectively.

Why ?

Easy to differentiate the variables, parameters and group names etc

4) Remove the other bands which you are not going to use in iReports

5) Variables and Parameter usage in iReport

Make use of internal parameters for the report and for the summation of columns recommended to use the variables.

6) Jasper Project Folder Structure

Project Name

archive (take a back up of jrxmls if you are going to update/modify them in this folder with a version number)

      Resources
     Input Controls ( All your parameter names for the project/various reports)
      Data sources(This folder is useful when you have multiple databases to use in your project)
      Files(Keep all your data source files here, for eg : Excel, CSV, XML and etc)
      JRXML’s (Whatever the JRXMLS you are creating you can keep all of them in this folder)
      Sub Reports(keep all you sub reports in this folder and refer from here where ever you want)
      Images(Keep all your images in this folder- for easy understanding)
      Reports(Keep all your reports in this folder)
      Dashboards(Save all your dashboards here)
      Temp (for temporary files)
      Test (Do experiment at the time of development of report in this folder)

Note that if your project is having lot many reports according to some sections/departments, it is advisable to sub divide the Reports folder with other folders.
For example:
Reports
    A.Department-1
         1.Report Name
         2.Report Name
    B.Department-2
        1.Report Name
         2.Report Name

 NOTE:

When you upload a report to JRXML it is recommended to write the description of the report. By seeing it every one can easily understand the purpose of the report/visualization.

7) Export / Import Utility

Command line utility to import/export/update folders/reports from the jasper server is given below.

Importing
js-import –input-zip(space) <Filename>
Ex: js-import –input-zip(space)”E:Work Space\Unified\Unified Reports\<file name>”

Updating
js-import –input-zip(space)”E:Work Space\Unified\Unified Reports\<file name>” –update

Exporting
js-export <location of the folder in jasper server> –output-zip <location of exporting folder>/<exporting_filename.zip>

8) Bands

Title band:

·         Every report must have some name, give the name of the report in this band.

·         Blue colour back ground with white colour font is preferable to give the titles.

·         Logos of the company are recommended to be placed left side of the band in title band under the title of the report.

Page Header:

·         Page header consists of the page numbers and date type of information. It is recommended to give page header information for long reports with heavy text involved in the reports.

Column Header:

·         This band is used for giving column headers for the fields. You can change the font style, size, give the borders, back ground colours and etc.

Detail band:

·         Detail band is used to display the output of the report using fields fetched by the query.

·         You need to drag and drop required fields to create the report to Detail band format them accordingly.

·         Detail band falls into for loop so we should keep only fields in this band rather than keeping any other component like table , cross tab, chart components.

Column footer:

 ·         This band is used to find the total, max, min of the columns from the details band.

·         You need to create variable for this and drag that variables under the column where you want see the sum, max or min

Page footer:

·         Page footer is used to place the page numbers, confidential type of text for the company etc.

Summary:

·         Summary of the report will be placed in the summary band.

·         Generally we keep the chart component, table component, cross tab component to summarize the report.

9)   9) Why should we keep input controls and data sources in resource folder?

  

Input controls in repository:

Create all your input controls in resource folder because every time for each report you need not to create the same input controls. You just need to link the existing input control from the repository folder.

Data sources in repository:
It is considered as a best practice to create data source connections in a folder called resources  and use this data source for the reports. It’ll reduce the report development time. You need to not create import database connections from iReport once you create this connection in the repository.

For any Jaspersoft, ireport, jasper studio, jasperserver or Open source DWBI requirement, please get in touch : [email protected], www.helicaltech.com

D3 Integration with Jaspersoft / iReport / Jasper Server

There can be different methods of integrating a D3 chart inside Jasper like using iFrame, using HTML etc. Below we will try to learn in detail how to do integration of D3 chart using HTML component integration.

 

What is D3 Charting? D3.js is a JavaScript library for manipulating documents based on data. D3 helps you bring data to life using HTML, SVG and CSS. D3’s emphasis on web standards gives you full capabilities of modern browsers without tying yourself to a proprietary framework, combining powerful visualization components and a data-driven approach to DOM manipulation. So D3 can be used in the HTML component of the Jaspersoft to create different types of visualization like charts, graphs, tables, etc.

 

Few things should be kept in mind:

  1. The HTML component of Jasper soft accepts codes without any delimiters like \n,\r, etc. So the complete HTML code should be converted accordingly. Moreover as the component definition is contained in “<html>………</html>”, all special characters like “, ‘, etc. should be replaced with \”, \’, etc.

2. The output of database query can also be passed to the script in the HTML component making the reports dynamic.

 

3. The html content expression can also access the variables and parameters defined in your report.

Suppose we have a variable name variable1 in iReport which is to be used in the html component. It can be used in the html component as shown below:

“<body><script>var =”+ $V{variable1}+”</script></body>”

 

4. Keep in mind that in the HTML content expression of HTML component you do not need to include the html tag, you only need to add the script, body, head, etc. tag which you are using.

Ex:

<!DOCTYPE html>
<html>
<body style=”background-color:yellow;”>
<h2 style=”background-color:red;”>This is a heading</h2>
</body>
</html>

 

In HTML component expression of iReport it would be written in the below format.

“<body style=\”background-color:yellow;\”><h2 style=\”background-color:red;\”>This is a heading</h2></body>”

 

5. Check whether sql jar (the database server you are using for your application) file is available with Jasper soft or not. By default it is not available. Download the jar file from internet and import the jar file in your application by going to the Tool ->Option. In the option menu, go to the classpath tab and click on add jar. Give the location of the jar file you downloaded.

 

Also make sure that the html component jar file is also present in the iReport<version>/iReport/modules/ext with name of jasperreports-htmlcomponent-<version>. If it is not present then download it and paste it in the same folder. (By default it is present. But check it.)

Jaspersoft chord D3 integration

Jaspersoft chord D3 integration 3

Jaspersoft force D3 integration 2

Jaspersoft chord D3 integration 2

Creating Candlestick Chart in iReport / Jaspersoft / Jasper Report

This blog will talk about how to create candlestick chart in Jaspersoft.

PREREQUISITE S/W:-

  • Jaspersoft(any version)
  • iReport tool(design)
  • d/b softwares (eg.MySql)
  • JAVA
  • Eclipse(if require)

 

WHAT IS CANDLE STICK CHART?

The candlestick techniques we use today originated in the style of technical charting used by the Japanese for over 100 years before the West developed the bar and point-and-figure analysis systems. In the 1700s, a Japanese man named Homma, a trader in the futures market, discovered that, although there was a link between price and the supply and demand of rice, the markets were strongly influenced by the emotions of traders.

 

HOW TO READ CANDLESTICK CHART?

In order to create a candlestick chart, you must have a data set that contains open, high, low and close values for each time period you want to display. The hollow or filled portion of the candlestick is called “the body” (also referred to as “the real body”). The long thin lines above and below the body represent the high/low range and are called “shadows” (also referred to as “wicks” and “tails”). The high is marked by the top of the upper shadow and the low by the bottom of the lower shadow.

Candlestick chart Jaspersoft iReport

 

FORMATION:

STEPS:-
1.      Create report in i-report designer,  select Blank A4 size report from ireport designer.
Ex:
File > New > Blank A4
(here eg.DemoOfCandlestickchart->NEXT->FINISH)

2.    Delete all band except summary band

3.    Goto-> Palette window tool->Select chart->Select MultiAxis chart->Select TimeSeries chart->Ok

4.    Right Click on Multiaxis chart->Select Add Exist Chart->Select Candlestick chart->Ok
(add two candlestick chart into multiaxis chart)

While writing query  keep in mind following things:
•    For input values to Candlestick chart  we require 5  values for each chart
High value

Low value
Open value
Close value
Volume value

 

Example:-

 

select

avg(0) as  avg,

MAX(0) as max,

MIN(0) as min,

STDDEV(0) as std_dev,

“dummy” as _lable,

 1-1-1111   as _date,

as  abc  from dual

 

· Add  new Dataset ->Write query->add it

· Goto -> Report Inspector->Summary band-> Select candlestick chart (first)

->Right click on it ->Select chart data->goto chart data

Note:-  Generally creating single candlestick chart  first candlestick High-close value  are same and second chart Low-close value are same

Candlestick chart Jaspersoft iReport 2

 

5. Similarly as mentioned above instruction  set data for another chart .
Eg:

Candlestick chart Jaspersoft iReport 3

INSTALLING JASPERSERVER IN AWS EC2 / LINUX

Things we need to find out before installing Jasperserver in AWS EC2 / Linux.

1)      Identifying whether your CPU is a 64bit or not. Use below command to find it

uname –m

Our CPU is a 64 bit.

 

2)      Identify whether Jasperserver default port 8080 is open or not? Below command helps you to identify it.

netstat -an | grep 8080| grep LISTEN

 

If nothing has not returned, then your port 8080 is free to use.

Installing Jasperserver

3)      Created a folder Helical and Jasperserver under home.

/home/Helical/Jasperserver.

 

4)      Downloaded Jasperserver 30-days trial from here http://www.jaspersoft.com/thanks-download-30-day

File name: Jasperreports-server-5.5-linux-x64-installer.run

 

Command used to download:

wget https://s3.amazonaws.com/jaspersoft_downloads/5.5/jasperreports-server-5.5-linux-x64-installer.run

 

If you face an Error saying “ -bash: wget: command not found.” Then it means wget package is not installed. Installed wget package using

 

yum install wget

 

Repeat wget command again.

 

5)      Downloaded “jasperreports-server-5.5-linux-x64-installer.run” under the location “/home/Helical/Jasperserver”.

 

Apply chmod 777 for the downloaded file

It was confirmed that port 8080 is not in use with any other process. Thus i am going forward with Jasperserver installation as

“./ jasperreports-server-5.5-linux-x64-installer.run”

Results of each step:

A)     Welcome to the JasperReports Server 5.5 Setup Wizard and its License Agreement. Do as it says and finals asks you to accept the license or not.

Press [Enter] to continue :

Reading the agreement

Accepting the License (y/n): Y

B)      It asks you to select the option of installation (Install All or Custom). If it is for the first time Installation select first option.

Please choose an install option below:

[1] Install All Components and Samples  (requires disk space of: 1.3 GB)

[2] Custom Install

Please choose an option [1] : 1

Jasper installation AWS 3

C)     It asks you to select a folder to install. By default it takes “/opt/jasperreports-server-5.5”

Jasper installation AWS 2

Press Enter simple Instead saying Yes(Y).

D)     It asks you to confirm the Installation by Continuing. Say (Y)

Do you want to continue? [Y/n]: Y

Jasper installation AWS

Start Your Jasperserver from the place you have installed it. Here the Jasperserver installed path is “/home/Helical/Jasperserver/y”.

Starting Jasperserver:

You can start the jasperserver in any one of the way sh ctlscript.sh start (or)  ./ ctlscript.sh start

Check the Status of the Service:

sh ctlscript.sh status

Once again the check the port number 8080 is engaged or not.

By default jasperserver uses Postgres Database thus from this installation even postgres DB will also be available on Port Number 5432 in your machine. No need to install it explicitly.

Location of Postgres: “/home/Helical/Jasperserver/y/postgresql/bin”

Now you must be able to access the Jasperserver from “ipaddress:8080/jasperserver-pro”. If not able to access it Check whether 8080 is configured to access TCP/IP incoming and outgoing in iptables.

Edit iptables as root user using vi /etc/sysconfig/iptables

 

Add a rule saying

 

-A INPUT -p tcp -m state –state NEW -m tcp –dport 8080 -j ACCEPT

 

Now you must be able to access the Jasperserver.

Differences between Reporting and Analysis – Concepts

Differences between Reporting and Analysis – Concepts

Definition

       1.The process of orga­niz­ing data into infor­ma­tional sum­maries in order to mon­i­tor how dif­fer­ent areas of a busi­ness are per­form­ing.

Definition

        1.The process of explor­ing data and reports in order to extract mean­ing­ful insights, which can be used to bet­ter under­stand and improve busi­ness performance.

       2.Report­ing trans­lates raw data into infor­ma­tion

        2.Analy­sis trans­forms data and infor­ma­tion into insights.

       3.Good report­ing should raise ques­tions about the busi­ness from its end users

        3.The goal of analy­sis is to answer ques­tions by inter­pret­ing the data at a deeper level and pro­vid­ing action­able rec­om­men­da­tions

      4.Report­ing shows you what is hap­pen­ing

      4.Analy­sis focuses on explain­ing why it is hap­pen­ing and what you can do about it.

Tasks

       5.Reporting: Build­ing, con­fig­ur­ing, con­sol­i­dat­ing, orga­niz­ing, for­mat­ting, and sum­ma­riz­ing.

        5.Analy­sis:  focuses on dif­fer­ent tasks such as ques­tion­ing, exam­in­ing, inter­pret­ing, com­par­ing, and con­firm­ing.

Outputs/Approach

       6.Report­ing fol­lows a push approach

         6.Analy­sis fol­lows a pull approach

       7.Reports are pushed to users who are then expected to extract mean­ing­ful insights and take   appro­pri­ate actions for them­selves (i.e.,     self-serve).


8.Types

Dashboard: These custom-made reports com­bine dif­fer­ent KPIs and reports to pro­vide a com­pre­hen­sive, high-level view of busi­ness per­for­mance for spe­cific audi­ences. Dash­boards may include data from var­i­ous data sources and are also usu­ally fairly static.

7.Par­tic­u­lar data is pulled by an ana­lyst in order to answer spe­cific busi­ness ques­tions.

8.Types
Ad hoc responses
Analy­sis pre­sen­ta­tions

Approach of Reporting and Analysis

image

How to resolve Jasperserver version compatability problem when migrating reports from one version to another

This blog will talk about how to make the report compatible with another version where it is being deployed.

Software used :- I-report, jasperserver (any version)

Solution:-

Step1) Open Your  report(.jrxml) in i-report

Jasper Version compatibility issue

step2)Goto Tool menu->options ->ireport menu->general->compatability

Jasper Version compatibility issue2

Step 3)select version which you want of jasperserver.

Here eg: jasperreport 4.1.3

Step4)goto (.jrxml) file of report  and make some  little change like set band height whatever you want and save it and update to server-repository navigator.

Step 5)check on your jasper server