Поехали по пунктам. Есть рабочий отчет:bill_report_2.
Код:
<?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="bill_report_1"
columnCount="1"
printOrder="Vertical"
orientation="Portrait"
pageWidth="595"
pageHeight="842"
columnWidth="535"
columnSpacing="0"
leftMargin="30"
rightMargin="30"
topMargin="20"
bottomMargin="20"
whenNoDataType="NoPages"
isTitleNewPage="false"
isSummaryNewPage="false">
<property name="ireport.scriptlethandling" value="2" />
<property name="ireport.encoding" value="UTF-8" />
<import value="java.util.*" />
<import value="net.sf.jasperreports.engine.*" />
<import value="net.sf.jasperreports.engine.data.*" />
<parameter name="_filter" isForPrompting="false" class="bitel.billing.server.admin.reports.BGReportFilter"/>
<parameter name="_months" isForPrompting="false" class="java.util.Map"/>
<queryString language="BGBS"><![CDATA[import java.sql.*;
import java.util.*;
public void fillReport( con, filter, result )
{
PreparedStatement ps = con.prepareStatement( result.sql( ""+
"select t1.id,t1.cid,t1.summ as summ, t1.format_number as Fnum,"+
"t1.create_dt as date,t2.val as val from bill_invoice_data_5 as t1 "+
"join contract_parameter_type_1 as t2 on t1.cid=t2.cid where t2.pid=8 "+
"and date_format(date,'%m')='$mm(month)", filter) );
ResultSet rs = ps.executeQuery();
result.setDataSource( rs );
}
]]></queryString>
<field name="summ" class="java.math.BigDecimal"/>
<field name="Fnum" class="java.math.BigDecimal"/>
<field name="date" class="java.util.Date"/>
<field name="val" class="java.lang.String"/>
<background>
<band height="0" isSplitAllowed="true" >
</band>
</background>
<title>
<band height="50" isSplitAllowed="true" >
</band>
</title>
<pageHeader>
<band height="50" isSplitAllowed="true" >
</band>
</pageHeader>
<columnHeader>
<band height="30" isSplitAllowed="true" >
</band>
</columnHeader>
<detail>
<band height="100" isSplitAllowed="true" >
<textField isStretchWithOverflow="false" pattern="##0.00" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="0"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{summ}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" pattern="###0" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="100"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{Fnum}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="200"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.util.Date"><![CDATA[$F{date}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="300"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{val}]]></textFieldExpression>
</textField>
<staticText>
<reportElement
x="400"
y="0"
width="14"
height="12"
key="staticText-1"/>
<box></box>
<textElement>
<font/>
</textElement>
<text><![CDATA[b]]></text>
</staticText>
</band>
</detail>
<columnFooter>
<band height="30" isSplitAllowed="true" >
</band>
</columnFooter>
<pageFooter>
<band height="50" isSplitAllowed="true" >
</band>
</pageFooter>
<lastPageFooter>
<band height="50" isSplitAllowed="true" >
</band>
</lastPageFooter>
<summary>
<band height="50" isSplitAllowed="true" >
</band>
</summary>
</jasperReport>
Он выдергивает из модуля бухгалтерии счетфактуры - он работает. На его основе сделан репорт этот -
Код:
<?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="bill_report_1"
columnCount="1"
printOrder="Vertical"
orientation="Portrait"
pageWidth="595"
pageHeight="842"
columnWidth="535"
columnSpacing="0"
leftMargin="30"
rightMargin="30"
topMargin="20"
bottomMargin="20"
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.*" />
<parameter name="_filter" isForPrompting="false" class="bitel.billing.server.admin.reports.BGReportFilter"/>
<parameter name="_months" isForPrompting="false" class="java.util.Map"/>
<queryString language="BGBS"><![CDATA[import java.sql.*;
import java.util.*;
public void fillReport( con, filter, result )
{
PreparedStatement ps = con.prepareStatement( result.sql( ""+
" select login_name as login, sum(input_octets)/1048576 as inm,"+
" sum(output_octets)/1048576 as outm"+
" from $module_month_table( log_session , date ) group by login_name;", filter) );
ResultSet rs = ps.executeQuery();
result.setDataSource( rs );
}]]></queryString>
<field name="login" class="java.lang.String"/>
<field name="inm" class="java.lang.String"/>
<field name="outm" class="java.lang.String"/>
<field name="val" class="java.lang.String"/>
<background>
<band height="0" isSplitAllowed="true" >
</band>
</background>
<title>
<band height="50" isSplitAllowed="true" >
</band>
</title>
<pageHeader>
<band height="50" isSplitAllowed="true" >
</band>
</pageHeader>
<columnHeader>
<band height="30" isSplitAllowed="true" >
</band>
</columnHeader>
<detail>
<band height="100" isSplitAllowed="true" >
<textField isStretchWithOverflow="false" pattern="##0.00" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="7"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{login}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" pattern="###0" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="107"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{inm}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="207"
y="0"
width="100"
height="12"
key="textField"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{outm}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band height="30" isSplitAllowed="true" >
</band>
</columnFooter>
<pageFooter>
<band height="50" isSplitAllowed="true" >
</band>
</pageFooter>
<lastPageFooter>
<band height="50" isSplitAllowed="true" >
</band>
</lastPageFooter>
<summary>
<band height="50" isSplitAllowed="true" >
</band>
</summary>
</jasperReport>
В нем единственное, что новое это суммаризация и группировка внутри sql. Стоит заметить, что подобную ошибку я также получал при запросе
Код:
<queryString><![CDATA[
select t5.title, t5.comment,t7.val, t4.login_alias, t4.pswd, t4.date1,t4.date2 from
(select t2.cid,t3.login_id,t3.login_alias, t2.login, t2.pswd, t2.date1,
t2.date2 from user_login_3 as t2 right join user_alias_3 as t3 on t3.login_id=t2.id) as t4
right join contract as t5 on t5.id=t4.cid
right join contract_parameter_type_1 as t7 on t7.cid=t5.id
where t7.pid=34 and mid(t5.title,1,4)='7120']]></queryString>
Вот такие вот пирожки с котятами пока. Чего я не вижу или не знаю?