Dates in Jasper iReport – Expression for default values

This post teach you how to play with default date parameters & calculations on dates in jasper iReport.
In this tutorial you learn how to make use of  “SimpleDateFormat” class and java.util.Calendar class.

I) Let us start with Report Start(Start Date) and Report End(Report End) parameters.

In some reports you need to give default parameters in your start date and end date.
Eg:
Start Date = Previous month 1st day
End Date= Previous month last day
Qn :
 How to write default expression for this ?
Ans:
NOTE: 
$P{cal}  is a parameter used to reduce the expression complexity.
$P{cal}=java.util.Calendar.getInstance()
Start Date: ( java.util.Date)
(
$P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()
 End Date(java.util.Date)
(
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()
Sample output(i.e., default values in the date place holders would be)
Star Date :  2013-10-01
End Date : 2013-10-31

II) To get the individuals of a date using SimpleDateFormat class.

From Report Start  Date($P{ReportStart})
StartYYYY
Integer.parseInt(new SimpleDateFormat(“yyyy”).format($P{ReportStart}))
StartMM
Integer.parseInt(new SimpleDateFormat(“MM”).format($P{ReportStart}))
StartDD
Integer.parseInt(new SimpleDateFormat(“dd”).format($P{ReportStart})) 
Similarly you can extract the parts of the date for ReportEnd parameter($P{ReportEnd} )

III) To get the current parts of the date from Calendar class in iReport

CurrentYear:
$P{cal}.get(Calendar.Year)
CurrentMonth:
$P{cal}.get(Calendar.MONTH)+1
CurrentDayOfMonth:
$P{cal}.get(Calendar.DAY_OF_MONTH)

Jaspersoft System Integration (SI) Partner Helical IT Solutions

Helical, a fast growing Business Intelligent (BI) organisation, offering Open source BI solutions across verticals has been appointed by Jaspersoft as a System Integrator Partner. As per the tie-up, Helical IT Solutions will be a System Integration Partner for Jaspersoft in the India/South Asia region.

As a part of this appointment, Helical IT Solutions will provide services for Jaspersoft’s BI suite including reporting, dashboards, ad-hoc and OLAP analysis, ETL/data integration. Helical IT Solutions’ knowledge of both Business Intelligence applications and the Jaspersoft BI platform is bound to ensure successful development and deployment of BI solutions.

Taking on this appointment, Mr Nikhilesh Tiwari, Founder, Helical IT Solutions shared “We are extremely happy and delighted with this tie-up and regard this as a great achievement for our organisation. We will definitely look forward to this collaboration with Jaspersoft to be beneficial for both the companies.

 

Mr Nitin Sahu, Cofounder at Helical IT Solutions added, “We have been working on Jaspersoft BI platform for a long time and we are happy to be their SI. With our technical strength and partnership with Jaspersoft, we are hopeful of surpassing our customers’ expectations

 

Mr. Royce Buñag, Vice President Asia Pacific at Jaspersoft said “Helical IT Solutions has impressed us with their knowledge of BI solutions.  They have already shown themselves to be a valuable partner with current and ongoing customer engagements.  We are delighted that they have also agreed to be one of the sponsors for the upcoming JasperWorld event in Bangalore, which is a great way for us to showcase the collaboration to customers. We look forward to a long and successful partnership.

Jaspersoft’s open source business intelligence solutions  is the world’s most widely used BI software, with more than 8 million total downloads worldwide and more than 10,000 commercial customers in 96 countries. The Jaspersoft provides a web-based, open and modular approach to the evolving business intelligence needs of the enterprise. It has 90,000 registered members working on more than 350 projects, which represents the world’s largest business intelligence community.

Helical IT Solutions is an open source DWBI company and has expertise in providing simple, practical & affordable solutions which are suitable for business users, right from CEO, CXO, line managers & to every end user of the enterprise. With a quick turnaround time, the company can provide mobile BI solutions, on premises or hosted SaaS solution, hence catering to every type of need. Helical offers services on entire BI stack, ranging from ETL, DW, Data mining, Analytics, BI solution. They also provide integration of disparate data sources and offers powerful interactive tools like balanced scorecards, personalized dashboards, key performance indicators, automated alerts, graphical mining, cross tab reporting and more.

The press release got published at many places which includes

IndiaInfoline
http://www.indiainfoline.com/Markets/News/Jaspersoft-appoints-Helical-IT-Solutions-for-system-integration-partner/5810655543

Eletronics for You
http://www.efytimes.com/e1/fullnews.asp?edid=119811

Light-Reading
http://www.lightreading.in/lightreadingindia/news-wire-feed/285912/jaspersoft-appoints-helical-solutions-integration-partner

INfotechLead
http://infotechlead.com/2013/10/29/jaspersoft-appoints-helical-system-integration-partner-south-asia/

Channeltimes
http://www.channeltimes.com/story/jaspersoft-appoints-helical-it-solutions-as-partner-for-bi-solution/

Techvorm
http://www.techvorm.com/helical-ties-jaspersoft/

SIlobreaker
http://news.silobreaker.com/jaspersoft-appoints-helical-it–5_2267202378157523078

Digisecrets
http://www.digisecrets.com/news/jaspersoft-appoints-helical-it-solutions-as-their-system-integration-partner-for-indiasouth-asia-region/

CIOL
http://www.ciol.com/ciol/news/199620/jaspersoft-appoints-helical-integration-partner

Embedding BI Reports (Jasper Reports/ Jasper Server/ Pentaho Reports/ Pentaho Server)

There are many methods of embedding BI reports, this blog tries to explain the meaning of Embedded BI, different approaches which can be used, their respective advantages and disadvantages

 

A)     What is embedded BI?

Lets say you have developed your entire BI solution which includes reports, dashboards, mashups, analysis etc, the platform which is used can be anything like Jaspersoft or Pentaho. The process of integration of this BI solution with your existing application/software/portal is called Embedded BI.

Embedded BI provides a lot of advantages like ability to add more reports over and above the existing canned reports which your application might provide, the look and feel and customization of all the reports can be changed as per the software/user preference, better visualization, unified application for everything. Biggest advantage can be your product value will increase much more, hence resulting in much more sales and revenue.

a) Embedding using iFrame Method :-

iFrame method of embedding BI reports can be used for embedding Jasper server, Jasper reports and Pentaho server, Pentaho reports. The report pulled in this case is using iframe as a tag. Even though the report is external, but to an end customer he will not be able to recognize the same.

The advantages of using iFrame method is this method is fast and quick to implement. The disadvantages includes it will be difficult to manage user, also in this case there can be cross browser compatibility issues, security is a major issue here.

 

b) Embedding Using webservice:- Webservices can also be used to integrate reports, dashboard, interactive report or/and ad-hoc reports. We can use webservices to accept the information from the Jasper/Pentaho server, this Jasper server can be anywhere ( on Tomcat or any other server). Using webservices we will invoke the response from Jasper Server/Pentaho server. This response can be can be then embedded inside application/software. Jasper server/Pentaho server also supports RESTFUL service (JSON format responses). In this approach there are no or less security related issues.

The disadvantage of this approach is there are no interactive features available of the report which thus results in poor user experience. Also in order to handle and understand the responses from jasper server/Pentaho server, custom coding is required in the application. In case if we had put input controls in the report, the same functionality then needs to be implemented in the application via custom coding. Aside, having many webcalls at times can be resource intensive as well.

The advantage of this approach is webservices approach gives more seamless integration since there is no security issue and no thirdparty calls (like in the case of iframe). Also in this case there are no interbrowser compatibility issues (assuming it is handled by your developer team).

c) Embedding using Jasper/Pentaho Libraries:- In this method we create JRXML/Pentaho report (prpt) files using ireport/Pentaho report designer. Compile these reporting using Java API, and then use this compiled code to generate views using Jasper Server API calls

The advantage of this method is this gives the most seamless integration. Also since all the information is present as a part of the application/server, no third party calls are required.

Disadvantage of this approach is some interactivity is lost in this method. Also the input parameters are also required to be created in the own application via custom coding.

 

Please get in touch at [email protected] for much more details about the respective approach, how to embed the same using these approaches, which was is the best suited to your needs and much more.

MariaDB to MariaDB Data Replication Strategies

Data Replication Strategies

 

This blog will try to explain about the different approaches which can be used for the data replication from Master DB (Maria DB) to the salve Maria DB.

Replication Benefits

Replication is having a number of benefits, some of them includes:

  • Scalability: By having one or more slave servers, reads can be spread over multiple servers, reducing the load on the master. The most common scenario for a high-read, low-write environment is to have one master, where all the writes occur, replicating to multiple slaves, which handle most of the reads.
  • Low hardware : Having a slave system db can provide the freedom to play with that data without affecting master database. We can have all the reporting etc done on this particular database. Hence, having a very high hardware requirement on the master database can be avoided.
  • Data analysis: Analyzing data may have too much of an impact on a master server, and this can similarly be handled on a slave server, while the master continues unaffected by the extra load.
  • Backup assistance:  Backups can more easily be run if a server is not actively changing the data. A common scenario is to replicate the data to slave, which is then disconnected from the master with the data in a stable state. Backup is then performed from this server.
  • Distribution of data: Instead of being connected to a remote master, it’s possible to replicate the data locally and work from this data instead.

 

 

Approach 1 : Maria DB to Maria DB replication using Master slave configuration.

 

Data Replication

Replication is a feature allowing the contents of one or more master servers to be mirrored on one or more slave servers.

We can exert control over which data to replicate. All databases, one or more databases, or tables within a database can all be selectively replicated as well.

The main mechanism used in replication is the binary log. If binary logging is enabled, all updates to the database (data manipulation and data definition) are written into the binary log as binlog events. Slaves read the binary log from each master in order to access the data to replicate.

In this particular approach, masters and slaves do not need to be in constant communication with each other. We can take the servers offline or disconnect from the network, and when they come back, replication will continue where it left off. The slave in this case will be in access read only mode, the data copying from Master to slave will be on real time without any lag. Hence, on the BI and reporting front, we can have real time BI capabilities.

 

 

Approach 2 : Usage of Third party tools

The second approach which can be used for data replication from Master DB to Slave DB is by the usage of DB replication third party tools. One of the example which could be cited here is a tool like Tungsten. Tungsten is an open source third party data replicator tool. It not only works on MySQL but also on Maria DB. Tungsten can help solve problems like promoting masters easily from pools of slaves, replicating data between different database versions, replicating efficiently across sites, building complex topologies, and parallelizing data flow between servers. Tungsten Replicator runs equally well in cloud as well as locally hosted environments. Tungsten Replicator users range from tiny start-ups to the largest web properties on the planet.

 

 

Approach 3: using ETL. CDC implement, free version

The third approach which could be followed is to use ETL. We are having open source ETL options which includes Talend Open Studio or Kettle – Pentaho Data Integrator.

For having the master slave databases configuration implemented, we would be implementing CDC (change data capture). By CDC we can identify, capture and deliver only the changes which are made to the master database.  Frequency of the same can be set. Change Data Capture (CDC) is based on Publisher/Subscriber principle. The publisher captures the change data and makes it available to the subscribers. Implementing CDC will also help in reducing the workload on ETL since we would be sharing the information of only the changed data.

The advantages of implementing via ETL is security can be implemented like data encryption, while copying data we can have options like we can select which all tables data we want to replicate, how much amount of data we can to replicate, rules can be implemented while replicating etc.

start_date and end_date parameters – giving default values – iReport

Hi Guys,

This post let’s you know how to use date parameters in SQL query using between operator and using  multi select parameter.

Example Query:

SELECT
column1, colum2
FROM
XYZ_table
WHERE
$X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.

Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.
http://community.jaspersoft.com/questions/819583/default-values-date-parameters

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)


Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False

(
$P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
($P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
)
? null : $P{cal}.getTime()

end_date Default Value Expression:

(
$P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()


In the above two start_date and end_date parameters you can find $P{cal} – which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

NOTE:
for all the input controls Parameters classs is : java.util.Calendar

Sadakar
(Learning never exhausts the mind)

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

Hire Jaspesoft Consultants / Hire Jaspersoft Developers / Hire ireport Developers

Helical IT Solutions, with its deep expertise on Jaspersoft and being an open source DW BI expert, can help in designing and constructing your BI solution. Helical IT solutions is having experience on end to end Jaspersoft BI suite right from ETL, data warehousing, ireport, talend, weka, R analytics, reports and dashboard designing, mondrian OLAP analysis etc. Our in-depth knowledge of both Business Intelligence applications and the Jaspersoft BI platform ensures successful development and deployment of your BI initiatives.

Helical can help at a number of aspects like

 – BI Tool Selection : Based on your requirement, with our deep rooted expertise on a number of BI solutions, we can help you in determining which BI tools would be best suited for you. Whether you actually need to purchase enterprise edition or if your requirement can be fullfilled via community edition by using some tweaks or custom coding or best of breed solution. The tool selection will be done after a thorough analysis of your requirements, present hardware and software, budget, speed of solution delivery requiered etc.

– Jaspersoft BI POC :- If your company is still in dillema to go ahead or not, then we can help you in developing a POC (proof of concept). We help evaluate the open source BI and ETL solution for your environment, demonstrate how it will work, and recommend how you can leverage the technology, even if you have other BI products.

– Jaspersoft BI Solution Development :- With our technical expertise and domain knowledge of end to end BI solution development in a number of different verticals ( we have executed BI solutions in energy, healthcare, insurance, supply chain, ecommerce, human resource), we can help you in designing and developing the perfect BI solution for you. We will help you in the right KPI parameters selection, reports and dashboards development, OLAP cubes, ETLscripts generation – talend, plugin desiging, security implementaiton, application integration, fetching data etc. For designing the solution for you, we can use any of the below mentioned tool present in the BI suite.

  • Jaspersoft Business Intelligence Platform
  • ETL – Talend (We can work on both Talend Open studio or Talend Integration Suite)
  • Jaspersoft Analysis (Mondrian)
  • R analytics
  • Jaspersoft Reporting – using ireport
  • Dashboards

– Jasper ETL – Talend :- Jasper BI uses Talend ETL (Extract, Transform, Load) suite that allows your existing data to be transformed, summarised and aggregated into a form that puts the business information at your fingertips. At Helical, we have extensive talend ETL knowledge, employing it both for Business Intelligence purposes and to transform and migrate data between systems, softwares & databases. Having a lot of experience on data warehousing, ETL, data modelling, data mart designing, query optimization etc, our dedicated team can also help you with all of your ETL requirement, creating transformations and jobs etc.

– Training and Documentation :- A software is as good as its end user. In order to make sure that the end user is able to use the developed software pretty well, we will be providing all the neccesary training to the end user. Also, the same would be supported by documentation like installation document, troubleshooting document etc.

– Support :- Helical team will be providing 24×7 support to the client, thus ensuring that the solution is up and running always.

Helical provides support and services on the entire Jaspersoft BI stack, as mentioned below

  • Jaspersoft Business Intelligence Platform
  • Jaspersoft reports – using iReport
  • Jasper ETL – Talend
  • Jaspersoft Analysis (Mondrian)
  • R Analytics
  • Dashboards
  • Metadata design and development
  • Performance tuning
  • Application integration

 

Contact us at [email protected]

Hire jaspersoft consultant

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