Split Excel output of Jasper Report into multiple sheets

Hi Jasperians …!!!!

A friend posted an interesting query on LinkedIn asking how to split Excel output into multiple sheets… !!! Just worked out and sharing it with you guys..!!!

It’s a sample one.. No parameters, No headers are used…

Greetings..!!! Here are the steps to follow…!!!

Aim: To split the output into multiple sheets in Excel exporting
Solution Way: Printing data in one sheet & a bar chart in another sheet.

I have used the follwoing.
i) iReport Designer : 5.0.4 (Community)
ii) PostgreSQL : 9.2 (foodmart database)

Query I have used:
select  * from employee limit 15

Step 1: 
Open iReport desinger give it to a name and save it to your fav location.

Step 2:
Write the query in query designer area

Step 3:
Remove all the bands from report design area except
Column header, Detail & summury band.

Step 4:
Drag and drop your fav columns to detail band as shown in below figure
Drag and drop a bar chart to the summary band as shown in below figure

Step 5 :
We need to set properties at two places.

i) In the report properties
ii) In settings

i) In the report properties
Report Inspector->ReportName(report1)->Right Click->Properties
Now add the propertey.
net.sf.jasperreports.export.xls.one.page.per.sheet
and value is set to be “true” do not use double quotes for giving value but in xml code it automatically goes into double quotes.
Find the below image.

ii) In the report Settings
Tools->Options-> Click on export Settings -> In the list of export options click on Excel
It opens Excel Export Parameters
In common tab —> Check One page per sheet.
Find the image below

NOTE:
Do not forget to place page break on the place from where you want to split the page.

In this example I used page break above the chart so that data comes in one sheet and chart comes in another sheet.


That’s it… Save the report and see the preview.
I’m not seeing the preview in Server but previwing internally in the iReport.

OUTPUT
The output would be something like as follows. Find the images of excel sheet.

OUTPUT in the first sheet:


OUTPUT in the second sheet:




Thanks for posting a good idea to work out on LinkedIn.

Thanks for reading this document.

Queries are welcome @    sadakar@helicaltech.com

Simlar posts on various sites:

1) http://stackoverflow.com/questions/3977658/how-do-you-export-a-jasperreport-to-an-excel-file-with-multiple-worksheets

2) http://community.jaspersoft.com/questions/531936/multiple-sheets-excel

3) http://jasperreports.sourceforge.net/config.reference.html

4) http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.one.page.per.sheet

5)http://community.jaspersoft.com/questions/533232/how-create-multi-worksheet-excel-jasper-repor

6) http://community.jaspersoft.com/questions/540310/unable-split-excel-output-multiple-sheets

7) http://stackoverflow.com/questions/8753401/exporting-a-report-with-multiple-sheets-from-jasperserver-to-excel

SADAKAR.P

Software Engineer in BI – Jaspersoft, Pentaho & Talend.

Helical IT Solutions Pvt. Ltd.

Hyderabad, INDIA.

Dynamically hiding the columns in table component in iReport

Hi folks.. here is the next stuff I worked out today.. i.e, on dynamically hiding the columns in the table component.
Introduction:
  • For some reports we need to display data in the format of table.. To do this in iReport we use Table Component from the palette section.
  • Sometimes to take better decision  we may not want all the fields/columns to display in the tabular data display.
  • Here are the steps to design our report with hiding columns
We work out with parameters to achieve this functionality.
We need ‘n’ number of parameters as per our requirement.
This tutorial is taking the following prerequisites.
iReport 5.1.0, Jasper Server 5.0, PostgreSQL 9.2, foodmart database.
Step 1: Creating New report
File–>New–>Name your report and save in your favorite location.
Step 2:  Creating dataset.
* From the Report Inspector , right click on report name.
* Then click on Add dataset–>Give data set name and then click on finish.
                                                –> give the connection
                                                 –> write your query(first you can write a
                                                                                            simple query, then your original one)
In our example :
       Data set name  : tableDataset
       Connection       : foodmart  (It is data source)
       Query                : SELECT distinct employee_id,full_name,first_name,position_id FROM
                                                                                                                                    employee
Step 3: Creating table using Table Component from Palette.
Remove all the unwanted bands from the design area.
* We have taken only Title band and deleted the rest.
* Drag and drop table component from the palette section and fill the columns as usually.
NOTE : Upto step 3 we do the report design normally
Step 4 :  Creating parameters
We create as many parameters required.. i.e, we have to use the number of parameters equal to the number of hiding columns.
* In our example I’m displaying 4 columns(Check step 3 Query).. and I want to hide 1 column or 2 columns or 3 columns or 4 columns…( It’s our choice to hide the columns from 1st column to last column or any random columns selection.).
* Just observe the below image
and see the below image to find the properties of parameters
(I’m showing only one parameter properties in the image , for the remaining 3 parameters we have to give like this one)
There are 4 parameters in our example for four fields in the tabular component.
1st parameter details :
Name : showEmployee_Id
Parameter Class : java.lang.Boolean
Use as a prompt : check it
Default Value Expression: new Boolean(true)
 
2nd, 3rd and 4th parameters details falls in the same way as above parameter.
Step 5: Using the created parameters 
 
We have placed the table component in in Title band ( It is not mandatory.. we can keep in  summary band also )
* We use the created parameters for the columns available with the table component.
* find the image below.
We have to write printWhenExpression for the columns as shown in the image.
* i.e, for all the columns .. we have to give conditions with suitable parametername.
* In our example I have given for all the four columns in Column Header Section like below
   Column 1 : Highlight this one and go the corresponding property.
   In the properties window give the Column Print When =new Boolean($P{showEmployee_Id})
* In this way we have to write conditions for the remaining columns with suitable parameters like above.
** By default the columns in the Detail section takes the same conditions that we have written for the columns in the ColumnHeader section.
That’s it we are done with the reporting design.
NOTE: 
Our report should consists of some main query.. otherwise we can not see the output. It’ll just show our report is blank and the document is empty.
Preview:
Preview with in the iReport
* We need to give either true or false in the text box as we are taking in the parameters as Boolean.
* Give the same for all the parameters.
* In our example I have given like this for this particular preview.
showEmployee_id : false
showFull_Name : true
showLast_Name : false
showProduct_Id : true
* So we are able to see only the true valued parameterised columns in the output.
* i.e, it looks like in the below image.
* see one more output where I’ve given fist one as true and the rest are as false.
* In this way we have made our report dynamic by hiding columns  of table component.
Preview in the server:
Upload the report to the server in a regular way.
* And create the input controls for the parameters.. parameters in iReport=inputControls in
repository of inputs
* parameter name in the iReport should be same as ID of the inputcontrol
 
* Crate all the 4 input controls as shown in the images.
* Remembers the Type for the input controls is Boolean
Now go to the location of the the report in the server and see the output.
* The out will looks like in the below image with the input controls specified.
OUTPUT 1: images on the selection of input controls
 
OUTPUT 2: 
If we select ‘n’ number of parameters(input controls) we can see that many columns output in the table component.
Queries/Suggestions/Improving the concept in some other way is/are welcome @ nikhilesh@helicaltech.com
Thanks for reading this article

Domain based report from iReport with filters

Here are the series of steps to create a domain report from jasper iReport.
In this tutorial I’m using
iReport-professional5.1.0
Jasper-server 5.0
postgreSQL(foodmart database).

Few points to note down about domains in jasper server and domainReport in iReport.

 

  •  Community Edition of Jasper Server and iReport do not provide the feature of domains and domain based reports.
  • Professional and Enterprise edition the Domains act as data sources.
  • They are a meta layer between the reports and the underlying data base.
  • The idea with Domains is they allow for easy joining of the data base tables and organize the resulting columns into a nice business friendly presentation.
  • Domains also allow for calculated fields.
  • The calculations are created using DomEL (domain expression language).
  • This is a simple language that allows for manipulation of fields that resolves to SQL in the underlying DB.

 

*  We do not create any database connection externally for developing domain based report which is a traditional way of creating database connections using JDBC and JNDI.
* Before we start developing domain based report we should create a domain in the jasper server.
* And we should connect to JasperServer from iReport.
* To know the procedure of creating domain in the jasper server plz  read the below article. http://jasper-bi-suite.blogspot.in/2013/05/creating-domain-in-jasperserver.html
* In this tutorial I have created a domain in jasperserver and named it as foodmart_domain.
* My domain consists of two tables. They are i) employee ii) store
* Fields I used for employee table are :
        first_name, last_name,gender,marital_status,hire_date,postion_title

 

* Fields I used for store table are : store_number,store_city,store_country
Do this first
Go to window->Click on Domains –> we can observe the window appearing just below to the Report Inspector window which is known as Domain Window.
Step 1: 
File->New->Click on Domain Report(appears at left side)->Click on BlankA4->Click on Finish.
Step 2:
We will navigate to the following window.
* Give all the details
  Connection : This is nothing but connection from iReport to JasperServer
                          ( We connect from Repository panel of iReport)
  Domain       : Select a domain which you have already created in jasper server.
                          ( In this tutorial I have already created a domain and named it as   foodmart_domain, and I’ve not shown the procedure of creating domain in this tutorial b’z we are    learning how to create a report using domain but not domain creation )
 Filters          :  Filters are nothing but input controls in the domain based report.
                          * We can select the field which we want to use as input control
                          * In this tutorial I have used two filters(input controls).
Find the images below for filters creation.
 
Available fields :  As we successfully connected to our foodmart_domain we can observe that the tables(of course fields also.. just double click on the folder of employee or store.. we can observe the fildes also ) available with the domain left side of the above image.
* Condition Editor : To use the filters concept we just have to
       –> highlight(click on the field) the field from Available fields panel
       –> then click on create condition
       –> Field name : When you highlight the field from available fields panel it will automatically appear here.
      –> Comparison :  give the condition from the drop down list.
       –> Value(s)      :   give some value ( Like we give in the parameter default expression in general reports)
       –> Check the is prompt ( This is used for popping up the filters)
    * In this way we can add as many filters(input controls=parameters) to the report before we design the report itself.
    * We can add the filters in the middle of the designing of the report also.
    * We can also change, delete, add/update the filters and corresponding conditions.
In our example I have given two filters. They are : i) gender ii) store_country
1st filter :
Field Name : gender
Comparison : Equals
Value(s) : M

 

2nd  filter :
Field Name : store_country
Comparison : Equals
Value(s) : USA
* Click on next
* Give the name for the report and location to save the report.
Step 3 :
When you click on finish you are ready to design your report….
* You can observe the tables and fields available within the domain in Domains Window
* From there you can drag and drop the fields to the details band of the design area.
* The complete design of the report you can find in the below image.
That’s it we are done with report … A domain report..
NOTE: 
When we designing domain report , the iReport engine generates domain query language on it’s own( defaultly)
* This you can observe at Report Query Area.
* Find the image below for our example
* We have not provided any SQL query to generate the report.
* We have not added any parameters in the Report Inspector of Parameters section.
* But the iReport engine has provided the domain query and parameters from jasperserver.
* When we work with domain based Reports from iReport these all things done by iReport Engine.
How to see the preview of the report in Report Designer ?
We can directly preview the output in the designer by clicking on preview tab
(OR)
You need to export this report to jasperserver and have to see the preview as follows.
* Right click on the Folder-> click on Run JasperServer Report
* You can see the output(preview) in new a new window(Report Viewer Window)
How to see the preview of the report in the JasperServer ?
upload the report to the server and see the preview.
The filter which we gave at the time of report design become input controls in the server and looks like in the following image.
Now click on Ok and see the preview.
We are done with simple domain based report from iReport.
Welcome with any queries at sadakar@helicaltech.com or in this blog.
Thanks for reading this article 🙂

Helical IT Solutions Raises Funding

Hyderabad, May 6, 2013: Of the many IT start-up success stories there is a new one on the block to watch out for. Helical IT Solutions, a Hyderabad based technology company providing services in open source data warehousing and business intelligence services has bagged a seed funding of INR 2.5 million from Singapore based Citibank official within 6 months of starting operations.

The company which started in November 2012 from Hyderabad has catered to technology needs of clients in various domains including e-commerce, insurance, energy, social media analytics, healthcare and human resource while building and managing their Business Intelligence platforms.

The eight member strong team is quite excited with the fund inflow and plans to use it primarily for recruitment and partnering with leading BI vendors in the open source space such as Jaspersoft and Pentaho. They want to focus and build up their capability of providing sector specific BI offering with this fund.

Specializing in Open source technologies, Helical has proved to be a great bet for its clients cutting down on their costs and adding to their efficiency and turnover. Their services include Business Intelligence, ETL, Data warehousing and data modelling, data visualization to name a few.

About Helical IT Solutions :
Helical IT Solutions is an open source Data warehousing and Business Intelligence company based out of Hyderabad. The company works primarily on Jaspersoft and Pentaho BI suite and on talend, on Talend and Kettle. Other services provided are data visualization, data modelling, data warehousing etc.

Contact Person :
Nikhilesh Tiwari
Cell : +91-7893947676
Email : nikhilesh@helicaltech.com

The companies funding news has been covered y many leading India based websites

http://www.watblog.com/2013/05/07/business-intelligence-startup-helical-it-raises-seed-funding/

http://www.vccircle.com/news/technology/2013/05/07/tech-startup-helical-raises-angel-funding-singapore-based-investor

http://www.iamwire.com/2013/05/data-warehousing-firm-helical-it-solutions-raises-angel-funding/

http://startupcentral.in/2013/05/helical-raises-seed-funding-from-spore-investor-22708/

http://www.dealcurry.com/2013057-Helical-IT-Solutions-Raises-Angel-Investment.htm

http://yourstory.in/2013/05/open-source-dwbi-company-helical-it-solutions-raises-angel-investment-worth-inr-2-5-million/

http://infotechlead.com/2013/05/07/helical-it-solutions-secures-inr-2-5-million-angel-funding/

http://www.nextbigwhat.com/helical-technology-solutions-funding-297/

http://www.dealcurry.com/2013057-Helical-IT-Solutions-Raises-Angel-Investment.htm

http://www.siliconindia.com/news/startups/Helical-IT-Solutions-raises-Rs25-million-In-Angel-Investment–nid-146582-cid-100.html

http://startupcentral.in/2013/05/helical-raises-seed-funding-from-spore-investor-22708/

Giving Hyperlink to text field.. Row banding implementation in Jasper iReport

Sometimes simple things might take lots of time….

Here is the tutorial …..
I’m using the following versions : iReport-Pro .5.0, Japserserver CE, Database : foodmart database in postgre SQL.
I’m using two reports
i) hyperlink
ii)hyperlink_second
I’m gonna show how we can give hyperlink in one report and how we will navigate to another report.
And How to give row banding in iReport?

In Report 1:
Step 1: Connect to database (Click on database icon and give all the details and test it )
Step 2: delete all the not needed bands(If we don’t delete it leads to page gaps in the output)
Step 3: Write a simple query in Report Query area ( Just right click on report name in the
Report Inspector then Select the query)
I’ve given like this : select 1 as One
Step 4: The bands I’m interested to take are : Column header and Detail bands respectively.
Step 5: Drag and drop the field to detail area And drag and drop a text field from palette to
column header.
The below image gives somehow idea.

Hyperlink to text field and Rowbanding for fields

Step 6: Giving Hyperlink to text field
i) Write text in double quotes like : “Hyper link”.( Text field should take double quotes whereas static text field doesn’t take double quotes and doesn’t applicable to create hyperlink)
ii) Right Click on the text field ->Click on Hyperlink-> It opens a window
see the sample in the following image

Hyperlink2

iii) Give Hyperlink target as : Blank
Hyperlink type as : ReportExecution
iv) Click on Link Parameters.. Give  Link parameter name as : _report
Parameter Class Name as : java.lang.String
Value Expression : location/Path of the second report in the server
for example the path is : “/reports/Demos_Sadakar/HyperLinkDemo/Hyperlink_Report2”
We can observe the location in the below image

Hyperlink3

NOTE : Remember that Hyperlink_Report2 in the URL is the ID of the jasperReport when we give at the time uploading report to server.. i.e, it is not the name of the Report.

 Hyperlink4

Step 7: Now save the report and upload to the server(Hope we know how to upload a report to server. Please find how to upload a report to server in the list of posts of my blog)

In Report 2:( A normal report with row banding functionality)
Step 1: Create a report and write query in query area
(Give same database details.. foodmart details)
I have taken this query : select * from employee
Step 2: Just drag and drop fields from Fields node of Report Insepector to Detail band.
( dragging only few fields)
Step 3: Now we are working on row banding.
i) Go to Report Inspector panel
ii) Right click on  Style option and Add some style.. I’ve given default name as style1
iii)Right click on style1 and select Add Conditional Style
iv) Now double click on it and i’ll open Expression Editor.. there we need to give the follwoing condition
new Boolean($V{REPORT_COUNT}.intValue() % 2 == 0)
In the above expression $V{REPORT_COUNT} is the default variable provided by jasperians in iReport. Check variables in the Report Insepctor.

Hyperlink5

v) Go to the properties of Conditional Style expression it opens it’s poperties( not click on Style1.. click on the conditional thing). In the properties window select give your favourite BackColor.
I’m showing the scenario in an image.

Hyperlink6

NOTE: do not check Opaque check box. just leave it.

Step 4:  Now select the fields you want to apply for the fields. I’ve selected all the fields that I’ve dragged to the Detail band. You can observe the multiple Objects properties window on the right side.
From the properties go to style and give the style1( just click on it it’ll show all the created styles.. In our example we have created only one style i.e, style1)

 Hyperlink8

That’s it. Now save the report and see the output of the report in the server . It’d look like the following

Hyperlink9

Execution of the Reports using hyperlink- Final Output.
NOTE : When we create an hyperlink and if it works fine then the color of the text changed to light blue and it’ll have underscore on mouseover.

First Report Output:

Hyperlink10

 Now click on “Hyper Link” that we are seeing on..

It’ll redirect to another page which we call it as hyperlink functionality in iReport.

Hyperlink9

🙂 🙂
Thanks for your time to read this tutorial 🙂 🙂 🙂
for any queries :  nikhilesh@helicaltech.com

How is BI driving Data Decisions

In today’s world, companies use various software like ERP, HRM, CRM, Tally, Salesforce, Web services, etc. There are different databases like MySQL, Oracle, DB2, Ingres, Postgres, MongoDB and the list goes on. Data generation is rapidly growing with collation of personal data, financial data, sales data, business data, accounting data, so on and so forth. Though, there are different software and databases, but the missing element at this point is an insight on the information.

There is a need for centralized location, where one can login and view current news and updates. This location will be like a storehouse of information, collated from various location and data. For a profitable business establishment, numerous parameters and accurate information are required by the managers to achieve the right decision for the growth of an organization. At this point, business intelligence plays a pivotal role in providing accuracy in information and views to profitable paths.

Business Intelligence (BI) refers to technologies, applications, and practices for collection, integration, analysis, and presentation of business information. The purpose of BI is to support and improve business decision-making. A proper BI system helps with right information, at the right time, in the right format.

A single BI could help all the departments like:
1. Marketing Department – Helps in growing its top-line with features like analyzing campaign returns, promotional yields, and provide solutions to expenditure for profitable ROI, and tracking social media marketing
2. Sales – Finding the best path and practices, customer acquisition cost, and improvement in yearly turnover and sales
3. Inventory – Monitoring and adjusting inventory levels
4. Human Resource – Tracking and managing employee turnover, attrition rate, recruitment process, etc.

Correct usage of BI has shown excellent results in all the sectors, be it healthcare, e-commerce, NGO, government, media domain, etc.
– On an average, ROI is $10.66 for every $1 spent on business analytics
– Moneyball movie guided by BI was nominated for the best picture
– FEBREZE marketing campaign was aided by BI, sales now hit over $1 bn annually
– Correct usage of BI can help in reducing OPEX by up to 50%
– FT used high-end BI functionality to see a growth of 20%
– The US government uses BI to assign tasks to the police personnel

Main Elements of BI are
There are many parameters on which BI tool are tested before selection

– Interactivity :- The various reports and dashboards should have high level of interactivity. Let say, if a person is viewing the total sales report, the report should be interactive and navigable so that one can drill down to view specifications like product sales, time period, etc.

– Data Visualization :- It is important to have an accurate format for data visualization. For example, month on month sales would be represented in the form of line graph, component wise contribution would be displayed in the form of pie diagram, etc. In case, the data is not represented in accurate format, drawing conclusion on the basis of unorganized format will lead to open ending decisions. Any BI tool should have really good data charting engines integrated with it.

– Connection to databases :- As explained earlier, BI tool has the ability to fetch information by connecting to different databases and webservices so that the right information is provided to the stakeholders. This can be implemented by the right ETL tools which are an additional part of BI tool.

– Mobile BI :- Increase of mobility among the workforce, is raising demands to create a space for information on mobile phones, tablets, and other such devices. Hence, the reports, dashboards, mashups, etc are being rendered to compatible with on the go devices.

– Ad-hoc Reporting :- Usually the requirements of a business user and an IT person differs on a large scale. This elongates the development cycle which creates a requirement for ad-hoc reporting. It helps the end users to drag, drop, and configure their own set of reports, visualization, and analysis as per their requirements from the dashboard.

– Application Integration :- BI tool is an easy integrable device, which is compatible with existing application/portal/software irrespective of its developed software of PHP, Java, Ruby, C or any other platform.

– Predictive Analytics :- By using high-end algorithms and using historical data, predictions can be made like propensity of a customer purchase and his return, machine failure, sales expected, revenue expected, region wise sales, etc. With this system adaptation, a company is set to be more proactive.

Apart from the above mentioned, there are other factors like speed, in-memory analytics, cloud BI, and security to name a few.

BI is an essential tool for companies to take concrete measures in their growth and decision-making aspects.  According to a survey, if a decision is made relying on data rather than pure intuition, the chances of succeeding are 79% higher. BI gives companies a more structured way to look at data while providing deep interpretations. It aids decision-making via real-time, interactive access to and analysis of important corporate information.

BI tools bridge the gaps between information silos of an organization. A company, which is not driven by metrics, might not be able to understand the problematic areas, whereas a company with BI has the capacity to access industry information and insight to the problem, and based on the data, a plan can be worked towards improving its business processes.

(Article taken from my original article published on Dataquest magazine – http://www.dqindia.com/dataquest/column/186915/how-bi-driving-data-decisions/page/1)

Jaspersoft V/s Pentaho – Which one to use & Is there any need to purchase Commercial Edition?

Jaspersoft V/s Pentaho – Which one to use & Is there any need to purchase Commercial Edition?

Any company (be it technology, manfucaturing, human resource, ecommerce, SME etc) always has the need for Business Intelligence to some or the other extent. If cost is one of the consideration factor, then the 2 BI tools which are at the forefront are Pentaho and Jaspersoft. But, often the same companies are caught up in an imbrogilo as to which tool to use, what are the technology/and end business user wise differences/ do i actually need to purchase commercial edition, is there any work around etc.

Differences :- In the below mentioned points, I have tried to cover functionality wise the differences

a. Reports :- Jaspersoft is known for its picture pixel perfect reporting. Jasper uses ireport for designing the reports. Hence, for having reports, Jaspersoft is the most ideal candidate. Pentaho uses Pentaho Report Designer.

b. Dashboards :- Pentaho provides much more capabililties, interactivity in terms of dashboards. Dashboards designed in Pentaho are far more superior in functionality, aesthetically as compared to Jaspersoft. Pentaho CE uses CDE/CDF, Pentaho EE uses PDD . Dashboard functionality is present only in the Enterprise edition of Jaspersoft.

c. Pentaho is having an intermediate layer known as Xactions & hence providing much more flexibility in terms of plugin designing, integration with applications, having out of box experience etc. Xactions supports scripting and scheduling of scripts execution. Jaspersoft dosent provide that much of flexibility in terms of intermediate layer, but never-the-less, Jasper server by default comes with some functionalities like scheduling, for plugin desiging they rely on ireport plugins and Jasper server plugins,

e. Mobile BI compatibility :- Jaspersoft scores higher than Pentaho here. They have BI for iOS, Android platforms. Functionality wise and user interface wise Jaspersoft mobile BI is better. They even have an opensouce Apple iphone application. Pentaho users have to often rely on third party tools to enhance mobile BI experience. But, Jasper provides native mobile API hence helping developers to create mobile applications. Pentaho mobile BI is only present in the enterprise edition.

f. Documentation : Jaspersoft invests a lot of time in documentation. Its pretty evident from that fact that they are having different case studies of the different clients on their website, explaining how Jaspersoft has helped them.

g. ETL : Jaspersoft uses Talend and Pentaho uses Pentaho Data Integrator (Kettle) as their default ETL tools. Kettle is developed by Pentaho organization itself unlike the case of Jaspersoft which uses Talend Open Studio version with some functionalities missing.

h. OLAP Analysis : Both Jaspersoft and Pentaho uses Pentaho Mondrian engine in their BI suite, and it goes unsaid which one will be stronger in this case.

i. Community support : Community support is more in Jaspersoft as compared to Pentaho. Also, the community members are more active of Jaspersoft community, hence if you are using CE, the chances of problem resolution is much more.

j. Plugin : Pentaho is having many plugin which are ready to use like CDE, CDF, Saiku etc. Pentaho is having a highly active community for plugin designing. Pentaho server by default comes with marketplace (just like appstore). Jaspersoft is a late entrant in this field and is currently having very limited plugins till now. They have also started Jasperexchange for their plugins.

k. Data Mining : Pentaho is having data mining module named Weka and Jaspesoft uses R. Again Weka is developed by Pentaho itself unlike the case with R. But toolwise, R is more advanced and more capable as compared to Weka

l. Big Data : Both Jaspersoft and Pentaho provides big data integration services.

m. Unlike Jaspesoft, Pentaho provides a much more tightly integrated BI suite since it has itself developed most of the components.

n. Jaspersoft EE has fusionchart integration. The latest version of Jaspesoft 5.0 EE is having Highcharts integration with it. Pentaho has open flashcharts integration. For geographical charting, Jaspersoft is having mapbox pro whereas Pentaho is having google maps.

o. Summarizing Jasper has a heavy focus on reporting and analysis. It has a better web 2.0 user interface than Pentaho and is generally easier to use. It benefits from better marketing, informational web sites, and documentation. Overall, it makes researching and finding what users need easier than Pentaho. Whereas Pentaho focuses on data integration, ETL, and workflow automation. It has a user interface that is more difficult for business users than is Jasper’s. The

documentation is also less robust than its competition. On the other hand, it has

strong data mining tools, ETL, flexibility via Xactions.

Which BI tools is to be used, whether enterprise edition has to be purchased or is there any other way out (like best of breed solution of different BI suites, plugin development, data visulization charting engines integration etc) – all these things can only be ascertained by a thorough discussion between the company and the solution provider. Its generally ideal to go and have a consultation with a company having exp in different BI technologies which will be able to give a comprehensive picture and can help the client towards business objective, rather than trying to push their software. Please get in touch with us for Jaspersoft, Pentaho, Data warehousing, ETL or any other DWBI related requirement – nikhilesh@helicaltech.com

Dashboards & Designing a good Dashboard

A picture is worth a thousand words & when spoken in IT paradigm, a dashboard is worth thousands of GB of data.

A typical definition of dashboard is
“An easy to read, often single page, real-time user interface, showing a graphical presentation of the current status (snapshot) and historical trends of an organization’s key performance indicators (KPIs) to enable instantaneous and informed decisions to be made at a glance.” – source Wikipedia

The concept dashboard originated from automobile dashboard, where driver can look & get all the required information like speed, distance traveled, RPM etc.
Dashboards have became a very critical & important tool for not only CXO but also mid level managers & any other employee for that matter. A normal data will not make sense to any person, & dashboard is just the perfect tool to help a company & employee to make sense out of the humungous data & to draw insights from it. This data can be from multiple softwares like ERP, HRMS, CRM, web service etc. The key benefit which dashboards gives is

–  View performance information in a graphical format that enables the ability to quickly identify performance issues and study the root causes behind an anomaly.
–  View performance information in an organized format aligned around key goals and objectives.
–  Deliver more timely information by moving away from costly manually intensive    methods of integrating and disseminating information.
–  Dashboards helps in better decision making by reducing operational inefficiencies.
–  Improved bottom line by reducing cost
–  Rapid problem detection, escalation & resolving

A dashboard is not useful until & unless it is linked to the vision & mission of the company. It should be linked to historical data & provide insight. There should be a high level of interactivity in the dashboard, there should be alerts, hover information, drill down & drill through capabilities. Also, a dashboard has to reflect the right KPIs which are relevant to the specific industry & also specific department. In general, finance sales HR & manufacturing are the departments which are found to use dashboards the most in comparison to others.

Dashboards Deliverables Expected :- Generally, below are the most commonly expected deliverable which are supposed to be fulfilled by any dashboards
– Historical information to be present as well
– Information to be upto date
– Customized view according to the department & sector
– Access levels – Security to be implemented
– Mobile capabilities – In today’s world, with more & more of the workforce being on the move, its very essential that the dashboards can be accessed by even a mobile, ipad & tablets
– Interactivity- The dashboards should have high level of interactivity like drill down, drill through, hover capabilities

 

Designing a good Dashboard :- while designing a good dashboard, the following points have to be kept in mind

– Dashboard should be simple & communicate easily with an end user
– There should be the best use of data visualization techniques so that in one look, end user should comprehend what is happening
– Utmost care should be taken in order to ensure that the most important information is made to stand out as compared to others
– Those text should be put in dark which has to be highlighted (like problematic are or exceptionally good performance)
– Icons draw more attention & hence can be used optimally
– wherever & to whatever extent possible, clicking should provide more information of that specific KPI
– There should never be overdose of information, the visualization should never ever be cluttered. Maximum 4-5 frame information.
– As much as possible, the entire information should be present on a single page without scrolling
– There should be focus on actionable data & insights. Patters should be highlighted on a dashboard
– Exceptions have to be highlighted – Via size, shape, icon, color, boldness, italic, shades
– Most important info to be present on topleft & least one at bottom right in a dashboard
– Giving the chart name can eliminate the need for axis naming & hence can save space
– For showing trends, the height should be bigger than length
– Never show more than 3 time series charts in a single frame
– Dashboard background should be always very light in color
– Negative to positive values should always be in opposite or contrasting colors

A typical components of dashboard which are generally used are bar & charts, map, diagrams, grids, gauges, scorecards, pareto chart, balance scorecards, gauges & scales etc

– For comparison, columns are the best tools to be used
– Databars are helpful to read data magnitude
– Color scales to be used in comparisons of data
– Line charts are the best when used to display trend or pattern
– Bar charts to be used for categorical comparisons. But should not be used for large data sets
– Pie charts generally take lots of space & also there is difficulty to read data in it. Hence, generally to be avoided in dashboards
– Sparkline & bulletgraphs deliver a lot of information without taking too much space
– Maps to be used for demographic information (should be interactive with hover, pointer, text addons, labels, graphs in maps etc)
– Sea should be in very light colors & country boundaries should be in dark colors
– Gauges are best for continuous processes like speed, RPM etc
– In gauges & other places as well, the colors can be used to depict whether the current value is falling in good, average or bad range

Following the above mentioned guidelines will make your dashboard more easy to read & visually appealing.

Happy dashboarding