Crosstab null removal

[mickqg]

How do i get "null" crosstab value to show as blank?   

ex: (from topic "Crosstab null column/row removal" #73466. thanks gandugamerio)

	Sales	Engineering	null	Sum
Ron	1	0	0	1
Sam	0	0	0	0
null	0	0	0	0
Sum	1	0	0	1

My data function is sum, but all null values show zero.   I check the "blank when null" text field property on the Crosstab field, but it continues to show zero.

Thanks, mickqg.

Code:

<?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="hrs_total_plant_press_cust_sub"              pageWidth="1008" pageHeight="612" orientation="Landscape"              columnWidth="968" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">    <style name="Crosstab Data Text" isDefault="false" hAlign="Center"/>    <parameter name="IN_START_DATE" class="java.lang.String">        <defaultValueExpression><![CDATA["2010-08-29"]]></defaultValueExpression>    </parameter>    <parameter name="IN_END_DATE" class="java.lang.String">        <defaultValueExpression><![CDATA["2010-09-25"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CUSTOMER" class="java.lang.String">        <defaultValueExpression><![CDATA["-1"]]></defaultValueExpression>    </parameter>    <parameter name="IN_TITLE" class="java.lang.String">        <defaultValueExpression><![CDATA["-1"]]></defaultValueExpression>    </parameter>    <parameter name="IN_EQUIPMENT_GROUP" class="java.lang.String">        <defaultValueExpression><![CDATA["65"]]></defaultValueExpression>    </parameter>    <parameter name="IN_MEASUREMENT_ID" class="java.lang.String">        <defaultValueExpression><![CDATA["4"]]></defaultValueExpression>    </parameter>    <parameter name="IN_INCLUDEOVERLAP" class="java.lang.String">        <defaultValueExpression><![CDATA["1"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CONVERT42" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CONVERT32" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CONVERT64" class="java.lang.String">        <defaultValueExpression><![CDATA["1"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CONVERT32IMP" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <parameter name="IN_CUSTOMERBREAK" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <parameter name="IN_SOLDSTATUS_SOLD" class="java.lang.String">        <defaultValueExpression><![CDATA["3"]]></defaultValueExpression>    </parameter>    <parameter name="IN_SOLDSTATUS_TENTATIVE" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <parameter name="IN_SOLDSTATUS_PROJECTED" class="java.lang.String">        <defaultValueExpression><![CDATA["0"]]></defaultValueExpression>    </parameter>    <queryString language="function">        <![CDATA[MFG.fn_get_impressions_by_press ($P{IN_START_DATE}, $P{IN_END_DATE}, $P{IN_CUSTOMER}, $P{IN_TITLE}, $P{IN_EQUIPMENT_GROUP}, $P{IN_MEASUREMENT_ID}, $P{IN_INCLUDEOVERLAP}, $P{IN_CONVERT42}, $P{IN_CONVERT32}, $P{IN_CONVERT64}, $P{IN_CONVERT32IMP}, $P{IN_CUSTOMERBREAK}, $P{IN_SOLDSTATUS_SOLD}, $P{IN_SOLDSTATUS_TENTATIVE}, $P{IN_SOLDSTATUS_PROJECTED})]]>    </queryString>    <field name="JS_EQUIPMENT_GROUP_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="LCC_PLANT_ABBR" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CITY" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="EQUIPMENT_TYPE_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CUSTOMER_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="TITLE_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CYEAR" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CMONTH" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CONV_IMPS" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="EQUIPMENT_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="HOURS" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <variable name="V_HTBM_hours" class="java.math.BigDecimal">        <variableExpression>            <![CDATA[$F{HOURS}.divide( new BigDecimal("3600"), 4, RoundingMode.HALF_UP )]]>        </variableExpression>    </variable>    <variable name="V_PERIOD_BY_PRESS" class="java.lang.String">        <variableExpression>            <![CDATA[$F{CMONTH}.intValue() < 10 ? "0" + $F{CMONTH}.toString()  + " " + $F{CYEAR}.toString().trim().substring(2) : $F{CMONTH}.toString()  + " " + $F{CYEAR}.toString().trim().substring(2)]]>        </variableExpression>    </variable>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band splitType="Stretch"/>    </title>    <pageHeader>        <band splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band splitType="Stretch"/>    </columnHeader>    <detail>        <band splitType="Stretch"/>    </detail>    <columnFooter>        <band splitType="Stretch"/>    </columnFooter>    <pageFooter>        <band splitType="Stretch"/>    </pageFooter>    <summary>        <band height="131" splitType="Stretch">            <textField>                <reportElement x="117" y="13" width="86" height="20"/>                <textElement/>                <textFieldExpression class="java.lang.String">                    <![CDATA["Press"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement x="0" y="13" width="48" height="20"/>                <textElement/>                <textFieldExpression class="java.lang.String">                    <![CDATA["Plant"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement x="203" y="13" width="78" height="20"/>                <textElement/>                <textFieldExpression class="java.lang.String">                    <![CDATA["Customer"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement x="48" y="13" width="69" height="20"/>                <textElement/>                <textFieldExpression class="java.lang.String">                    <![CDATA["Method"]]>                </textFieldExpression>            </textField>            <crosstab>                <reportElement x="0" y="0" width="968" height="131"/>                <rowGroup name="LCC_PLANT_ABBR" width="45" totalPosition="End">                    <bucket>                        <bucketExpression class="java.lang.String">                            <![CDATA[$F{LCC_PLANT_ABBR}]]>                        </bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0"                                               width="45" height="15"/>                                <box leftPadding="2"/>                                <textElement textAlignment="Left"/>                                <textFieldExpression class="java.lang.String">                                    <![CDATA[$V{LCC_PLANT_ABBR}]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents backcolor="#737A80" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <staticText>                                <reportElement x="0" y="0" width="140" height="15" forecolor="#FFFFFF"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <text><![CDATA[Total]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalRowHeader>                </rowGroup>                <rowGroup name="EQUIPMENT_TYPE_NAME" width="70" totalPosition="End">                    <bucket>                        <bucketExpression class="java.lang.String">                            <![CDATA[$F{EQUIPMENT_TYPE_NAME}]]>                        </bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="15"/>                                <box leftPadding="2"/>                                <textElement textAlignment="Left"/>                                <textFieldExpression class="java.lang.String">                                    <![CDATA[$V{EQUIPMENT_TYPE_NAME}]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents backcolor="#E6F3FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                        </cellContents>                    </crosstabTotalRowHeader>                </rowGroup>                <rowGroup name="group1" width="87">                    <bucket>                        <bucketExpression class="java.lang.String">                            <![CDATA[$F{EQUIPMENT_NAME}]]>                        </bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5"/>                                <topPen lineWidth="0.5"/>                                <leftPen lineWidth="0.5"/>                                <bottomPen lineWidth="0.5"/>                                <rightPen lineWidth="0.5"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0"                                                width="87" height="15"/>                                <box leftPadding="2"/>                                <textElement textAlignment="Left"/>                                <textFieldExpression class="java.lang.String">                                    <![CDATA[$V{group1}]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <rowGroup name="group2" width="100">                    <bucket>                        <bucketExpression class="java.lang.String">                            <![CDATA[$F{CUSTOMER_NAME}]]>                        </bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5"/>                                <topPen lineWidth="0.5"/>                                <leftPen lineWidth="0.5"/>                                <bottomPen lineWidth="0.5"/>                                <rightPen lineWidth="0.5"/>                            </box>                            <textField isStretchWithOverflow="true">                                <reportElement style="Crosstab Data Text" x="0" y="0"                                                width="100" height="15"/>                                <box leftPadding="2"/>                                <textElement textAlignment="Left"/>                                <textFieldExpression class="java.lang.String">                                    <![CDATA[$V{group2}]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <columnGroup name="CYEAR" height="15" totalPosition="End">                    <bucket>                        <bucketExpression class="java.math.BigDecimal">                            <![CDATA[$F{CYEAR}]]>                        </bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0"                                               width="50" height="15"/>                                <textElement textAlignment="Right"/>                                <textFieldExpression class="java.math.BigDecimal">                                    <![CDATA[$V{CYEAR}]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents backcolor="#737A80" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <staticText>                                <reportElement x="0" y="0" width="50" height="30" forecolor="#FFFFFF"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <text><![CDATA[Total]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <columnGroup name="V_PERIOD_BY_PRESS" height="15" totalPosition="End">                    <bucket>                        <bucketExpression class="java.lang.String">                            <![CDATA[$V{V_PERIOD_BY_PRESS}]]>                        </bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents backcolor="#C3CFD9" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="15"/>                                <box leftPadding="2"/>                                <textElement textAlignment="Left"/>                                <textFieldExpression class="java.lang.String">                                    <![CDATA[(new java.text.DateFormatSymbols().getShortMonths()[integer.parseInt($V{V_PERIOD_BY_PRESS}.substring(0,2)) - 1]).toUpperCase() + " 20" + $V{V_PERIOD_BY_PRESS}.substring(3,5)]]>                                </textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents backcolor="#E6F3FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <measure name="V_HTBM_hoursMeasure" class="java.math.BigDecimal" calculation="Sum">                    <measureExpression><![CDATA[$V{V_HTBM_hours}]]></measureExpression>                </measure>                <crosstabCell width="50" height="15">                    <cellContents>                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField pattern="" isBlankWhenNull="true">                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="15"/>                            <textElement textAlignment="Right"/>                            <textFieldExpression class="java.math.BigDecimal">                                <![CDATA[$V{V_HTBM_hoursMeasure}]]>                            </textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell height="15" rowTotalGroup="LCC_PLANT_ABBR">                    <cellContents backcolor="#737A80" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField pattern="#,##0.00">                            <reportElement style="Crosstab Data Text" x="0" y="0"                                            width="50" height="15" forecolor="#FFFFFF"/>                            <textElement textAlignment="Right"/>                            <textFieldExpression class="java.math.BigDecimal">                                <![CDATA[$V{V_HTBM_hoursMeasure}]]>                            </textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="50" height="15" columnTotalGroup="CYEAR">                    <cellContents backcolor="#737A80" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField pattern="#,##0.00">                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50"                                           height="15" forecolor="#FFFFFF"/>                            <textElement textAlignment="Right"/>                            <textFieldExpression class="java.math.BigDecimal">                                <![CDATA[$V{V_HTBM_hoursMeasure}]]>                            </textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell height="15" rowTotalGroup="LCC_PLANT_ABBR" columnTotalGroup="CYEAR">                    <cellContents backcolor="#737A80" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField pattern="#,##0.00">                            <reportElement style="Crosstab Data Text" x="0" y="0"                                            width="50" height="15" forecolor="#FFFFFF"/>                            <textElement textAlignment="Right"/>                            <textFieldExpression class="java.math.BigDecimal">                                <![CDATA[$V{V_HTBM_hoursMeasure}]]>                            </textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="0" height="15" columnTotalGroup="V_PERIOD_BY_PRESS">                    <cellContents backcolor="#E6F3FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                    </cellContents>                </crosstabCell>                <crosstabCell width="0" height="15" rowTotalGroup="LCC_PLANT_ABBR"                               columnTotalGroup="V_PERIOD_BY_PRESS">                    <cellContents backcolor="#737A80" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                    </cellContents>                </crosstabCell>                <crosstabCell height="0" rowTotalGroup="EQUIPMENT_TYPE_NAME">                    <cellContents backcolor="#E6F3FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                    </cellContents>                </crosstabCell>                <crosstabCell height="0" rowTotalGroup="EQUIPMENT_TYPE_NAME"                               columnTotalGroup="CYEAR">                    <cellContents backcolor="#737A80" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                    </cellContents>                </crosstabCell>                <crosstabCell width="0" height="0" rowTotalGroup="EQUIPMENT_TYPE_NAME"                               columnTotalGroup="V_PERIOD_BY_PRESS">                    <cellContents backcolor="#E6F3FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                    </cellContents>                </crosstabCell>                <crosstabCell rowTotalGroup="group1">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="group1" columnTotalGroup="CYEAR">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="group1" columnTotalGroup="V_PERIOD_BY_PRESS">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="group2">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="group2" columnTotalGroup="CYEAR">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="group2" columnTotalGroup="V_PERIOD_BY_PRESS">                    <cellContents/>                </crosstabCell>            </crosstab>        </band>    </summary></jasperReport> [/code]

[mickqg]

found my answer in the "Ireport Discussion/Help" forum.

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=74382

Thanks all who contributed.

change my text field expression from  $V{V_HTBM_hoursMeasure}      to       ($V{V_HTBM_hoursMeasure}.intValue() ==0) ? null : $V{V_HTBM_hoursMeasure}

 also had to check "blank when null" in properties.

 

Thanks again.

 

[erik.dittert]

mhhhh i dont get it, i see still a empty column? how can i REMOVE this column look here:

 

http://i.imgur.com/DPqDW99.png

[CoraKeen]

erik.dittert

[2340patel]

any update on this issue https://community.jaspersoft.com/jasperreports-library/issues/4626 for above issue