2002 JI Open Discussion Posted August 18, 2006 Share Posted August 18, 2006 By: Mohamed Kreifeur - moh2100 please help me ? 2003-09-30 14:28 Hi everyone I am facing a problem of grouping calculation... When summing up fields in the details section, the sum works fine. But when i try to sum up fields in the group, I had errounous values (it sums up group field value even repeated with each record in the detail section) Take a look a the xml file! plz and give me the value of "original budget hours " for the group "type" that must sum up projOrBudget ("project" group) Is there some thing like "running totals" in Crystal report in Jasperreports Could any one get my report worked ? plz Any help is very appreciated !!! Moh here My tables data types table(typeID,typeDesc) 10, dev 20, mnt projects table(projID,projDesc,projOrBudget,typeID) 1,P1,250,20 2,P2,350,10 3,P3,300,20 4,P4,200,10 activities table(actvID,actvDesc,actvHours,projID) 1, A1, 25,1 2, A2, 35,1 3, A3, 20,2 4, A4, 10,2 5, A5, 30,3 6, A6, 15,3 7, A7, 25,4 8, A8, 10,4 here is my xml file <?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="moh1" columnCount="1" printOrder="Vertical" orientation="Portrait" pageWidth="595" pageHeight="842" columnWidth="535" columnSpacing="0" leftMargin="28" rightMargin="22" topMargin="20" bottomMargin="20" whenNoDataType="NoPages" isTitleNewPage="false" isSummaryNewPage="false"> <parameter name="fromDate" isForPrompting="true" class="java.lang.String"> <defaultValueExpression><![CDATA["yyyy-mm-ddd"]]></defaultValueExpression> </parameter> <parameter name="toDate" isForPrompting="true" class="java.lang.String"> <defaultValueExpression><![CDATA["yyyy-mm-ddd"]]></defaultValueExpression> </parameter> <queryString><![CDATA[select typeDesc, projDesc, projOrBudget, actvDesc, actvHours from types, projects, activities where types.typeID=projects.typeID and projects.projID=activities.projID group by typeDesc, projDesc, actvDesc;]]></queryString> <field name="typeDesc" class="java.lang.String"/> <field name="projDesc" class="java.lang.String"/> <field name="projOrBudget" class="java.lang.Double"/> <field name="actvDesc" class="java.lang.String"/> <field name="actvHours" class="java.lang.Double"/> <variable name="RANGEHOURS_PRJ" class="java.lang.Double" resetType="Group" resetGroup="project" calculation="Sum"> <variableExpression><![CDATA[$F{actvHours}]]></variableExpression> <initialValueExpression><![CDATA[new Double(0)]]></initialValueExpression> </variable> <variable name="RANGEHOURS_TYPE" class="java.lang.Double" resetType="Group" resetGroup="Types" calculation="Sum"> <variableExpression><![CDATA[$F{actvHours}]]></variableExpression> <initialValueExpression><![CDATA[new Double(0)]]></initialValueExpression> </variable> <variable name="OR_BUD_TYPE" class="java.lang.Double" resetType="Group" resetGroup="Types" calculation="Sum"> <variableExpression><![CDATA[$F{projOrBudget}]]></variableExpression> <initialValueExpression><![CDATA[new Double(0)]]></initialValueExpression> </variable> <group name="Types" isStartNewColumn="false" isStartNewPage="false" isResetPageNumber="false" isReprintHeaderOnEachPage="false" minHeightToStartNewPage="0" > <groupExpression><![CDATA[$F{typeDesc}]]></groupExpression> <groupHeader> <band height="17"> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="Types" hyperlinkType="None" > <reportElement mode="Opaque" x="7" y="2" width="201" height="12" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="SansSerif" pdfFontName="Helvetica" size="9" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{typeDesc}]]></textFieldExpression> </textField> <line direction="TopDown"> <reportElement mode="Opaque" x="8" y="14" width="336" height="0" forecolor="#993300" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" /> </line> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="Types" hyperlinkType="None" > <reportElement mode="Opaque" x="302" y="2" width="40" height="10" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" printWhenGroupChanges="project" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="SansSerif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{RANGEHOURS_TYPE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="0.00" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="project" hyperlinkType="None" > <reportElement mode="Transparent" x="226" y="2" width="68" height="10" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" printWhenGroupChanges="Types" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{OR_BUD_TYPE}]]></textFieldExpression> </textField> </band> </groupHeader> <groupFooter> <band height="3"> </band> </groupFooter> </group> <group name="project" isStartNewColumn="false" isStartNewPage="false" isResetPageNumber="false" isReprintHeaderOnEachPage="false" minHeightToStartNewPage="0" > <groupExpression><![CDATA[$F{projDesc}]]></groupExpression> <groupHeader> <band height="13"> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="project" hyperlinkType="None" > <reportElement mode="Opaque" x="14" y="2" width="193" height="10" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" printWhenGroupChanges="project" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{projDesc}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="0.00" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="project" hyperlinkType="None" > <reportElement mode="Opaque" x="226" y="2" width="68" height="10" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" printWhenGroupChanges="Types" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{projOrBudget}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="project" hyperlinkType="None" > <reportElement mode="Opaque" x="302" y="2" width="40" height="10" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" printWhenGroupChanges="project" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="SansSerif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{RANGEHOURS_PRJ}]]></textFieldExpression> </textField> <line direction="TopDown"> <reportElement mode="Opaque" x="15" y="12" width="328" height="0" forecolor="#993300" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" /> </line> </band> </groupHeader> <groupFooter> <band height="0"> </band> </groupFooter> </group> <background> <band height="10"> </band> </background> <title> <band height="50"> <staticText> <reportElement mode="Transparent" x="87" y="11" width="327" height="17" forecolor="#993300" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="14" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[PROJECT MANAGEMENT REPORT - SUMMARY ]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="385" y="31" width="25" height="12" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="9" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[From:]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="475" y="31" width="47" height="12" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica-Bold" size="9" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[9999/99/99]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="460" y="31" width="13" height="12" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica" size="9" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[To:]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="411" y="31" width="44" height="12" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica-Bold" size="9" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[9999/99/99]]></text> </staticText> </band> </title> <pageHeader> <band height="48"> <line direction="TopDown"> <reportElement mode="Opaque" x="3" y="4" width="520" height="0" forecolor="#800000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <graphicElement stretchType="NoStretch" pen="1Point" fill="Solid" /> </line> <line direction="TopDown"> <reportElement mode="Opaque" x="3" y="45" width="520" height="0" forecolor="#800000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <graphicElement stretchType="NoStretch" pen="1Point" fill="Solid" /> </line> <staticText> <reportElement mode="Transparent" x="228" y="8" width="67" height="33" forecolor="#993300" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica-Bold" size="8" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[Original Budget Hours]]></text> </staticText> <staticText> <reportElement mode="Transparent" x="300" y="7" width="41" height="26" forecolor="#993300" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="sansserif" pdfFontName="Helvetica-Bold" size="8" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <text><![CDATA[Actual hours]]></text> </staticText> </band> </pageHeader> <columnHeader> <band height="0"> </band> </columnHeader> <detail> <band height="14"> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement mode="Opaque" x="22" y="2" width="182" height="11" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Left" verticalAlignment="Top" lineSpacing="Single"> <font fontName="SansSerif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{actvDesc}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" > <reportElement mode="Opaque" x="303" y="2" width="38" height="11" forecolor="#000000" backcolor="#FFFFFF" positionType="FixRelativeToTop" isPrintRepeatedValues="true" isRemoveLineWhenBlank="false" isPrintInFirstWholeBand="false" isPrintWhenDetailOverflows="false"/> <textElement textAlignment="Right" verticalAlignment="Top" lineSpacing="Single"> <font fontName="SansSerif" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" /> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{actvHours}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="3"> </band> </columnFooter> <pageFooter> <band height="3"> </band> </pageFooter> <summary> <band height="3"> </band> </summary> </jasperReport> 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