jasper-reportscrosstab
How to prevent joining values in Row Group (column) of Crosstab?
Is there a way to unmerge cells with the same value in crosstab?
The problem is that same valued cells are getting merged.
I want the value to be repeated and have cell borders. For example, in a row group, if i have 2 columns, col1 with values 'abc' and col2 with values 1,2,3 and if there are 3 rows, the col1 is one big cell with 'abc' printed only once.
This is what I don't want and instead want it to be printed 3 times.
This is presentation I required:
Currently I have:
<?xml version="1.0" encoding="UTF-8"?>
<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="Blank_A4_1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a53b72b8-0dda-468b-bffd-3e28d792a2d6">
<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>
<![CDATA[select * from test]]>
</queryString>
<field name="col1" class="java.lang.String"/>
<field name="col2" class="java.lang.Integer"/>
<field name="col3" class="java.lang.String"/>
<field name="col4" class="java.lang.Integer"/>
<field name="col5" class="java.lang.Integer"/>
<summary>
<band height="214" splitType="Stretch">
<crosstab>
<reportElement x="0" y="0" width="555" height="214" uuid="a8663f8c-217c-4ef3-ab2c-a11120aea80e"/>
<rowGroup name="col11" width="60">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{col1}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="6e0cb2ba-ea94-4b5e-a774-3092f6b0bd06"/>
<textFieldExpression><![CDATA[$V{col11}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents>
<staticText>
<reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="4787314d-3cb1-4f29-99d4-1443453e5d1b"/>
<text><![CDATA[Total col11]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<rowGroup name="col21" width="60">
<bucket class="java.lang.Integer">
<bucketExpression><![CDATA[$F{col2}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="bf3d5e77-12a9-44cc-94d1-3f2a317f32d1"/>
<textFieldExpression><![CDATA[$V{col21}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents>
<staticText>
<reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="c21bf242-edbb-4533-9d7d-7120282703a5"/>
<text><![CDATA[Total col21]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="col31" height="20">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{col3}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
<textField>
<reportElement x="0" y="0" width="120" height="20" uuid="320dded3-882b-4e8d-a821-ce94996a72d2">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<textFieldExpression><![CDATA[$V{col31}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents>
<staticText>
<reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="e78a53ad-4437-41d1-8cea-ab1a2fdf09d3"/>
<text><![CDATA[Total col31]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="col4_MEASURE1" class="java.lang.Integer" calculation="Count">
<measureExpression><![CDATA[$F{col4}]]></measureExpression>
</measure>
<measure name="col5_MEASURE1" class="java.lang.Integer" calculation="Count">
<measureExpression><![CDATA[$F{col5}]]></measureExpression>
</measure>
<crosstabCell width="120" height="20">
<cellContents mode="Opaque" style="Crosstab_CD">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="ddc2801e-7492-4848-b7f7-422d7267cadb"/>
<box>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
<rightPen lineWidth="0.5"/>
</box>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="60" y="0" width="60" height="20" uuid="38136ac4-94aa-46ef-91ca-b32ac1d19aba">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
<rightPen lineWidth="0.5"/>
</box>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" columnTotalGroup="col31">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="0b620468-123a-434e-8a87-be8125b86470"/>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="d3c3da1b-7c66-48e5-8328-9c775e036229"/>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="col11">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="08db97ef-0f91-4301-a552-2eec171c2b91"/>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="2fa36822-c633-4954-b6ad-bb2633dd827b"/>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="col11" columnTotalGroup="col31">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="a1545b3f-ac13-4e97-8720-3b526af18207"/>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="02c1d1d7-8b20-461e-8bd9-b2e4ceff02ed"/>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="col21">
<cellContents mode="Opaque" style="Crosstab_CG">
<textField>
<reportElement x="0" y="0" width="60" height="10" uuid="94b61c57-3b18-4134-a48f-36394cf8ac28"/>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="10" width="60" height="10" uuid="b8b84ab6-a800-427e-8738-40dc17f8b7ae"/>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="col21" columnTotalGroup="col31">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="e34172fb-29af-43aa-af6b-50bb63949236"/>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="232ba479-f365-4fb8-8402-4a9d50f79c6c"/>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>
Solution
The col1 and col2 in your sample are Rows Groups. This name (Row Group) tell us that JasperReports engine unions all non unique values in groups in case using Crosstab component. That is why values should be always ordered for grouping data right (as shown at image below).
The data in Row Groups will be grouped by JR engine and one group is drawing as one cell.
Does this mean that the problem has no solution? - No. Let's solve it!
Looks like you want "to join" col1 and col2 columns values. This means that the value of col1 Row Group can be added to the "column" of col2 Row Group.
Example
Datasource
It is enough to test sample with simple csv datasource.
col1,col2,col3,col4,col5
abcd,1,b,1c,434
abcd,2,c,2c,333
abcd,3,c,4c,333
abcd,3,c,4c,333
abcd,5,c,4c,333
abcd,3,e,3g,909
The name of data adapter for this datasource in the example below is values.csv. The first line from the file is skipped - it is contains the column's name.
Report template
We should set the width of first column (col1 Row Group) as zero and increase the width of second column (col2 Row Group) to place additionally value of col1. In this case the JR engine draws both both values for each unique col2 value. We still need the first column because we need value of col1 (the bucket for col1 Row Group).
The design in Jaspersoft Studio (JSS) looks like:
The jrxml file:
<?xml version="1.0" encoding="UTF-8"?>
<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. Merged values" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="values.csv"/>
<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_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>
<field name="col1" class="java.lang.String"/>
<field name="col2" class="java.lang.Integer"/>
<field name="col3" class="java.lang.String"/>
<field name="col4" class="java.lang.Integer"/>
<field name="col5" class="java.lang.Integer"/>
<summary>
<band height="214" splitType="Stretch">
<crosstab>
<reportElement x="0" y="0" width="555" height="214"/>
<crosstabHeaderCell>
<cellContents style="Crosstab_CH">
<staticText>
<reportElement x="0" y="0" width="60" height="20"/>
<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>
<text><![CDATA[Col1]]></text>
</staticText>
<staticText>
<reportElement x="60" y="0" width="60" height="20"/>
<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>
<text><![CDATA[Col2]]></text>
</staticText>
</cellContents>
</crosstabHeaderCell>
<rowGroup name="col11" width="0">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{col1}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH"/>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents/>
</crosstabTotalRowHeader>
</rowGroup>
<rowGroup name="col21" width="120">
<bucket class="java.lang.Integer">
<bucketExpression><![CDATA[$F{col2}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CD">
<textField>
<reportElement x="0" y="0" width="60" height="20"/>
<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{col11}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="60" y="0" width="60" height="20"/>
<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{col21}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents/>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="col31" height="20">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{col3}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="120" height="20"/>
<textFieldExpression><![CDATA[$V{col31}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents/>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="col4_MEASURE1" class="java.lang.Integer" calculation="Count">
<measureExpression><![CDATA[$F{col4}]]></measureExpression>
</measure>
<measure name="col5_MEASURE1" class="java.lang.Integer" calculation="Count">
<measureExpression><![CDATA[$F{col5}]]></measureExpression>
</measure>
<crosstabCell width="120" height="20">
<cellContents mode="Opaque" style="Crosstab_CD">
<textField>
<reportElement x="0" y="0" width="60" height="20"/>
<box>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
<rightPen lineWidth="0.5"/>
</box>
<textFieldExpression><![CDATA[$V{col4_MEASURE1}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="60" y="0" width="60" height="20"/>
<box>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
<rightPen lineWidth="0.5"/>
</box>
<textFieldExpression><![CDATA[$V{col5_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>
Output result
The result in JSS:
- jasperreports: vertical arrangement in jr:list doesn't work and results in overlapping elements
- Jasper Reports throws Exception when running JAR but working PERFECTLY in IntelliJ IDE
- Filling a Jasper Report but getting nulls
- The subreport repeats itself in Detail band
- JasperReport: How to get date from the "oracle.sql.TIMESTAMP' field?
- jrxml uuid attribute not allowed with JasperReports 6.21.0
- cvc-complex-type.3.2.2 attribute 'uuid' is not allowed to appear in element 'jasperreport'
- Jasper soft report expression to get comma separated string from object array parameter
- Extra blank page at the end of the report JasperReports
- How to display in Jasper BLOB Image?
- How to get date six month before current date in single line in Java?
- Jasper Report : Errors were encountered when compiling report expressions class file On server
- How to use date range type parameter as normal java.util.date in jasper
- JsonQLDataSource cannot be resolved to a type
- How to print page number to other language in Jasper Report by Jaspersoft Studio?
- jasper text field getting truncated
- How to show column at Crosstab even the data is absent
- Pass Fields to Subreports in Jasper Reports
- How to add table of content in Jasper report
- Grouping in Jasper Reports
- Jasper Reports Show "Page X of Y" using a single text field
- JasperReports (6.20.1) dependency not satisfied in Java project : Failure to find com.github.librepdf:openpdf:jar:1.3.30.jaspersoft.1
- How can I use parameter for lineColor in JasperReports?
- Jasper Reports Excel Output Problems
- Jasper Reports can't locate "package net.sf.jasperreports.engine does not exist" while running jar but everything working fine in IDE
- Enable Jasper Studio Webservice plugin
- Jaspersoft studio adjust position of fields relative to height of other fields
- How to add DynamicJasper report to Jasper Report as sub-report Dynamically
- Jasper Reports: JRBeanCollectionDataSource cannot be resolved to a type
- How to export to multiple sheets in excel from jasper report