The easiest way to layout a report in horizontal direction is to use a crosstab *. However, since crosstab needs columns, rows, and measures to construct the data structure and make the calculation, users may need to reconstruct their report data content to meet the requirement.
Suppose we have a report requirement as the following:
Report CSV data:
"P", "C" "Bob", 1 "Bob", 2 "Bob", 3 "Carol", 1 "Carol", 2
Report layout requirement:
To make this work with crosstab, we need to provide column information in the report data structure for crosstab to categorized the data and make the calculation. The approach is to sequentialize the user's data and synchronize those data among different users ("Bob" and "Carol").
The modified report CSV data:
"P", "C","-" "Bob", 1,"A" "Bob", 2,"B" "Bob", 3,"C" "Carol", 1,"A" "Carol", 2,"B"
With the change, we can construct a crosstab report, then manually remove tally information and column display from the crosstab to make the final cut.
Report design template:
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928 -->
<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="sample report" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="sampleData4William-crosstab"/>
<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet/Fourth sheet/Fifth sheet"/>
<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>
<style name="Crosstab 1_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 1_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 1_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 1_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="csv">
<![CDATA[]]>
</queryString>
<field name="P" class="java.lang.String"/>
<field name="C" class="java.lang.Integer"/>
<field name="-" class="java.lang.String"/>
<summary>
<band height="234">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<crosstab>
<reportElement positionType="Float" x="0" y="70" width="570" height="20" uuid="06aa263f-8e83-464d-8994-6fd92739f688">
<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"/>
<property name="com.jaspersoft.studio.unit.x" value="px"/>
</reportElement>
<crosstabDataset>
<dataset resetType="Report"/>
</crosstabDataset>
<rowGroup name="P" width="60" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{P}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="67a80877-17f0-488c-8792-27982f454074"/>
<textFieldExpression><![CDATA[$V{P}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab_CT"/>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="-" height="0" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{-}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CT"/>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="C_MEASURE" class="java.lang.Integer">
<measureExpression><![CDATA[$F{C}]]></measureExpression>
</measure>
<crosstabCell width="60" height="20">
<cellContents mode="Opaque" style="Crosstab_CD">
<textField isBlankWhenNull="true">
<reportElement x="0" y="0" width="60" height="20" uuid="f4fecb82-5612-4011-b0b3-1738982f2625"/>
<textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="0" height="20" columnTotalGroup="-">
<cellContents mode="Opaque" style="Crosstab_CT">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="0" rowTotalGroup="P">
<cellContents mode="Opaque" style="Crosstab_CT"/>
</crosstabCell>
<crosstabCell width="0" height="0" rowTotalGroup="P" columnTotalGroup="-">
<cellContents mode="Opaque" style="Crosstab_CT">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</cellContents>
</crosstabCell>
</crosstab>
<crosstab>
<reportElement x="0" y="0" width="570" height="60" uuid="6fa523c9-19ae-4065-97d3-bc4aa3917715">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
<property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab 1_CH"/>
<property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab 1_CG"/>
<property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab 1_CT"/>
<property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab 1_CD"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<crosstabDataset>
<dataset resetType="Report"/>
</crosstabDataset>
<rowGroup name="P" width="60" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{P}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab 1_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="6a57bb58-af0c-40d3-b7d2-316f24f14267"/>
<textFieldExpression><![CDATA[$V{P}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab 1_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="ba16acc2-19fc-498f-907f-c938bc381208"/>
<text><![CDATA[Total P]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="-" height="20" totalPosition="End">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{-}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab 1_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="6a2b4024-dfee-4ecb-8c10-9ee992fbc476"/>
<textFieldExpression><![CDATA[$V{-}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Opaque" style="Crosstab 1_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7abb08b3-b43f-4bc2-bd00-0e6265b7c144"/>
<text><![CDATA[Total -]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="C_MEASURE" class="java.lang.Integer">
<measureExpression><![CDATA[$F{C}]]></measureExpression>
</measure>
<crosstabCell width="60" height="20">
<cellContents mode="Opaque" style="Crosstab 1_CD">
<textField isBlankWhenNull="true">
<reportElement x="0" y="0" width="60" height="20" uuid="4c0f667c-7bde-421b-9f38-d8e9b13c8714"/>
<textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" columnTotalGroup="-">
<cellContents mode="Opaque" style="Crosstab 1_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="c3cc795a-9bf4-4205-89df-122e24360ec0"/>
<textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="P">
<cellContents mode="Opaque" style="Crosstab 1_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="a187a42f-4aaf-4c85-a6bd-a3ddd2dfde5d"/>
<textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="P" columnTotalGroup="-">
<cellContents mode="Opaque" style="Crosstab 1_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="764b5862-1264-402e-8f72-a97a62c1e3e0"/>
<textFieldExpression><![CDATA[$V{C_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>
Report output:
A crosstab is a table showing the relationship between two or more variables and in general is for quantitative data analysis. Merely laying out report data in a certain direction, such as the use case in this sample, is not the best choice to use crosstab with JasperReports engine (JRL).
This sample report only demonstrates how this can be achieved using the existing product feature but as users may discover the approach is convoluted and require manual trial and error modification to achieve the design goal. I do not recommend this approach but to use other methods to meet the design requirement.
===========================================================================
TTC-20210508
Recommended Comments
There are no comments to display.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now