JFree Bar Chart Customization in iReport.. Category axis labels overlapping is removed.

HI Folks,

Today we are going to learn how to customize BarChart Category axis.

Credit of developing code goes to Mr. Sharad Sinha who is my colleague. As part of sharing knowledge I’m gonna explain the steps.  I’m starting with the problem statement followed by the solution.

Prerequisites :

iReport : 5.0.4

PostgreSQL : 9.2

Jasper Server : 5.0

Problem :

* We have a bar chart having many values to display on the chart .. i.e, let us say 100+

* This is not an XY Chart … simply it is Vertical Bar Chart.

* Bars are coming up properly BUT the labels on the category axis are overlapped and coming in a line. or when the angle is set up to -70 the labels are coming as clumsy as shown in figure below.

* The values which we want to show on the category axis are : dates like 1 Jan 2013 and the next label we want to show 8 Jan 2013 and etc..

* Here is the snapshot of the problem statement.

 

Solution:

Our final out put should looks some thing like as follows.

What we have to do to get the above output?

We have to write Chart customizer class and have to call that class from iReport.

I’m going to explain step by step how to achieve this.

 

Read the points:

1.  Write chart customizer class java code in NetBeans.

2. Make a jar file.

3. Add that jar file in iReport as well in Jasper Server.

4. Call the class from the properties of Vertical Bar Chart in customizer class.

 

1.  Write chart customizer class java code in NetBeans.

package com.xyz.bar.chart.customizer;  //xyz is name of the company generally we give
import java.awt.Color;

import org.jfree.chart.axis.CategoryAxis;

import org.jfree.chart.axis.CategoryLabelPositions;

public class BarChartCustomizer

implements

net.sf.jasperreports.engine.JRChartCustomizer

{

private Number tickUnits;
public BarChartCustomizer() {

tickUnits = Integer.valueOf(0);

}
@Override

public void customize(org.jfree.chart.JFreeChart chart, net.sf.jasperreports.engine.JRChart jasperChart) {

org.jfree.chart.renderer.category.BarRenderer renderer;

org.jfree.chart.plot.CategoryPlot plot;

org.jfree.chart.axis.NumberAxis rangeAxis;

org.jfree.chart.axis.CategoryAxis axis;

renderer = (org.jfree.chart.renderer.category.BarRenderer) chart.getCategoryPlot().getRenderer();

 

plot = chart.getCategoryPlot();

rangeAxis = (org.jfree.chart.axis.NumberAxis) plot.getRangeAxis();
axis = plot.getDomainAxis();
CategoryAxis domainAxis = plot.getDomainAxis();

CategoryLabelPositions pos = domainAxis.getCategoryLabelPositions();

for(int i=0; i< plot.getCategories().size()-1; i++)

{

if(i%6==0){

String cat_Name = (String) plot.getCategories().get(i);
}

else

{

String cat_Names = (String) plot.getCategories().get(i);

domainAxis.setTickLabelPaint(cat_Names, Color.white);

}

}

// plot.getDomainAxis().setLabel(“TEST “+plot.getCategories().size()+”  “+pos.);

}

}

2. Make a jar file.

* Right click on the project and click on clean and build

* Net beans will automatically create jar file.

* Find this jar file in “dest” folder of your project.

 

3. Add that jar file in iReport as well in Jasper Server.

Location to place this jar files in Jasepr server.

C:\Program Files\jasperreports-server-5.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib

Location to call the jar files in iReport.

* On the menu bar go to

Tools –> Option –> iReport –>Class Path–> Add jar

* Select the location of jar file.

 

4. Call the class from the properties of Vertical Bar Chart in customizer class.
* High light the chart (bar chart in our case)  that you want to apply the customizer class.

* Go to properties (right side appears) —> go to customizer –> in the blank space write the classname including the package.

* For example in our case:

package com.xyz.bar.chart.customizer.BarChartCustomizer

That’s it we have done with the customization of the chart.

 

NOTE: after adding jar file to the server, you must restart your server otherwise you can’t see the effect of added jar file in our report

NOTE: Make sure to import necessary library files in Netbens while developing the plugin.

* Your imported library files (nothing but jars) should compatible with the files that your jasper server is using otherwise you will get minor/higher version related error when you run your report.

* Find the image below.

Thanks for reading document 🙂

 

Queries are welcome @ [email protected]

Helical IT Solutions (an Open source DWBI company providing services on Jaspersoft, Pentaho, Talend, Kettle, Data visualization) covered by a growing magazine.. Business for All

Helical Covered by Biz for All Magazine

Helical Covered by Biz for All Magazine

 

 

Among the many Information Technology startups, Helical IT Solutions, a Hyderabad based technology company providing services in open source data warehousing and business intelligence services, has been moving up the ladder with incredible pace and bagged a seed funding of INR 2.5 million from the Singapore based Citibank within six months of starting operations.

 

The company which started in Nov. 2012 has catered to the 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 (BI) platforms.

 

The eight member strong team in Helical 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. “We want to focus and build up our capability of providing sector specific BI offering with this fund,” one of founders Nikhilesh Tiwari said. 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 BI, ETL, Data warehousing and data modelling, data visualization to name a few. Helical IT Solutions is an open source Data warehousing and BI company based out of Hyderabad. The company works primarily on Jaspersoft and Pentaho BI suite and on talend, on Talend and Kettle. According to Gartner’s 2012 CIO survey, analytics and BI is the No. 1 technology priority for CIOs in 2012.

 

 

BI, analytics and PM software market was the second-fastest growing sector in the overall worldwide enterprise software market in 2011. “India is growing faster than any other country in BI sector in Asia Pacific with 36 per cent growth rate and the market for BI software in India is forecast to reach revenue of $81.5 million in 2012,” he said. Every 15 seconds, an open source BI s/w is downloaded and around 80 per cent of them need consultation and services as to how use them. With the angel investment, plans are underway to expand the team and hiring, develop infrastructure, SI partnership with Jaspersoft & Pentaho to get projects from even bigger clients and at a higher margin. “Building a parallel team after some time and start working towards building a product (we have energy domain, ecommerce domain primarily in mind),” he said about future plans. It is not that the company had walked a rosy path as due to lack of resources, they had to turn down many clients like Jabong.com, Mangaquest, Inspire edge IT solutions,  XiBoss.

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 @ [email protected]
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 [email protected] 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 : [email protected]

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 :  [email protected]

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 – [email protected]

Importance of Business Intelligence in Healthcare

What is Business Intelligence

To turn any business establishment profitable, the managers & the work force is highly reliant on decisions & those decisions are dependent on the kind of information available. This is where Business Intelligence comes into picture

Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information and also sometimes to the information itself. The purpose of BI is to support better business decision-making.

BI systems provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data. Software elements that make up the BI system support reporting, interactive “slice-and-dice” pivot-table analyses, visualization, and statistical data mining.

Importance of BI in healthcare Industry :-

Today, healthcare establishments are also generating a lot of data (Data in healthcare – e.g., encounters, labs, pharmacy, membership, finance, claims, billing, CRM, EMR, HMS, etc). Though, they have lot of data, but information is very less.

There are three broad categories of data that any healthcare organization is interested in : financial, operational & clinical. Healthcare organizations must apply business intelligence to organize clinical, business and operational data for decision-making purposes. This data is used to support programs like disease management, outcomes management, clinical performance and process improvement, cost and waste reduction, quality accreditation and predictive analytics.

Forward-thinking healthcare organizations realize that data—and, thus, business intelligence (BI)—is at the center of informed and precise decision-making that will improve patient and service outcomes in addition to ensuring their organizations’ future, thus, many leading hospitals have already adopted BI

  1. Harvard Medical School, St. Jude Children’s Research, CIBA, & Mutual of Omaha
  2. Blue Cross Blue Shield of Florida
  3. Columbus Children’s Hospital
  4. St. Joseph Medical Center & St. Joseph’s Hospital
  5. International Federation of Red Cross & Crescent
  6. St. Luke’s Medical Clinic, Houston Infectious
  7. Diseases Associated
  8. Denver Health and Hospital Authority (BI is especially crucial to “avoid both waste and having patients fall between the cracks)
  9. Lincolnshire Partnership Foundation Trust (plunged into BI because of a specific need to better analyze the efficiency of its service lines)
  10. Hartford Hospital
  11. Nemours
  12. Hospital for Sick Children in Toronto
  13. University Health Network

Benefits of using Business Intelligence in a healthcare establishment :-

a. Consolidation & protection of data since there would be a single point access of data Better protection of patient data is also possible by providing access to data only to those with appropriate access levels.

b. Improved Efficiency :- Since any type of information & report, whichever is required at any time. This will enable better decision making and improved efficiency

c. Increased revenue & reduce costs :- BI makes it possible possible to eliminate waste and mine data stores to examine and recoup denied claims in healthcare organizations. Costs of: healthcare professionals, lab equipment and consumables, pharmaceuticals/ medical material, treatment per diagnosis and cost per type of medical intervention (e.g. specific medical operation) can be reduced as well

d. Improved Margins :- BI (BI/balanced scorecard program) helps to improve gross margins of healthcare organizations.

e. Improved patients satisfaction by using BI and analytic tools.

f. Improved Patient Treatment & Care :- Through the use of BI, healthcare professionals have easy access to patient’s data and they can create a variety of classifications/reports based on demographic data, sex, age, and so on. Thanks to the evidence based medicine and capture of medical history of the patient doctors can accurate diagnosis and apply efficient treatment with reduction of risks during treatment (e.g. related to on time admittance, the use of medicine, biomedical equipment, blood transfusions). Timely and effective clinical decisions are better facilitated by increasing the potential of BI.

 g. Reduction of medical errors and improved patients safety. It could be reached by supporting medical research and data treatment. BI systems can support a larger Healthcare system, by the exchange of medical information on a patient. Improved decision-making in the area of comprehensive health care policy by the authorities of the organization of the health sector. It can be reached with monitoring the performance of doctors, departments and medical material requirements. Multiple groups or individuals can be put together by emphasizing the analysis and accurate data, which brings them closer to the point of service in order to enhance decision making & make data actionable.

h. Better data management helps in better patient treatment in terms of timely admittance, diagnosis and risk in casualty.

i. Some other benefits includes supply cost optimization, fraud detection

Main KPIs & Performance Indicators

The main KPIs which can be used to be displayed via reports & dashboards are mentioned below.

Clinical Data KPI

Healthcare establishment can improve clinical quality and resource utilization by effectively monitoring and measuring clinical performance and outcomes data. By delivering analysis from multiple sources at once, BI enables organizations to track large amounts of information stemming from clinical activities and identify the most efficient practices. BI helps providers identify trends and anomalies, and analyze risk in clinical care. With a lot of stakeholders involved like doctors, diagnostic centers, pharmacy etc all of them cant function as independent silos. With BI, all constituents can work from the same data over a secure extranet with information personalized based on security credentials. BI’s unique centralized administration and role-based security assures that healthcare providers have security measures at every layer of the architecture. With secure, essential clinical information, individual practitioners can diagnose and prescribe more quickly and provide top quality care with greater peace of mind.

The different types of reports permissible are

Avg cost per case Clinical outcomes
Cost & clinical data Cost affectiveness
Disease management In-patient admission rates
Investment in research programs No of procedures vs benchmarks
Patient safety & staffing Performance reporting
Population risk Resource cosumption vs benchmark
Response time Treatment

Patient Care KPI

Healthcare providers want to offer the highest-quality patient care in the most efficient way possible with improved patient access to care. BI enables the right people to access the right information at the right time, delivering a single platform to healthcare providers for sharing information with patients for better decision-making and connecting patients across hospital, nursing home, physician office, and community social support settings. By providing easy, secure access to crucial information, BI supports safe care delivery, assists clinicians in evidence-based clinical decision-making, and facilitates seamless care coordination across clinical settings. Providers use BI to monitor patient diagnoses and use of healthcare services in order to improve patient care, reduce wait times, and administer more effective treatments.

Clinical Outcomes Day to next appointment
Dispatch request & monitoring Patient functionality
Patients wait time in admission Patients wait time in pharmacy
Service complaints Service Level Agreement reporting
Services performed Timeliness of service

Financial KPI

Healthcare organizations need visibility into the full scope of their financial operations. BI provides full transparency, analysis, and delivery of financial and operational data. BI enables healthcare providers to drill from reports into detailed analyses of costs and revenues, view data underlying cash flow statements, and compare planned versus actual income and margin. BI’s analytical capabilities allow providers to analyze current care practice patterns to identify unnecessary or under-utilized services, execute cash flow analysis, forecast collections, and monitor underwriting requirements. In addition, BI enables healthcare organizations to meet statutory reporting requirements and ensure accountability from financial analysts to business unit management, executives, and directors.

Accounts payable Accounts receivable
Accrued expenses Assets in current vs prior period
Bad debt as % of net revenue Budget variance analysis
Capital expenditure growth rate Cash & equivalents
Cash collected vs target Cash on hand
Current asset turnover Current Ratio
Customer Plan migration Net assets
Net income Operating margin
% of capital expenses % of cash flow to total debt
% of cash flow to total liabilities % of charitable revenue
% of debt to capitalization % of out patient revenue

Quality & Performance KPI

Providing safe, quality care is a top priority for healthcare organizations. BI allows providers to improve performance by leveraging evidence-based performance data, tracking variations in quality, providing patient dashboards, setting alerts, and checking drug interactions. Providers can use BI to monitor the quality of their care according to the Health Plan Employer Data and Information Set (HEDIS) standards established by the National Committee for Quality Assurance to allow for performance comparisons across quality, patient access, patient satisfaction, utilization, and financials. Additionally, Dashboard can be used for improving quality at healthcare organizations, which empowers managers to make more timely decisions and participate in on-the-spot quality improvements. BI allows providers to identify the presence or absence of interventions recommended by evidence based medicine.

Dose Frequency of attacks
Handling costs Avg length of stay for patients
Length of visit with nurse practitioner Length of visit with physician
Medication error Monthly drug cost
% of follow ups No of emergency room visits
No of hospitalizations No of patients admitted
No of patients referred outside No of patients with infections
No of patients with vaccinations No of physicians visit
Packaging cost Patients satisfaction

Marketing KPIs:-

With a No. of hospitals, clinics & nursing centers, healthcare providers must place a greater focus on their marketing efforts to stay competitive. BI helps analyze the success of marketing efforts designed to demonstrate the clinical effectiveness of their products and services, create an increased feeling of overall wellness, and reduce workplace absenteeism. BI also allow providers to determine the most cost-effective marketing techniques by tracking campaign costs against budget, calculating the return on investment for campaigns, and comparing performance against goals. In addition, BI allows healthcare providers to use dashboards to show which products and services are profitable in specific patient segments and measure how their marketing efforts and corporate communications alter brand perception and affect brand performance.

Acquisition Advertising budget per procedure
Avg visit length to website Brand Management
Churn Analysis Customer Profitability
Customer Segmentation Demographic Analysis
Loyalty Program Market Share by Business
Marketing Focus group scores No of visit to website
Patient satisfaction Physician practices
Preference & permission bundling Retention

Above mentioned are some of the commonly used KPIs. Keeping a tab on them via selecting a proper Business Intelligence tool & correct implementation, can surely help them to rise above the competition & deliver a world class service to the patients.

For any query regarding Business Intelligence implementation, plz mail [email protected]

Nikhilesh

+91-7893947676

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 travelled, 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 benenfit 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 deliverables 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 postive 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 continous 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