Crosstab null removal

Posted on October 6, 2010 at 12:16pm

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>
JasperReports® Library
mickqg's picture
mickqg
31
Joined: Oct 5 2010 - 12:38pm
Last seen: 12 years 8 months ago

4 Answers:

Posted on October 6, 2010 at 12:50pm

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.

 

mickqg's picture
mickqg
31
Joined: Oct 5 2010 - 12:38pm
Last seen: 12 years 8 months ago
Posted on January 27, 2015 at 2:05am

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

 

erik.dittert's picture
erik.dittert
147
Joined: Sep 20 2013 - 7:12am
Last seen: 1 month 6 days ago
Posted on May 25, 2016 at 9:50am

 

Any answer to erik's post?


erik.dittert


CoraKeen's picture
CoraKeen
41
Joined: Jun 22 2015 - 10:04am
Last seen: 2 years 10 months ago
Posted on October 15, 2018 at 2:14pm

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

2340patel's picture
2340patel
464
Joined: Jan 11 2018 - 8:38pm
Last seen: 3 years 11 months ago
Feedback