mickqg
-
Posts
3 -
Joined
-
Last visited
Content Type
Profiles
Forum
Events
Featured Visualizations
Knowledge Base
Documentation (PDF Downloads)
Blog
Documentation (Test Area)
Documentation
Dr. Jaspersoft Webinar Series
Downloads
Posts posted by 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]
-
Crosstab null removal
in Products
Posted
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.