We have a requirment to develop a multilevel crosstab using Jasper reports. Sample expected crosstab report is attached FYR. Screenshot Name : Expected_Crosstab_Sample.png
We were able to achieve the Multi level crosstab as shown in the screenshot :
Following are the issues that needs to be adddressed to achieve the expected crosstab report :
1). Set the Header for each column as shown in the screenshot : Expected_Crosstab_Sample.png 2). Need to increase the width of the Row group,Column group,Measures as per the data size, which currently is not being allowed.
Attaching the jrxml file FYR. Jaspersoft studio version : 7.1.0`
<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="CrossTabTest" pageWidth="4941" pageHeight="3593" columnWidth="4941" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true" uuid="5f1ed15b-c6db-4b94-bb34-1bfd401a9307">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="MSSQLAdapter"/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<subDataset name="CrossDS" uuid="ef4d1658-10fd-47a5-9f31-3aa5754774fa">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="MSSQLAdapter"/>
<queryString language="SQL">
<![CDATA[SELECT * FROM TB_P2P_CROSSTAB_TEST]]>
</queryString>
<field name="PRODUCTID" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="PRODUCTID"/>
</field>
<field name="REGION" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="REGION"/>
</field>
<field name="AREA" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="AREA"/>
</field>
<field name="SALES_MONTH" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="SALES_MONTH"/>
</field>
<field name="SALES" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="SALES"/>
</field>
<field name="UNIT_SALES" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="UNIT_SALES"/>
</field>
<field name="COST" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="COST"/>
</field>
</subDataset>
<subDataset name="TestDS" uuid="f92f93e5-5fd5-4fd7-8a00-e1b1cf976652">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="MSSQLAdapter"/>
<queryString language="SQL">
<![CDATA[Select 'USA'AS REGION,'CA' AS AREA, 'JAN' AS SALES_MONTH, 'Yes' AS SALES, '5' AS UNIT_SALES, '67' AS COST
UNION Select 'CANADA'AS REGION,'CA' AS AREA, 'JAN' AS SALES_MONTH, 'Yes' AS SALES, '6' AS UNIT_SALES, '23' AS COST
UNION Select 'CANADA'AS REGION,'MU' AS AREA, 'JAN' AS SALES_MONTH, 'No' AS SALES, '2' AS UNIT_SALES, '19' AS COST
UNION Select 'CANADA'AS REGION,'CA' AS AREA, 'FEB' AS SALES_MONTH, 'No' AS SALES, '3' AS UNIT_SALES, '55' AS COST
UNION Select 'CANADA'AS REGION,'MU' AS AREA, 'FEB' AS SALES_MONTH, 'Yes' AS SALES, '7' AS UNIT_SALES, '21' AS COST
UNION Select 'USA'AS REGION,'MU' AS AREA, 'JAN' AS SALES_MONTH, 'No' AS SALES, '9' AS UNIT_SALES, '49' AS COST
UNION Select 'USA'AS REGION,'VO' AS AREA, 'FEB' AS SALES_MONTH, 'YES' AS SALES, '1' AS UNIT_SALES, '81' AS COST
UNION Select 'USA'AS REGION,'CA' AS AREA, 'MAR' AS SALES_MONTH, 'No' AS SALES, '4' AS UNIT_SALES, '87' AS COST
UNION Select 'USA'AS REGION,'VO' AS AREA, 'JAN' AS SALES_MONTH, 'YES' AS SALES, '8' AS UNIT_SALES, '43' AS COST]]>
</queryString>
<field name="REGION" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="REGION"/>
</field>
<field name="AREA" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="AREA"/>
</field>
<field name="SALES_MONTH" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="SALES_MONTH"/>
</field>
<field name="SALES" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="SALES"/>
</field>
<field name="UNIT_SALES" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="UNIT_SALES"/>
</field>
<field name="COST" class="java.lang.String">
<property name="com.jaspersoft.studio.field.label" value="COST"/>
</field>
</subDataset>
<queryString language="SQL">
<![CDATA[select getdate() as dummy]]>
</queryString>
<field name="dummy" class="java.sql.Timestamp">
<property name="com.jaspersoft.studio.field.label" value="dummy"/>
</field>
<detail>
<band height="638" splitType="Stretch">
<crosstab>
<reportElement x="13" y="9" width="187" height="91" uuid="3e2ea548-5f33-44e6-8a11-9c5fcbcb30fe">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
<property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab 11_CH"/>
<property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab 11_CG"/>
<property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab 11_CT"/>
<property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab 11_CD"/>
</reportElement>
<crosstabDataset>
<dataset>
<datasetRun subDataset="TestDS" uuid="0fb50808-97a5-437d-8857-10a4533bb70c">
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
</dataset>
</crosstabDataset>
<rowGroup name="REGION" width="60">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{REGION}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab 11_CH">
<textField>
<reportElement x="0" y="0" width="60" height="60" uuid="280b025e-4f99-490c-b5e9-72f8b5c44066"/>
<textFieldExpression><![CDATA[$V{REGION}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab 11_CT">
<staticText>
<reportElement x="0" y="0" width="120" height="60" forecolor="#FFFFFF" uuid="c9ece152-35d3-40e8-bb13-ae04b67366e9"/>
<text><![CDATA[Total REGION]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<rowGroup name="AREA" width="60">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{AREA}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab 11_CH">
<textField>
<reportElement x="0" y="0" width="60" height="60" uuid="ee8823a5-bc1c-4afc-a809-055e50d1cb29"/>
<textFieldExpression><![CDATA[$V{AREA}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab 11_CG">
<staticText>
<reportElement x="0" y="0" width="60" height="60" uuid="0f3f4689-e048-4be4-9cab-f6f73663f8a8"/>
<text><![CDATA[Total AREA]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="SALES_MONTH" height="20">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{SALES_MONTH}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab 11_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="1d5e7d7f-7362-4a50-b106-8a04fb3b9bf7"/>
<textFieldExpression><![CDATA[$V{SALES_MONTH}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Opaque" style="Crosstab 11_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="37f497fa-8b42-44fe-9f5d-39f68a7064bf"/>
<text><![CDATA[Total SALES_MONTH]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="SALES_MEASURE" class="java.lang.String">
<measureExpression><![CDATA[$F{SALES}]]></measureExpression>
</measure>
<measure name="UNIT_SALES_MEASURE" class="java.lang.String" calculation="Count">
<measureExpression><![CDATA[$F{UNIT_SALES}]]></measureExpression>
</measure>
<measure name="COST_MEASURE" class="java.lang.String" calculation="Count">
<measureExpression><![CDATA[$F{COST}]]></measureExpression>
</measure>
<crosstabCell width="60" height="60">
<cellContents mode="Opaque" style="Crosstab 11_CD">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/>
<textField isBlankWhenNull="true">
<reportElement x="0" y="0" width="20" height="60" uuid="d39b9b59-a137-489c-8ea3-58d43df6c10a"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement x="20" y="0" width="20" height="60" uuid="566577f4-8224-4887-90f7-4e8b18707ad7"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement x="40" y="0" width="20" height="60" uuid="db214393-5cdf-448f-8965-d793131b05d6"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="60" columnTotalGroup="SALES_MONTH">
<cellContents mode="Opaque" style="Crosstab 11_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="2928e108-441c-49c9-9e0d-abc4a3446a5e"/>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="60" height="20" forecolor="#FFFFFF" uuid="a9fd31cc-ceb8-450d-b854-b4892568aaff"/>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="60" height="20" forecolor="#FFFFFF" uuid="02eeb874-2276-4811-acfc-5c8c5d1c06e2"/>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="60" rowTotalGroup="REGION">
<cellContents mode="Opaque" style="Crosstab 11_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="85403e91-eb99-4220-abc6-f6129b9f62d4"/>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="60" height="20" forecolor="#FFFFFF" uuid="d3e0bcb4-835c-47ad-b690-c6697dc72c3a"/>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="60" height="20" forecolor="#FFFFFF" uuid="2cb3478e-f3b3-4fe5-8d0e-9919632d680f"/>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="60" rowTotalGroup="REGION" columnTotalGroup="SALES_MONTH">
<cellContents mode="Opaque" style="Crosstab 11_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="255bc8db-ff72-4f5f-b341-3c4238b386ab"/>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="60" height="20" forecolor="#FFFFFF" uuid="5ea8a836-aa42-492a-90fa-de8cd1bdd965"/>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="60" height="20" forecolor="#FFFFFF" uuid="46b7d522-ea4f-4fde-b6a0-42f836ed9fa1"/>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="60" rowTotalGroup="AREA">
<cellContents mode="Opaque" style="Crosstab 11_CG">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="8af15c7d-a948-4791-a9ca-b3a55b854622"/>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="60" height="20" uuid="47657745-4211-4ed9-9f38-263cd8f5f5c9"/>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="60" height="20" uuid="43cfcb74-5a26-4891-a160-2ec01db09643"/>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="60" rowTotalGroup="AREA" columnTotalGroup="SALES_MONTH">
<cellContents mode="Opaque" style="Crosstab 11_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="187053c3-64da-4349-90f0-bafeee4f20ec"/>
<textFieldExpression><![CDATA[$V{SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="60" height="20" forecolor="#FFFFFF" uuid="58845221-7061-4e0f-a10b-f0f85cfe2bd7"/>
<textFieldExpression><![CDATA[$V{UNIT_SALES_MEASURE}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="60" height="20" forecolor="#FFFFFF" uuid="7d150284-6590-44b2-8f78-c08e5d13e941"/>
<textFieldExpression><![CDATA[$V{COST_MEASURE}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</detail>
</jasperReport>
