Sorting and limit the bars on bar charts in Pentaho CDE without ORDER BY and limit functions in SQL Query ( Descending order)

Link

Hi Guys…!!!
This post tech you how you can make your bar chart dynamic..
You can limit the number of bars on bar charts with out using the “limit” function in your query as well you can also sort the bars in ascending or descending order with out using ORDER by clause  in SQL.

Some times you might have to deal with this type of functionality in your dashboard.
So here is a working example. Follow the steps…
Environment:
Pentaho BI server 4.8 stable with C-Tools(CDA,CDE,CDF 13.06) installed.
PostgreSQL
Aim : 
1) Creating a dashboard with two horizontal bar charts.
2) How to use single query result set for two charts
3) Order by the bar on the dashboard with out writing ORDER BY clause in SQL.
4) limit the number of bars on charts with out writing “LIMIT” clause in SQL

Suppose your result set is like below

Query Eg :
SELECT ColumnA, columnB,ColumnC from table_Name  GROUP BY ColumnA

Result set Assumption:
ColumnA          ColumnB       ColumnC
pentaho               45                     75
jasper                  23                     34
pdi                       90                     22
and assume there are 30 rows in the result set …

You are using single query to plot the two charts on dashboard using ColumnA,ColumnB & ColumnA,ColumnC.

When you use ORDER BY clause in the query, you can only either order by columnB or ColumnC but you can not order by with ColumnB and ColumnC.

* In CDE, there is a functionality called “Indexs”. Indexs for columns in pentaho CDE starts from 0,1,2 and etc.
* So ColumnA index is 0, ColumnB index is 1 and vice versa.

* Prepare your environment for the dashboard and have a look at the “Data sources” now.
* Click on “Output Options” and give index values as 0,1,2 by clicking “add” button multiple times.

How to get ColumnA, ColumnB on first chart ? (B’z you are using single query result set)
* Now click on the first chart component
* In the Advanced properties click on “Pre Execution”
* Write the below code to fetch ColumnA, ColumnB on first bar chart.

function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 1},
{indexs: 2}
];
}

NOTE:
ColumnA=category and  is accessed with Index 0
ColumnB=value and is accessed with Index 1
index2 is written in the code b’z to ignore the value appending to the category showed on bars.

How to sort and limit the bars on first chart ?(B’z you are using single query result set)

* Click on the “Post Fetch” option from the Advanced properties.
* Write this code, this code will limit the number of bars on chart and sort the bars in descending order.
* In the code below “param_no_of_end_points” is the parameter created in the “Generic” section.
* And for the parameter create a select in the “Selects” section with “Text input” component.

function f1(cdaData) {
var categIndex = 0;
var valueIndex = 1;

var param_no_of_end_points = +Dashboards.getParameterValue(“param_no_of_end_points”);

if(isNaN(param_no_of_end_points))
{
param_no_of_end_points = 0;
}

var resultset = cdaData.resultset.slice();
var compareDesc = function(a, b)
{
return a === b ? 0 : a > b ? 1 : -1;
};
resultset.sort(
function(rowa, rowb)
{
return compareDesc(+rowa[valueIndex],+rowb[valueIndex]);
}
);

if(param_no_of_end_points > 0)
{
resultset.splice(0,resultset.length-param_no_of_end_points,0);
cdaData.resultset = resultset;
}
cdaData = {
metadata: cdaData.metadata,
resultset:  resultset
};
return cdaData;
}

Write the same code for the second chart but make sure to use different function names and Index value. In an application we can not write two functions with same name.

Save the the dashboard and see the preview.

Meet us for more solutions @  http://www.helicaltech.com/contact.php

Sadakar
BI developer

 

Jaspersoft CE v/s EE / Having Jaspersoft EE functionality in Jaspersoft CE

Jaspersoft CE versus EE / Having Jaspersoft EE functionality in Jaspersoft CE

 

Jaspersoft is world’s leading Open source BI software with more than 1.75 million download and usage. It comes in two variants, one is the community edition which is free and the other is the enterprise edition. Via this blog, we would try to understand the differences between the CE and EE. Also, we will make an attempt to understand and have Jaspersoft Enterprise Edition functionality in Jaspersoft Community Edition. Many a times, customers (esp startups and SMEs) dont have the budget or requirement to go for a full license, but they want full functionality which are present in EE, for them we have also discussed possible way outs as well.

 

a) Report Designer: Jaspersoft is known for its pixel perfect reports. This is created by iReport designer (free and open source). This tool helps in creation of complex reports with charts, graphs, tabular data, cross tabs, images etc. These reports can further be downloaded in different formats.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

b) Adhoc Report designer: Very beneficial for Business user, it gives a pivot kind of functionality, thus an end user can himself drag and drop the fields and create his own report on the fly. He can see this report as either a tabular data, or visualize it in different formats, save this and download this.

 

Availability: Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: In order to use adhoc reporting feature in Jaspersoft Community edition (CE), we can use a best of breed kind of solution. We can use Pentaho SAIKU plugin (its free), using this plugin for adhoc reporting, we can call it inside liferay and have adhoc reporting functionality in Jaspersoft as well.

 

c) Reporting Engine: Jasper reporting engine which is java based is again free and open source. It can fetch data from any kind of data source and provide picture perfect reports which can be seen, download (in different formats), printed.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

d) Dashboards: A dashboard can be a mix and match of reports, data visualizations, web APIs data, interactive parameters etc. Dashboard functionality is present only in the Jaspersoft EE

 

Availability:  Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: For implementing dashboard inside Jaspersoft Community Edition (Jaspersoft CE), we will have to go ahead with a best of breed solution. What we can do is for dashboards, we can use Pentaho CDE and CDF components. For representation of that, we can either use liferay or Alfresco.

Another alternative is we can call multiple instances of ireports inside liferay, thus creating a dashboard. For this a portlet has to be developed in liferay.

 

e) Interactive report viewing: This lets user to see the columns and perform functions like filter, arrange, sort etc.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

f) In-memory analysis: In memory basically helps in analyzing, seeing reports dashboards analysis etc on real-time and at a very high speed because it directly dumps all the information in its in-memory and doesn’t access db again and again for any requirement. This results in very high speed and throughput.

 

Availability: Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: Again, to implement In-memory analytics in Jaspersoft Community edition, we will have to use a best of breed solution. We can go with the Pentaho SAIKU plugin (which is free of cost) and liferay usage for representation

 

g) Server Repository: Place to store reports, dashboards, views, mashups, analytics, user profiles etc.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

h) Report Scheduling / Report Bursting: Email scheduling of the reports (could be time or trigger based).

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

i) Support: Jaspersoft is having multiple level of supports like standard support, premium support but it is only available to enterprise edition customers.

 

Availability: Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: Jaspersoft CE customer, can either will entirely depend on community support or they can come to companies like us (Helical IT Solutions), which have expertise on Jaspersoft and can help out on all the issues.

 

 

j) Mobile Business Intelligence: – Native app supporting iOS and Android platform. Touch-based browser support for iPad. iOS and Android SDK for embedding BI in native mobile app.

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

k) Metadata Layer: Metadata basically means data about data content. Via metadata layer, we create another layer containing information about the database, hence thus implementing data level security.

 

Availability:    Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: For implementing metadata layer in Jaspersoft CE, what we can do is we can use OLAP server (which is present in CE – community edition) to design metadata layer along with implementing role and user based security.

 

l) User access to reports/dashboards and other things: This facility is again present in both the versions of Jaspersoft.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

m) Data Virtualization: This basically means integrating multiple data sources into a metadata view, and then doing BI work on it. This step reduces the work this involved in ETL.

 

Availability:  Jaspersoft CE: No

Jaspersoft EE: Yes

 

 Alternatives: The entire work of data virtualization can be done by writing ETL jobs and stored procedures. For ETL, Talend or Kettle either of the tool can be used.

n) Data Integration (ETL): ETL stands for extract transform and load. Extract means extracting data from different dbs (big data, columnary databases, graphical databases etc), web APIs etc. After extracting, transform the same according to the business rules and finally load it into the target system which could be a database, data warehouse, data mart etc. Jaspersoft has ETL tool by the name of Talend (also known as Jasper ETL).

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

o) Interactive Visualizations: This basically means very interactive HTML5 charts with animations, hover information, interactivity in terms of selection, zoom in zoom out, interactive legends etc.

 

Availability: Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: These kind of interactive visualizations are not present in Jaspersoft community edition. For enabling that, what can be done is we can design plugin designed like jcharts and openflash (for openflash plugins are already available). One of the very famous example is Pentaho Community edition having interactive charts by CCC components (community chart components). We can also use CCC and thus have best of breed solution.

 

p) OLAP Server: OLAP (online analytical processing) helps in analyzing large amount of data using querying, visualizations, multi lever analysis, slice and dice, filtering etc. OLAP helps users to understand multi-dimensional data from multiple-view point. It consists of mainly three functions – consolidation, drill down, slicing & dicing.

 

Availability: Jaspersoft CE: Yes

Jaspersoft EE: Yes

 

q) Multi-tenancy: This allows single instance support multiple customers, with each customer having his own userid password.

 

Availability: Jaspersoft CE: No

Jaspersoft EE: Yes

 

Alternatives: Multi-tenancy can be implemented in Jaspersoft Community Edition (CE) using Liferay portlet.

 

r) License: Jaspersoft CE has General public license whereas the license is having commercial license. Hence, if a client is Jaspersoft EE client, he does not need to open up his source code to the public, which is not the case with Jaspersoft CE clients.

 

Thanks for reading the blog. Get in touch with me at [email protected], Cell : +91-7893947676

Jaspersoft Default Passwords

1.  Jaspersoft Corporation – Jasper Reports Server
Method    HTTP
User ID : superuser
Password : superuser
Level    Administrator
Doc    http://community.jaspersoft.com/documentation/jasperreports-server-install-guide-community-edition

2.  Jaspersoft Corporation – Jasper Reports Server
Method    HTTP
User ID   : jasperadmin
Password : jasperadmin
Level    Administrator
Doc    http://community.jaspersoft.com/documentation/jasperreports-server-install-guide-community-edition

3.  Jaspersoft Corporation – Jasper Reports Server
Method    HTTP
User ID :   joeuser
Password :   joeuser
Level    User
Doc    http://community.jaspersoft.com/documentation/jasperreports-server-install-guide-community-edition
Notes    Sample data user

4.  Jaspersoft Corporation – Jasper Reports Server
Method    HTTP
User ID   :  demo
Password :   demo
Level    User
Doc    http://community.jaspersoft.com/documentation/jasperreports-server-install-guide-community-edition
Notes    Sample data user

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

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.

Installing Jasper server with MySQL Database

This blog will help you understand how to install Jasper Server with MySQL database

Pre requisites
1. Install the Oracle/Sun Java JDK 1.6 or 1.7. Create and set the JAVA_HOME system
environment variable. i.e., Make sure Java path are set for your user profile or system
profile
Please follow the link to get the software.
http://www.oracle.com/technetwork/java/javase/downloads/java-se-jre-7-download-
432155.html

2. Jasperreports-server-cp-5.0.0-bin-zip
a. Download Jasperreports-server-cp-5.0.0-bin-zip from the below mentioned location.
http://sourceforge.net/projects/jasperserver/files/JasperServer/JasperReports%20S
erver%205.0.0/jasperreports-server-cp-5.0.0-bin.zip/download

3. MySQL database.
a. Please follow this link to get the software.
http://dev.mysql.com/downloads/mysql/5.0.html
Note: We can use any other database as well. For demo purpose we are using mySQL.

4. Apache Tomcat Server
a. Please follow this link to get the software.
http://tomcat.apache.org/download-60.cgi

Follow below mentioned steps to install Jasper server CE 5.0.0 with mySQL database.

Step 1:- Extract all files from jasperreports-server-cp-5.0.0-bin.zip. Choose a destination, such as C:\Jaspersoft on Windows. The directory, jasperreports-server-cp-5.0.0-bin, appears in the file location you choose.

Step 2 :- Copy the <database>_master.properties file for your database from sample_conf and paste it to buildomatic:
Copy from — <js-install>/buildomatic/sample_conf/
Paste to — <js-install>/buildomatic
For example, copy mysql_master.properties to <js-install>/buildomatic.

Step 3:- Rename the file you copied to default_master.properties.

Step 4:- Edit the default_master.properties file to add the settings for your database and application server.

Below mentioned is a sample property values MySql database.

MySQL appServerType=tomcat6 [tomcat7, tomcat5, jboss, jboss7, glassfish2,
glassfish3, skipAppServerCheck*]
appServerDir=c:\\Program Files\\Apache Software Foundation\\Tomcat 6†

dbUsername=root
dbPassword=password
dbHost=localhost

Set the following in the file
webAppNameCE = jasperserver

***NOTE:
Place mysql-connector-java-5.1.10.jar driver in the following location
D:\install SW\jasperreports-server-cp-5.0.0-bin\buildomatic\conf_source\db\mysql\jdbc

Step 6:-
Run the js-install scripts.
a. Start your database server.
b. Stop your application server.
c. Open Command Prompt as Administrator on Windows.
d. Run the appropriate commands:

js-install-ce.bat : Installs JasperReports Server, sample data,
and sample databases (foodmart and sugarcrm)

js-install-ce.bat minimal : Installs JasperReports Server, but does not
install sample data and sample databases

NOTE
If you installed the optional sample databases, complete the installation by executing this command: js-ant deploy-webapp-ce from buildomatic location in command prompt.

For example in the following location:
C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps

Step 7:-
Run the tomcat server and type the jasper server url.
For example: http://localhost:2222/jasperserver/login.html

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]

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 🙂

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]

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)