Compiler issue with Crosstab

[chauncy313]

 

I have a crosstab report that compiles and previews in iReport 3.1.4.  When I try to compile it in servlet code (using iReport 3.1.4 JAR), it gives an error as follows:

net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 1. Field not found : area_name 2. Field not found : product_line_code at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:262) at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:144) at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:219) at

Below is the XML file.  I changed the names of the bucketing expressions in an attempt to debug, but this report runs without issue in the iReport 3.1.4 IDE I suspected the validation might be looking at the main report dataset, which is why I have the dummy fields there.

 

 

 

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="report name" pageWidth="1200" pageHeight="595" orientation="Landscape" columnWidth="1160" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true"> <style name="Crosstab Data Text" isDefault="false" hAlign="Center"/> <subDataset name="dataset1">  <queryString>   <![CDATA[select  area_name, i.product_line_code + '-' + i.product_line_description product_line_description, i.product_line_code,SUM(list_price_extended_base) dollars, SUM(quantity_shipped) units,count(distinct order_number) orders,SUM(list_price_extended_base)/ count(distinct order_number) average_orderfrom fact_om_shipment fINNER JOIN dim_item i ON f.dim_item_key = i.dim_item_keyINNER JOIN dim_customer_sold_to c ON f.dim_customer_sold_to_key = c.dim_customer_sold_to_keyINNER JOIN dim_effective_date e ON f.dim_effective_date_key = e.dim_effective_date_key INNER JOIN (dim_sales_person s  INNER JOIN HIER_TERRITORY ht on s.territory_name = ht.territory_id)  ON f.dim_sales_person1_key = s.dim_sales_person_key   where e.effective_cal_year = 2009 and f.list_price_base <> 0group by  area_name, i.product_line_description, i.product_line_codeorder by 3]]>  </queryString>  <field name="area_name" class="java.lang.String">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="product_line_description" class="java.lang.String">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="product_line_code" class="java.lang.String">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="dollars" class="java.math.BigDecimal">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="units" class="java.math.BigDecimal">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="orders" class="java.lang.Integer">   <fieldDescription><![CDATA[]]></fieldDescription>  </field>  <field name="average_order" class="java.math.BigDecimal">   <fieldDescription><![CDATA[]]></fieldDescription>  </field> </subDataset> <queryString>  <![CDATA[select 1 foo, '2' area_name, '3' product_line_description, 4 dollars]]> </queryString> <field name="foo" class="java.lang.Integer">  <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="area_name" class="java.lang.String">  <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="product_line_description" class="java.lang.String">  <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="dollars" class="java.lang.Integer">  <fieldDescription><![CDATA[]]></fieldDescription> </field> <group name="dummy">  <groupExpression><![CDATA["1"]]></groupExpression>  <groupHeader>   <band height="7"/>  </groupHeader>  <groupFooter>   <band height="7"/>  </groupFooter> </group> <background>  <band/> </background> <title>  <band height="32">   <staticText>    <reportElement x="220" y="0" width="240" height="21"/>    <textElement>     <font size="14" isBold="true"/>    </textElement>    <text><![CDATA[2009 Sales By Product Line]]></text>   </staticText>  </band> </title> <detail>  <band height="80">   <crosstab>    <reportElement x="0" y="0" width="977" height="80"/>    <crosstabDataset>     <dataset>      <datasetRun subDataset="dataset1">       <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>      </datasetRun>     </dataset>    </crosstabDataset>    <rowGroup name="area_nameX" width="70" totalPosition="End">     <bucket>      <bucketExpression class="java.lang.String"><![CDATA[$F{area_name}]]></bucketExpression>     </bucket>     <crosstabRowHeader>      <cellContents backcolor="#F0F8FF" 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="20"/>        <textElement>         <font size="8"/>        </textElement>        <textFieldExpression class="java.lang.String"><![CDATA[$V{area_nameX}]]></textFieldExpression>       </textField>      </cellContents>     </crosstabRowHeader>     <crosstabTotalRowHeader>      <cellContents backcolor="#BFE1FF" mode="Opaque">       <box>        <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>       </box>       <staticText>        <reportElement x="0" y="0" width="70" height="22"/>        <textElement textAlignment="Center" verticalAlignment="Middle">         <font size="8"/>        </textElement>        <text><![CDATA[Total]]></text>       </staticText>      </cellContents>     </crosstabTotalRowHeader>    </rowGroup>    <columnGroup name="product_line_description" height="30" totalPosition="End">     <bucket>      <bucketExpression class="java.lang.String"><![CDATA[$F{product_line_description}]]></bucketExpression>     </bucket>     <crosstabColumnHeader>      <cellContents backcolor="#F0F8FF" mode="Opaque">       <box>        <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>       </box>       <textField>        <reportElement style="Crosstab Data Text" x="0" y="0" width="56" height="30"/>        <textElement>         <font size="8"/>        </textElement>        <textFieldExpression class="java.lang.String"><![CDATA[$V{product_line_description}]]></textFieldExpression>       </textField>      </cellContents>     </crosstabColumnHeader>     <crosstabTotalColumnHeader>      <cellContents backcolor="#BFE1FF" mode="Opaque">       <box>        <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>       </box>       <staticText>        <reportElement x="0" y="0" width="55" height="30"/>        <textElement textAlignment="Center" verticalAlignment="Middle">         <font size="8"/>        </textElement>        <text><![CDATA[Total]]></text>       </staticText>      </cellContents>     </crosstabTotalColumnHeader>    </columnGroup>    <measure name="dollarsMeasure" class="java.math.BigDecimal" calculation="Sum">     <measureExpression><![CDATA[$F{dollars}]]></measureExpression>    </measure>    <crosstabCell width="56" height="20">     <cellContents>      <box>       <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>      </box>      <textField pattern="#,##0.00" hyperlinkType="Reference">       <reportElement style="Crosstab Data Text" x="0" y="0" width="56" height="20"/>       <textElement textAlignment="Right">        <font size="8"/>       </textElement>       <textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{dollarsMeasure}]]></textFieldExpression>       <anchorNameExpression><![CDATA["CellDrill"]]></anchorNameExpression>       <hyperlinkReferenceExpression><![CDATA["dummy"]]></hyperlinkReferenceExpression>      </textField>     </cellContents>    </crosstabCell>    <crosstabCell width="56" height="22" rowTotalGroup="area_nameX">     <cellContents backcolor="#BFE1FF" 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="56" height="22"/>       <textElement textAlignment="Right">        <font size="8"/>       </textElement>       <textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{dollarsMeasure}]]></textFieldExpression>      </textField>     </cellContents>    </crosstabCell>    <crosstabCell width="55" height="20" columnTotalGroup="product_line_description">     <cellContents backcolor="#BFE1FF" 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="55" height="20"/>       <textElement textAlignment="Right">        <font size="8"/>       </textElement>       <textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{dollarsMeasure}]]></textFieldExpression>      </textField>     </cellContents>    </crosstabCell>    <crosstabCell width="55" height="22" rowTotalGroup="area_nameX" columnTotalGroup="product_line_description">     <cellContents backcolor="#BFE1FF" 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="55" height="22"/>       <textElement textAlignment="Right">        <font size="8"/>       </textElement>       <textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{dollarsMeasure}]]></textFieldExpression>      </textField>     </cellContents>    </crosstabCell>   </crosstab>  </band> </detail> <summary>  <band height="10"/> </summary></jasperReport>

[chauncy313]

I have a reference type hyperlink on the cell of the crosstab.  I have determined that removing this allows the report to compile, but I would, of course, like to determine a way to solve the issue and still retain the hyperlink.

 

--Scott

[chauncy313]

 

I discovered my issue.  I was adding a field reference to the hyperlink reference expression.  This is (and it makes sense) only legal in a bucket definition.  To reference the value in a cell hyperlink, I think only V$ variable reference is legal.

 

--Scott