Thursday 24 April 2014

Tool Differnces b/w Jasper and Pentaho - Next post will teach in - detail differnces b/w the tools and features.

Hi Guys,
This post will talk about quick differences b/w leading open source BI softwares Jasper and pentaho community and enterprise.
Depending on your client/customer requirement you can go either of the software's or the both.


Read this blog (will update link once I done it) to know in-depth differences b/w the jasper & pentaho tools


SNO
Feature
Jaspersoft
Pentaho

1

Availability of Open Source

Community and Enterprise

Community and Enterprise


2
Enterprise Cost






3

Reporting Tools
(These are stand alone desktop tools)

iReport(developed on Netbeans)

Or

Jasper Studio( developed on Eclipse plat form)

Pentaho Report Designer(PRD)



4

Dashboard Tools

CE- No dashboard designer available 

EE – Yes, Built-in Dashboard feature is available

CE -Yes
(Tools : CDE,CDA,CDF)

EE- Yes (Tools : CDE,CDA,CDF) and/or PDD(Pentaho Dashboard Designer)


5

ETL
CE – Yes 

JasperETL or Talend Open Studio for DI

EE – Yes
JasperETL or Talend EE
 CE- Yes 
(Tool Name : Kettle)

EE- Yes (Tool Name : Kettle)

6

Analysis Developing Tool( or OLAP or Cubes or Mondian)
Stand Alone desktop tool


CE- Yes 
(Tool : Schema Work bench)

EE – Yes(Tool : Schema Work bench )

CE- Yes (Pentaho Schema Workbench)

EE – Yes(Pentaho Schema Workbench-)

7

Analysis Server Tools

CE – Jpivot View
EE – Jpivot View
  • Analyzer or JPviot View
  • Pivot4J
  • Saiku Analytics

8

Ad-hoc Reporting

CE – NO ad-hoc reporting is available with CE

EE – Yes
  • You need to create domains and/or topics and then on top of them you will perform ad-hoc reporting
  • Saiku Analytics 

  • Pivot4J 
You need to create OLAP cubes and publish the schema to BA server.
i.e., You will be developing multidimensional database and then performing ad-hoc reporting on it. 
9
Date Mining
Tool/language : R
Tool: WEKA

Tip - Display No Data found message in jasper iReport

Hi Guys,

Whenever there is NO data for your selected input controls to your report in jasper you can show NO data message either in the form of text or a No data image.

For this you just need to enable a property for the Report.

From the Report Inspector

* Right click on Report Name
*  Click on Properties
*  Search for "When No Data" property and from the list of drop down select "No Data Section"

* Add "No Data" band to your report.
* Take a text field and type your No data message or insert an image saying that there is no data and select other inputs.

Save the report and select input controls for which you do not have data. 

Here is a sample image :


Tuesday 22 April 2014

Drill down functionality example on HTML-5 charts in jasper iReport

Hi Guys,

Here is a work out on drill down functionality using HTML-5 charts in iReport/Jasper Studio.

Example :
iReport 5.5 jasper server 5.5 and foodmart database.
Report 1 : Pie Chart
 Report 2 : Table component 

On pie chart you'll be showing the top 10 total sales values city wise
On click on a slice(a particular city) you will be navigating to the drill down report which will display the customer details per that city.

In simple, On clicking on city slice, the report has to pass city value from main report to drill down report in next page.


Source Code of this example : Click Here : Just Import to the server and see the report preview and check out the functionality using iReport tool by opening jrxml files.


Core part of the example :
Right click on Pie chart -> Data Axes -> Advanced Properties -> Click on Add (Optional: By clicking on Hyperlink you will get 2 default parameters - Don't for this for the 1st time , once you get familiar with drill downs you can make use of it).

As shown in below figure you need to set the HyperlinkType and HyperlinkTarget with the drill down report URL using _report parameter. The other parameter is city  which will have to pass the value of city field when you click on the slice.





HyperlinkTarget

 Hyperlink Type:

Drill down report URL


Passing field value from city parameter 


Before writing this expression you need to Define City Bucket at Data Axes Categories section.
below picture gives an idea.




Out put :
See the preview, you will get the out put 1st two images in this post

Suggesting you to download and look at the JRXML's for better understanding of drill downs on HTML-5 Charts.


Reference :
http://community.jaspersoft.com/wiki/html5-charts-ireport



Thursday 17 April 2014

Install DB Visulizer in Cnet OS 32 bit


* DB Visualizer is a Database management tool.
* Using this tool you can connect to any of the database.
 For example : you can connect to mysql, postgres, oracle at a time and can work.

* Site : http://www.dbvis.com/
* Click on Linux x86 (setup installer) - Download.
* This is a script file.
* Download link :
http://www.dbvis.com/download/thankyou.jsp?filename=/product_download/dbvis-9.1.8/media/dbvis_linux_9_1_8.sh

* Go to the downloaded folder and open the terminal and run the scripting file.
* This will start the installation. 

 NOTE : requires java 1.6 or later installed in your machine.


Thank you.

Wednesday 9 April 2014

Tip - Converting String to Integer and performing conditions then converting back to Strings - Hours on X-axis scenario

Hi Guys,

This small tip might be useful for iReport developers.

Scenario :
Let us say 5,6,7,8,9,10,11,12,13,14,15,16,17 are the general hours in the string format on the category axis of a bar chart.[Category axis allows only strings in iReport]
If you want to display them as 5 A.M 6 A.M 7 A.M 8 A.M 9 A.M 10 A.M 11 A.M 12 P.M 1 P.M 2 P.M 3 P.M 4 P.M 5 P.M you need to convert the hours into Integer and have to do some comparison and then you have to convert back the integers to Strings.

Let us say your String field name is HOURS ( $F{HOURS}) , you need to write below expression for Category Expression 

Integer.parseInt($F{HOURS})>12 ? ((Integer.parseInt($F{HOURS})-12).toString()+" "+"P.M") : ((Integer.parseInt($F{HOURS})==12) ? ($F{HOURS}+" "+"P.M"):($F{HOURS}+" "+"A.M"))

Integer.parseInt - It will convert the strings to integer - It's not directly available in iReport methods hence you need to call it with it's class Integer.
.toString()  - This function will convert the Integer value to String - By default this method is available

Logic :
if hours>12 then (hours-12 )+P.M else if hours==12 then hours+P.M else hours+A.M


Sample output :


Note : I have no 12,13 from my query result in the above image. It's just a sample output.

:D :-) :P



Friday 4 April 2014

Tip - Playing with variable in report bands - Summation of a query column using variable in jasper iReport in Title, Column Header and other bands

Hi Guys,

This is very useful tip in jasper iReport to calculate the summation of query columns using variables. 

* This way of summation reduces the SQL/other language code to calculate the column summation with in it.  i.e., 
* Faster and simpler and easy understandable. 

* To sum the the SQL query column value, you just need to drag and drop the column field from Fields section of Palette. 

* When you drag and drop, the iReport engine in the back end creates variable with the properties.
  Find images 1 and 2 for the above point. 

* This way of drag and drop of column summations will work in Title band, Column Header,Footer, Page Header and Footer band. 

* The other way is you can directly create your own variable by specifying the properties which will not work in the Title, Column/Page Header/Footer bands but works  only in Summary band with Reset type change. 

You can find a different value in column footer & summary for own created variable. 

* So it is recommend to drag and drop when you need column summations in the bands.  









NOTE: 
* Even though if you give the same properties for your own variable it'll give Null value except in Summary band with Reset type change. 

* Also note that , the variable in Detail band will not work. Detail band will fall into loop(repetition) and this is the case you are finding summation value.(A single value). 

* Not checked with other Calculations while dragging and dropping but hope will work in the same way.

:-) :-) :-)

Sadakar 
Specialist in BI


Tuesday 1 April 2014

SquirreL SQL Client Installation in Cent OS 6.5 and connecting to Postgresql foodmart database


SquirreL SQL Client Installation in Cent OS 6.5 and connect to Postgresql foodmart database

When you install jasper server 5.5 pro in Cent OS, it'll install postgresql server as back end database .
In windows , you can connect to postgres using it's client tool PgAdmin-III but it doesn't come in linux machines.

I was in a need of connecting postgresql for demonstrations using foodmart database and found SquirreL SQL client tool to connect.

Installation of SquirreL SQL client in Cent OS 6.5

Note that all the latest SquirreL SQL client software requires java 1.7 or later installed in your local machine(either window or linux).

Steps:(This procedure is for 3.2.1 Client Tool with 1.6 java installed in windows/linux)
1) Download SquirreL SQL from below link


2) Click on the dowloaded jar file and proceed

3) By default it'll take /usr/local/squirrel-sql-3.2.1 location.

4) start Squirrel SQL client
Go to the installed location and double click on squirrel-sql.sh

5) Connecting to postgrsql of jasper from Squirrel SQL client.

a) Download jdbc driver from http://jdbc.postgresql.org/download.html
b) Click on Drivers tab in Squirrel Client as shown in image
c) Click on Extract Class Path
d) Click on Add and locate the downloaded jdbc jar file

 Once you click ok, you can find right mark which symbolized that the driver installed successfully in client tool.  

Image-1


 Image-2

 Image-3