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.

Tech Tips in Penaho Server – publishing password setting for server – Disable the folder names in Browser panel in the Server

Hi guys..

The below tips are very useful in Pentaho Server for BI developers.
1) publishing password setting for server
You need to publish the reports or schema(s)[developed in PSW] once you done with developing. For this you need set the publishing password for Pentaho server.
Go to this location 
<location of Pentaho>biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\publisher-config.xml and edit the “publisher-config.xml”  file.

Give your publish password b/w  these tags  <publisher-password> </publisher-password>

Example:
<publisher-password>password</publisher-password>    ( password can be any name that you wish to give)

Snapshot is a worth more than text. find the image below.

Save the file. stop the server and start the server.

2) Disable the folder names in Browser panel of Pentaho Server
* What ever the folder you create in Browser panel of pentaho server, it directly stores   under pentaho-soulutions
i.,e in this location
<Pentaho location>Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions
* You can disable all of your folders that comes with installation & the later created ones  which you don’t want to appear in the browser panel.
*   Inside every folder(pentaho default or the folders created by you) you can find “index.xml” file. You need to edit this file. You need to change visable tag as “false”.
Find the sanpshot.

You need not restart the server. Just you need to refresh the browser panel in the server.
You can observer the invisibility of the folder.

Sadakar Pochampalli.

 

 

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

Pentaho Community Dashboard (CDE) Basics – Creating a Simple Dashboard

Pentaho Community Dashboard Editor (CDE)
CTools Website – Community Tools

http://www.webdetails.pt/ctools.html

What is CDE ?
* CDE is one of the plugins for Pentaho BI Server, contributed and maintained by     Pentaho Partner webdetails.
* We create dashboards using this tool.
* Community Dashboard Editor (CDE) was born to simplify the creation, edition and      rendering processes of the CTools Dashboards.
* CDE is a very powerful and complete tool, combining front end with data sources and custom components in a seamless way.

Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?

You can find the symbol “Pentaho Market place” on the menu bar..
Click on it you can see the Pentaho Marketplace editor as shown in below figure.
Find Community Dashboard Editor and Click on install.
As I have already installed it is showing as Up to Date.
Its generally a good practice to keep on updating the plugins with the latest version available in the market.
NOTE:
You must install the dependent plug-ins before you start working with CDE.

The dependencies for CDE are:
CDF(Community Dashboard Framework)
CDA(Community Data Access)
You must restart your server to take the effect of installation.

Where you can find the sample examples after installing?
* In the left side panel you can find plugin-samples folder.
* Expand it and then click on CDE
* In the Files you can find sample example CDE Sample Dashboard.
* Right Click on CDE_Sample Dashboard àThen click on Edit.
CDE has 3 major components
They are.
* Layout
* Componets
* Data Sources.CDE has developed based on MVC-2 architecture of Advanced Java Technologies.
* Layout – View
* Components – Controller
* Data sources – Model.
Find the images below to understanding the basic of CDE.

* Scroll down the dashboard. Find “About” on the left side and find the version number of CDE installed.

* Close the version window and Just click on Preview. I’ll let you know how to work out with all the components in CDE with a good working example.
* Now, just click on Preview. You can find the output of the dashboard as shown in below figures.

* Scroll down.

* Read the points that were in boxes. You will get the basic idea of CDE dash boarding.
Example:
Aim : To design a simple chart(on dashboard)
Environment:
* Pentaho BI Server community 4.8 stable version.
* CDA,CDF & CDE installed in the server from Market place.
Database: SampleData (It comes along with pentaho installation.. for this artical I’m not connecting to any external database.)
* This example was based upon CCC-Version1. The current Example which I’m going to explain is based upon CCC2-Version2.
* There are a lot of changes made from CCC-V1 to CCC-V2.
* Make sure your environment of CDE supports CCC-V2 for the example.

Step 1: Creating New CDE Dashboard
There are two ways to create a new CDE dashboard.
i) From the menu bar
ii) By clicking CDE icon
 Save your dashboard
 Click on Save -> Save your dashboard in your fav folder with your fav name.
 

After saving your dashboard  “Refresh your Repository” and then you can find the file as shown below figure.

Step 2:  Working with Layout, Componets and Data sources.
CDE is mainly works on scripting such as css, javascript.
Click on + sign.. (We are going to add Cascading Style Sheet code(css) code to our dashboard).
Resource type : css
External file
Resource type : css

Resource file : click on   ^  that is highlighted using arrow in the below image.

   * Give Styles2(do not give Styles2.css i.e, only give name but not extension).
   * It automatically cmes with .css extension and with $ and flower braces.
   * Save the css file externally to a folder as shown in below figure.
   * To see the Styles2.css appearing in the folder your file must contain some css code.
   * You must refresh your Repository to see the name of the file(Styles2.css) in the folder.
Giving Title to Dashboard:
*  CDE layout mainly works on Rows and Columns.
* Click on Add row symbol as shown in below figure.
 *  Give name of the Row and give back ground colour as shown in below figure.

* And give Corners: Round( it is below the back ground , not shown in the image-it is not visible here in the image)

 *  Now add Column(find ||) symbol on Layout Structure and click on it.

* Give all the necessary properties as shown in the below figure.
  Name: title
 HTML : <h1> Sales OverView YDT <h1> ( as show in figure at arrow symbol click on button.. You will be prompted to a new window there you have to write this HTML Code)
Font Size: 10
 See the preview:
The preview consists of only the title of the dashboard with its layout.
It is further going to be modified.

 Add your .css code here…

Css code
Body{
                background-color:#fffdf1;
}
h1{
                font-size:24px;
                color:#fff;
                margin-left:10px;
                margin-top:10px;
}
 Now see the preview.

By the time you get some idea on it.

Do as follows, I’m reducing the images now as you might get some idea..
Add another row -> Column->Html  as shown in figure.
For html give Name as title and write HTML code <h3>Sales Performance</h3>  in HTML editor
Add the following code to Styles.css to editor
h3{
    font-size:18px;
                font-weight:bold;
                color:#b68c58;
                margin-left:10px;
                margin-top:10px;
                margin-bottom:8px;
}
.salesTitle{
    color:#666;
                height:2px;
    margin-top:10px;
                margin-left:10px;
                margin-bottom:2px;
                width:939px;
}
Find the images for doing the above and see the preview again
 Add another column as shown in below figure

Give the following properties:

Name: sales_chart
Span size: 16
Now working with Data Sources and Query part:
  Click on Data Sources

·         Find SQL Queries in Left panel.
·         Click on sql over sqlJndi
·         In left panel give Properties
o   Name : get_sales_performance
o   Access Level : public (default)
o   Jndi: SampleData(We are working with pre defined database that comes along with pentaho software to develop this dashboard)
o   Query:
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2003
)
Working with Component
·         Click on Components
·         On the left panel click on Charts
·         In properties give the following
o   Name: sales_chart
o   Datasource: get_sales_performance
o   Width: 610
o   Height:140
o   HtmlObject : sales_chart
o   SeriesInRows : False
·         I will show you how to work with Advanced Properties in the upcoming posts on
CDE.
Now save your dashboard and Preview
The preview will look like as follows
That’s it.
This artical is for developing simple dashboard.
In my next article I’m going to make this dashboard dynamic with parameters and will make the dashboard better look and feel.
Thanks,

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.

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.

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]