JasperReports Tutorial | #3 | Crosstab in Jasper Report | Code Factory

Donate : Link

Reference Link : Link

Applications : Link

Download Code and Jars : Link1 & Link2

File : crossTab.jrxml

<?xml version=”1.0" encoding=”UTF-8"?>
<! — Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1 →
<! — 2017–07–15T19:35:56 →
<jasperReport xmlns=”http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=”http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name=”crossTab” pageWidth=”595" pageHeight=”842" columnWidth=”555" leftMargin=”20" rightMargin=”20" topMargin=”20" bottomMargin=”20" uuid=”b0537bac-ad5b-45da-b8c2–5dc51e1e6dc3">
<property name=”com.jaspersoft.studio.data.sql.tables” value=”” />
<property name=”com.jaspersoft.studio.data.defaultdataadapter” value=”Sample DB” />
<style name=”Crosstab_CH” mode=”Opaque” backcolor=”#F0F8FF”>
<box> <pen lineWidth=”0.5" lineColor=”#000000"/> <topPen lineWidth=”0.5" lineColor=”#000000"/> <leftPen lineWidth=”0.5" lineColor=”#000000"/> <bottomPen lineWidth=”0.5" lineColor=”#000000"/> <rightPen lineWidth=”0.5" lineColor=”#000000"/> </box>
</style>
<style name=”Crosstab_CG” mode=”Opaque” backcolor=”#BFE1FF”>
<box> <pen lineWidth=”0.5" lineColor=”#000000"/> <topPen lineWidth=”0.5" lineColor=”#000000"/> <leftPen lineWidth=”0.5" lineColor=”#000000"/> <bottomPen lineWidth=”0.5" lineColor=”#000000"/> <rightPen lineWidth=”0.5" lineColor=”#000000"/> </box>
</style>
<style name=”Crosstab_CT” mode=”Opaque” backcolor=”#005FB3">
<box> <pen lineWidth=”0.5" lineColor=”#000000"/> <topPen lineWidth=”0.5" lineColor=”#000000"/> <leftPen lineWidth=”0.5" lineColor=”#000000"/> <bottomPen lineWidth=”0.5" lineColor=”#000000"/> <rightPen lineWidth=”0.5" lineColor=”#000000"/> </box>
</style>
<style name=”Crosstab_CD” mode=”Opaque” backcolor=”#FFFFFF”>
<box> <pen lineWidth=”0.5" lineColor=”#000000"/> <topPen lineWidth=”0.5" lineColor=”#000000"/> <leftPen lineWidth=”0.5" lineColor=”#000000"/> <bottomPen lineWidth=”0.5" lineColor=”#000000"/> <rightPen lineWidth=”0.5" lineColor=”#000000"/> </box>
</style>
<queryString language=”SQL”>
<![CDATA[select * from loan]]>
</queryString>
<field name=”branch” class=”java.lang.String” />
<field name=”loan_type” class=”java.lang.String” />
<field name=”financial_year” class=”java.lang.String” />
<field name=”disbursement_amt” class=”java.math.BigDecimal” />
<background>
<band splitType=”Stretch” />
</background>
<title>
<band height=”50" splitType=”Stretch” />
</title>
<summary>
<band height=”263" splitType=”Stretch”>
<crosstab>
<reportElement x=”60" y=”30" width=”440" height=”117" uuid=”69565def-59c3–48c4–927b-02b6dbb4ecbf”>
<property name=”com.jaspersoft.studio.layout” value=”com.jaspersoft.studio.editor.layout.VerticalRowLayout” />
<property name=”com.jaspersoft.studio.crosstab.style.header” value=”Crosstab_CH” />
<property name=”com.jaspersoft.studio.crosstab.style.group” value=”Crosstab_CG” />
<property name=”com.jaspersoft.studio.crosstab.style.total” value=”Crosstab_CT” />
<property name=”com.jaspersoft.studio.crosstab.style.detail” value=”Crosstab_CD” />
</reportElement>
<rowGroup name=”branch” width=”60" totalPosition=”End”>
<bucket class=”java.lang.String”>
<bucketExpression>
<![CDATA[$F{branch}]]>
</bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode=”Opaque” style=”Crosstab_CH”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”40" uuid=”cb2497a6-de07–43fc-b75d-721aa7ce9c1b” />
<textFieldExpression>
<![CDATA[$V{branch}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<staticText>
<reportElement x=”0" y=”0" width=”120" height=”20" forecolor=”#FFFFFF” uuid=”0746d2c6–109a-4481-b78d-e99338d6e695" />
<text>
<![CDATA[Total branch]]>
</text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<rowGroup name=”loan_type” width=”60" totalPosition=”End”>
<bucket class=”java.lang.String”>
<bucketExpression>
<![CDATA[$F{loan_type}]]>
</bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode=”Opaque” style=”Crosstab_CH”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" uuid=”f1e277e0–62eb-4393-aa92-b0ab9a1c749b” />
<textFieldExpression>
<![CDATA[$V{loan_type}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode=”Opaque” style=”Crosstab_CG”>
<staticText>
<reportElement x=”0" y=”0" width=”60" height=”20" uuid=”76b9c952-a4b7–4001-a89a-8957b323da5a” />
<text>
<![CDATA[Total loan_type]]>
</text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name=”financial_year” height=”20" totalPosition=”End”>
<bucket class=”java.lang.String”>
<bucketExpression>
<![CDATA[$F{financial_year}]]>
</bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode=”Opaque” style=”Crosstab_CH”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" uuid=”3e685b61–6920–4002–9a38–27101e13a19a” />
<textFieldExpression>
<![CDATA[$V{financial_year}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<staticText>
<reportElement x=”0" y=”0" width=”60" height=”20" forecolor=”#FFFFFF” uuid=”145eaeca-9020–4d54-a71d-600b2965d17d” />
<text>
<![CDATA[Total financial_year]]>
</text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name=”disbursement_amt_MEASURE” class=”java.lang.Integer” calculation=”Sum”>
<measureExpression>
<![CDATA[$F{disbursement_amt}]]>
</measureExpression>
</measure>
<crosstabCell width=”60" height=”20">
<cellContents mode=”Opaque” style=”Crosstab_CD”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" uuid=”51ecf9db-ab5d-444d-9222-ce9c0def4080" />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width=”60" height=”20" columnTotalGroup=”financial_year”>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" forecolor=”#FFFFFF” uuid=”4181deae-fd98–43dd-a074–04a18a136495" />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width=”60" height=”20" rowTotalGroup=”branch”>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" forecolor=”#FFFFFF” uuid=”a0c86934-b76e-4842–878f-278d7e9fef7c” />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width=”60" height=”20" rowTotalGroup=”branch” columnTotalGroup=”financial_year”>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" forecolor=”#FFFFFF” uuid=”72ae47a8-cb8a-4b74-a573-a31036860bf9" />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width=”60" height=”20" rowTotalGroup=”loan_type”>
<cellContents mode=”Opaque” style=”Crosstab_CG”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" uuid=”9488d395–1c63–4aa9–95a2–70f0fbe71589" />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width=”60" height=”20" rowTotalGroup=”loan_type” columnTotalGroup=”financial_year”>
<cellContents mode=”Opaque” style=”Crosstab_CT”>
<textField>
<reportElement x=”0" y=”0" width=”60" height=”20" forecolor=”#FFFFFF” uuid=”e1cae68a-1a6c-4da6-bd52–248d88c4fd76" />
<textFieldExpression>
<![CDATA[$V{disbursement_amt_MEASURE}]]>
</textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>

Call JasperReport from Jsp :

File : index.jsp

<%@page import=”java.io.OutputStream”%>
<%@ page contentType=”application/pdf”%>
<%@ page trimDirectiveWhitespaces=”true”%>
<%@page import=”java.util.HashMap”%>
<%@page import=”java.util.Map”%>
<%@page import=”net.sf.jasperreports.engine.design.JRDesignQuery”%>
<%@page import=”net.sf.jasperreports.engine.xml.JRXmlLoader”%>
<%@page import=”net.sf.jasperreports.engine.design.JasperDesign”%>
<%@page import=”net.sf.jasperreports.view.JasperViewer”%>
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd">
<html>
<%@ page import=”net.sf.jasperreports.engine.*”%>
<%@ page import=”java.io.File”%>
<%@ page import=”java.io.FileInputStream”%>
<%@ page import=”java.io.FileNotFoundException”%>
<%@ page import=”java.io.InputStream”%>
<%@ page import=”java.sql.Connection”%>
<%@ page import=”java.sql.DriverManager”%>
<%@ page import=”java.sql.SQLException”%>
<head>
<title>Image Example</title>
</head>
<body>
<%
Connection conn = null;
try {
Class.forName(“com.mysql.jdbc.Driver”);
conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test1”, “root”, “root”);
// paste location here
String report = “C:/Users/Rahul8/JaspersoftWorkspace/MyReports/crossTab.jrxml”;
JasperReport jasperreport = JasperCompileManager.compileReport(report);
Map<String, Object> data = new HashMap<String, Object>();
data.put(“Image”, this.getServletContext().getRealPath(“/”) + “/images/asteroids.jpg”);
//data.put(“Image”, this.getServletContext().getRealPath(“/”)+”/images/green-goblin.png”);
JasperPrint jp = JasperFillManager.fillReport(jasperreport, data, conn);
JasperExportManager.exportReportToPdfStream(jp, response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (Exception e) {
out.println(e);
}
%>
</body>
</html>