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


Donate : Link

Medium Blog : 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>

2 thoughts on “JasperReports Tutorial | #3 | Crosstab in Jasper Report | Code Factory”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s