mutantbc Posted June 21, 2007 Share Posted June 21, 2007 How do I group by month in ireport? The text field expression($F{DeliveryDate}) is returning Timestamp. Can I do a filter by month? Edit: Fixed the problem. Just tweak a little in SQL syntax and modify text field expression. :) 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="SalesPerItemReport" columnCount="1" printOrder="Vertical" orientation="Portrait" pageWidth="842" pageHeight="595" columnWidth="782" 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.*" /> <queryString><![CDATA[sELECT ProductName, Product_Id, DeliveryDate, SUM(Quantity), UnitPrice FROM customer_order GROUP BY Product_Id, Month(DeliveryDate) ORDER BY DeliveryDate;]]></queryString> <field name="ProductName" class="java.lang.String"/> <field name="Product_Id" class="java.math.BigInteger"/> <field name="DeliveryDate" class="java.sql.Timestamp"/> <field name="SUM(Quantity)" class="java.math.BigDecimal"/> <field name="UnitPrice" class="java.lang.Float"/> <group name="DeliveryDate" > <groupExpression><![CDATA[$F{DeliveryDate}]]></groupExpression> <groupHeader> <band height="25" isSplitAllowed="true" > <rectangle radius="0" > <reportElement x="0" y="0" width="782" height="25" forecolor="#8080FF" backcolor="#000000" key="rectangle"/> <graphicElement stretchType="NoStretch"/> </rectangle> <staticText> <reportElement x="3" y="2" width="96" height="23" forecolor="#FFFFFF" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="16"/> </textElement> <text><![CDATA[DeliveryDate]]></text> </staticText> <textField isStretchWithOverflow="false" pattern="MMM-yy" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="102" y="2" width="98" height="23" forecolor="#FFFFFF" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="18"/> </textElement> <textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{DeliveryDate}]]></textFieldExpression> </textField> </band> </groupHeader> <groupFooter> <band height="10" isSplitAllowed="true" > <line direction="BottomUp"> <reportElement x="1" y="4" width="781" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> </band> </groupFooter> </group> <background> <band height="0" isSplitAllowed="true" > </band> </background> <title> <band height="50" isSplitAllowed="true" > <staticText> <reportElement x="159" y="5" width="465" height="40" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="30"/> </textElement> <text><![CDATA[Classic Report Template (Landscape)]]></text> </staticText> <line direction="TopDown"> <reportElement x="0" y="48" width="781" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> <line direction="TopDown"> <reportElement x="0" y="3" width="781" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> </band> </title> <pageHeader> <band height="9" isSplitAllowed="true" > </band> </pageHeader> <columnHeader> <band height="18" isSplitAllowed="true" > <rectangle> <reportElement mode="Opaque" x="0" y="0" width="782" height="17" forecolor="#000000" backcolor="#808080" key="rectangle"/> <graphicElement stretchType="NoStretch" pen="None"/> </rectangle> <line direction="BottomUp"> <reportElement x="0" y="0" width="782" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> <line direction="BottomUp"> <reportElement x="0" y="15" width="782" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> <staticText> <reportElement x="0" y="0" width="195" height="17" forecolor="#FFFFFF" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12"/> </textElement> <text><![CDATA[ProductName]]></text> </staticText> <staticText> <reportElement x="195" y="0" width="195" height="17" forecolor="#FFFFFF" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12"/> </textElement> <text><![CDATA[Product_Id]]></text> </staticText> <staticText> <reportElement x="390" y="0" width="195" height="17" forecolor="#FFFFFF" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12"/> </textElement> <text><![CDATA[sUM(Quantity)]]></text> </staticText> <staticText> <reportElement x="585" y="0" width="195" height="17" forecolor="#FFFFFF" key="staticText"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12"/> </textElement> <text><![CDATA[unitPrice]]></text> </staticText> </band> </columnHeader> <detail> <band height="17" isSplitAllowed="true" > <line direction="TopDown"> <reportElement x="0" y="16" width="782" height="0" forecolor="#808080" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="0" y="0" width="195" height="17" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{ProductName}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="195" y="0" width="195" height="17" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Number"><![CDATA[$F{Product_Id}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="390" y="0" width="195" height="17" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{SUM(Quantity)}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="585" y="0" width="195" height="17" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Float"><![CDATA[$F{UnitPrice}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="0" isSplitAllowed="true" > </band> </columnFooter> <pageFooter> <band height="27" isSplitAllowed="true" > <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="573" y="4" width="170" height="19" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement textAlignment="Right"> <font/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement mode="Transparent" x="746" y="4" width="36" height="19" forecolor="#000000" backcolor="#FFFFFF" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER}]]></textFieldExpression> </textField> <line direction="TopDown"> <reportElement x="0" y="3" width="782" height="0" forecolor="#000000" backcolor="#FFFFFF" key="line"/> <graphicElement stretchType="NoStretch"/> </line> <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" > <reportElement x="1" y="6" width="209" height="19" forecolor="#000000" key="textField"/> <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/> <textElement> <font/> </textElement> <textFieldExpression class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression> </textField> </band> </pageFooter> <summary> <band height="0" isSplitAllowed="true" > </band> </summary></jasperReport> SQL Syntax:Code:[code]SELECT ProductName, Product_Id, DeliveryDate, SUM(Quantity), UnitPrice FROM customer_order GROUP BY Product_Id, Month(DeliveryDate) ORDER BY DeliveryDate;Post edited by: mutantbc, at: 2007/06/22 04:33 Link to comment Share on other sites More sharing options...
xwcheng Posted June 26, 2007 Share Posted June 26, 2007 SELECT ProductName, Product_Id, DeliveryDate, SUM(Quantity), UnitPrice FROM customer_order GROUP BY Product_Id, Month(DeliveryDate) ORDER BY DeliveryDate; in fact your sql syntax can not compile. a correct one is like thisCode:SELECT ProductName, Product_Id, DeliveryDate, SUM(Quantity), UnitPrice FROM customer_order GROUP BY ProductName, Product_Id, DeliveryDate, UnitPrice ORDER BY DeliveryDate;you can adjust your sql as need like aboveHope to help you.Post edited by: Cheng.Ken, at: 2007/06/26 01:06 Link to comment Share on other sites More sharing options...
mutantbc Posted June 28, 2007 Author Share Posted June 28, 2007 thanks for the advise but I was able to manage it. ;) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now