Crosstab null removal

0

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 <a href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"" target="_blank">http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"</a> 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>
</td></tr></tbody></table>
mickqg's picture
3
Joined: Oct 5 2010 - 12:38pm
Last seen: 8 years 10 months ago

4 Answers:

0

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
3
Joined: Oct 5 2010 - 12:38pm
Last seen: 8 years 10 months ago
1

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

 

erik.dittert's picture
Joined: Sep 20 2013 - 7:12am
Last seen: 1 week 5 days ago
0

 

Any answer to erik's post?


erik.dittert


CoraKeen's picture
Joined: Jun 22 2015 - 10:04am
Last seen: 9 months 2 weeks ago
0

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

2340patel's picture
Joined: Jan 11 2018 - 8:38pm
Last seen: 2 months 2 weeks ago
Feedback
randomness