thotwielder
Reputation: 1707
Jasper report remove/hide empty group
Background: Jaspersoft studio 6.
Have this crosstab:
The dataset for the crosstab:
select 'prod1' as prod, 'mon1' as month, 1 as val
union all select 'prod1' as prod, 'mon2' as month, 1 as val
union all select 'prod2' as prod, 'mon1' as month, 1 as val
union all select 'prod2' as prod, 'mon2' as month, 1 as val
union all select 'prod3' as prod, null as month, null as val
The problem: as you may have noticed, prod3 has no data related to mon1 or mon2. But I will still need it to be showed in the report. So there is the null column being created by Jasper. How can I get rid of the null column? I have tried blank when null or Remove line when blank at various places, but nothing works. Even at some point i can get rid of the null text but the cell is still being rendered so the borders and background colours still show.
And the source file (just a crosstab with one row group and one column group):
<?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="test" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d00fbad1-be1e-4544-b22f-6dca803b6714">
<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_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 'prod1' as prod, 'mon1' as month, 1 as val
union all select 'prod1' as prod, 'mon2' as month, 1 as val
union all select 'prod2' as prod, 'mon1' as month, 1 as val
union all select 'prod2' as prod, 'mon2' as month, 1 as val
union all select 'prod3' as prod, null as month, null as val]]>
</queryString>
<field name="prod" class="java.lang.String"/>
<field name="month" class="java.lang.String"/>
<field name="val" class="java.lang.Integer"/>
<summary>
<band height="229" splitType="Stretch">
<crosstab ignoreWidth="true">
<reportElement x="10" y="14" width="520" height="116" isRemoveLineWhenBlank="true" uuid="bb1bb352-1dab-438b-b2c8-b73286af3a9c">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
</reportElement>
<rowGroup name="prod1" width="60">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{prod}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField>
<reportElement x="0" y="0" width="60" height="20" uuid="d56c7426-8030-4919-8869-86129c230a48"/>
<textFieldExpression><![CDATA[$V{prod1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Opaque" style="Crosstab_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="ef3d9aab-f696-48c4-b95b-6be099951c55"/>
<text><![CDATA[Total prod1]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="month1" height="20">
<bucket class="java.lang.String">
<bucketExpression><![CDATA[$F{month}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CH">
<textField isBlankWhenNull="false">
<reportElement x="0" y="0" width="60" height="20" uuid="f8dca50f-1f98-425b-bc4e-8a9505901477"/>
<textFieldExpression><![CDATA[$V{month1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Opaque" style="Crosstab_CT">
<staticText>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="4960b0b9-4df7-4216-908e-e042ccf2e3a6"/>
<text><![CDATA[Total month1]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>
<measure name="val_MEASURE1" class="java.lang.Integer" calculation="Sum">
<measureExpression><![CDATA[$F{val}]]></measureExpression>
</measure>
<crosstabCell width="60" height="20">
<cellContents mode="Opaque" style="Crosstab_CD">
<textField isBlankWhenNull="false">
<reportElement x="0" y="0" width="60" height="20" uuid="d207dcd9-1db6-4adb-a7ba-1b539fa06993"/>
<textFieldExpression><![CDATA[$V{val_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" columnTotalGroup="month1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="59ff8664-202c-4ec7-966c-93c30b372202"/>
<textFieldExpression><![CDATA[$V{val_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="prod1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="686e7851-9a94-4cbe-aac1-60703fdfc049"/>
<textFieldExpression><![CDATA[$V{val_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="prod1" columnTotalGroup="month1">
<cellContents mode="Opaque" style="Crosstab_CT">
<textField>
<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="acbe85bf-5cea-40d3-9951-cb0de8baa353"/>
<textFieldExpression><![CDATA[$V{val_MEASURE1}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
</crosstab>
</band>
</summary>
</jasperReport>
Upvotes: 3
Views: 2938
Answers (1)
thotwielder
Reputation: 1707
Ok, got this solved. Nothing to do with Jasper (it's a good tool but to be honest it just lacks some features). The solution is simply give prod3 a column group and 0 value. The key is simply do not have null value for any group field so that Jasper can't generate a null group. For this case, just give prod3 value of any existing group member (mon1 or mon2) and 0 value. For a real case, there has to be some sql messing to achieve this, but which is far more easier than hacking with jasper.
select 'prod1' as prod, 'mon1' as month, 1 as val
union all select 'prod1' as prod, 'mon2' as month, 1 as val
union all select 'prod2' as prod, 'mon1' as month, 1 as val
union all select 'prod2' as prod, 'mon2' as month, 1 as val
union all select 'prod3' as prod, 'mon1' as month, 0 as val
Upvotes: 1