Jump to content
Changes to the Jaspersoft community edition download ×

Cross tab columnd and row count variables


hanlie

Recommended Posts

Hi,

 

I'm trying to create a cross tab report with networks in the columns and months (including their years) in the rows.

 

When I try to compile the report, I get the following errors:

 

net.sf.jasperreports.engine.JRException: Report design not valid : 1. Variable not found : networkCount 2. Variable not found : networkCount 3. Variable not found : year_monthCount 4. Variable not found : year_monthCount at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:267) at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:144) at net.sf.jasperreports.engine.design.JRDefaultCompiler.compileReport(JRDefaultCompiler.java:105) at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:127) at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:109) at it.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:510) at java.lang.Thread.run(Unknown Source)

 

My jrxml is as follows:

 

Code:
<?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
name="CrossTabReport"
columnCount="1"
printOrder="Vertical"
orientation="Portrait"
pageWidth="595"
pageHeight="842"
columnWidth="555"
columnSpacing="0"
leftMargin="5"
rightMargin="5"
topMargin="30"
bottomMargin="30"
whenNoDataType="NoPages"
isTitleNewPage="false"
isSummaryNewPage="false">
<property name="ireport.scriptlethandling" value="0" />
<property name="ireport.encoding" value="UTF-8" />
<import value="java.util.*" />
<import value="net.sf.jasperreports.engine.*" />
<import value="net.sf.jasperreports.engine.data.*" />

<queryString><![CDATA[sELECT date_part('year',entry.date)||' '||date_part('month', entry.date) as year_month, network.network, COUNT(entry.date) as number_entries
FROM entry, network
WHERE network.network_id = entry.network_id
GROUP BY date_part('year',entry.date), date_part('month',entry.date), network.network
ORDER BY date_part('year',entry.date), date_part('month',entry.date), network.network]]></queryString>

<field name="year_month" class="java.lang.String"/>
<field name="network" class="java.lang.String"/>

<variable name="networkCount" class="java.lang.String" resetType="Report" calculation="Count">
<variableExpression><![CDATA[$F{network}]]></variableExpression>
</variable>
<variable name="year_monthCount" class="java.lang.String" resetType="Report" calculation="Count">
<variableExpression><![CDATA[$F{year_month}]]></variableExpression>
</variable>
<background>
<band height="0" isSplitAllowed="true" >
</band>
</background>
<title>
<band height="0" isSplitAllowed="true" >
</band>
</title>
<pageHeader>
<band height="0" isSplitAllowed="true" >
</band>
</pageHeader>
<columnHeader>
<band height="0" isSplitAllowed="true" >
</band>
</columnHeader>
<detail>
<band height="0" isSplitAllowed="true" >
</band>
</detail>
<columnFooter>
<band height="0" isSplitAllowed="true" >
</band>
</columnFooter>
<pageFooter>
<band height="0" isSplitAllowed="true" >
</band>
</pageFooter>
<summary>
<band height="60" isSplitAllowed="true" >
<crosstab >
<reportElement
x="0"
y="0"
width="782"
height="60"
key="crosstab"
stretchType="RelativeToTallestObject"
isPrintRepeatedValues="false"/>
<crosstabHeaderCell> <cellContents mode="Transparent">
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
</cellContents>
</crosstabHeaderCell> <rowGroup name="year_month" width="50" totalPosition="End">
<bucket>
<bucketExpression class="java.lang.String"><![CDATA[$F{year_month}]]></bucketExpression>
</bucket>
<crosstabRowHeader>
<cellContents mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="0"
y="0"
width="50"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{year_month}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabRowHeader>
<crosstabTotalRowHeader>
<cellContents mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<staticText>
<reportElement
x="0"
y="0"
width="50"
height="20"
key="staticText"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement verticalAlignment="Middle">
<font/>
</textElement>
<text><![CDATA[Month Total]]></text>
</staticText>
</cellContents>
</crosstabTotalRowHeader>
</rowGroup>
<columnGroup name="network" height="20" totalPosition="End">
<bucket>
<bucketExpression class="java.lang.String"><![CDATA[$F{network}]]></bucketExpression>
</bucket>
<crosstabColumnHeader>
<cellContents mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="true" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="0"
y="0"
width="60"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement verticalAlignment="Bottom">
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$V{network}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabColumnHeader>
<crosstabTotalColumnHeader>
<cellContents mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<staticText>
<reportElement
x="0"
y="0"
width="60"
height="20"
key="staticText"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement verticalAlignment="Bottom">
<font/>
</textElement>
<text><![CDATA[Network Total]]></text>
</staticText>
</cellContents>
</crosstabTotalColumnHeader>
</columnGroup>

<measure name="network_totals" class="java.lang.String" calculation="Sum">
<measureExpression><![CDATA[$F{network}]]></measureExpression>
</measure>
<measure name="year_month_totals" class="java.lang.String" calculation="Sum">
<measureExpression><![CDATA[$F{year_month}]]></measureExpression>
</measure>
<crosstabCell width="60" height="20">
<cellContents backcolor="#FFFFFF" mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="5"
y="0"
width="55"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement textAlignment="Left" verticalAlignment="Bottom">
<font/>
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$V{networkCount}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" columnTotalGroup="network">
<cellContents backcolor="#FFFFFF" mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="5"
y="0"
width="55"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement textAlignment="Left" verticalAlignment="Bottom">
<font/>
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$V{networkCount}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="year_month">
<cellContents backcolor="#FFFFFF" mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="5"
y="0"
width="55"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement textAlignment="Left" verticalAlignment="Bottom">
<font/>
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$V{year_monthCount}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<crosstabCell width="60" height="20" rowTotalGroup="year_month" columnTotalGroup="network">
<cellContents backcolor="#FFFFFF" mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="5"
y="0"
width="55"
height="20"
key="textField"/>
<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
<textElement textAlignment="Left" verticalAlignment="Bottom">
<font/>
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$V{year_monthCount}]]></textFieldExpression>
</textField>
</cellContents>
</crosstabCell>
<whenNoDataCell> <cellContents backcolor="#FFFFFF" mode="Transparent">
<box topBorder="Thin" topBorderColor="#000000" leftBorder="Thin" leftBorderColor="#000000" rightBorder="Thin" rightBorderColor="#000000" bottomBorder="Thin" bottomBorderColor="#000000"/>
</cellContents>
</whenNoDataCell> </crosstab>
</band>
</summary>
</jasperReport>

 

 

I used the cross tab sample in the jasper report codes as a basis, but I'm battling to understand it.

 

Can someone perhaps help?

 

Thanks

Hanlie

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

networkCount and year_monthCount are report variables, and you can't access them in your crosstab.

 

Your crosstab is intriguing; for instance you define a measure having class="java.lang.String" and calculation="Sum" - are you trying to sum Strings? Go over the crosstab samples again to understand how crosstabs work.

 

Regards,

Lucian

Post edited by: lucianc, at: 2006/11/21 14:15

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...