14

First of all, the records are shown in the table by table component but not in the report one.

The results looks like this:

 YEARS MONTHS SUMMONTH SUMQUARTER   
 ----- ------ -------- ----------  
  2009 Jan      130984     432041
       Feb      146503
       Mar      154554     
       Apr      147917     435150 
       May      131822     
       Jun      155411     
       Jul      144000     424806 
       Aug      130369     
       Sep      150437     
       Oct      112137     400114 
       Nov      152057     
       Dec      135920     
 =====================================
       Jan-Dec  1692111
 =====================================
  2010 Jan      139927     417564 
       Feb      154940     
       Mar      122697     
       Apr      163257     413305 
       May      124999     
       Jun      125049     
       Jul      145127     427612 
       Aug      138804     
       Sep      143681     
       Oct      143398     406381 
       Nov      125351     
       Dec      137632     
 =====================================
       Jan-Dec  1664862
 =====================================

The sumquarter column shows the sum of each quarter in year.

They are not printed when it repeated the value of the field.

The question is how to group the column of sumquarter, so that the first printed repeated value in each row to join the next repeated value to become a single cell until it meets the non-repeated value?

You can simply see it in an image. Below is the image that the table shows and the solution that I preferred is to group those 3 months of sum into a single cell.

Here is the image:

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1264222
  • 225
  • 2
  • 4
  • 12

1 Answers1

19

You can use this sample:

<?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="year_sum_quarter" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="year" class="java.lang.Integer"/>
    <field name="month" class="java.lang.String"/>
    <field name="sum" class="java.lang.Integer"/>
    <field name="q" class="java.lang.Integer"/>
    <variable name="yearSum" class="java.lang.Integer" resetType="Group" resetGroup="yearGroup" calculation="Sum">
        <variableExpression><![CDATA[$F{sum}]]></variableExpression>
    </variable>
    <variable name="qSum" class="java.lang.Integer" resetType="Group" resetGroup="quaterGroup" calculation="Sum">
        <variableExpression><![CDATA[$F{sum}]]></variableExpression>
    </variable>
    <group name="yearGroup">
        <groupExpression><![CDATA[$F{year}]]></groupExpression>
        <groupFooter>
            <band height="20">
                <textField>
                    <reportElement x="100" y="0" width="100" height="20"/>
                    <box leftPadding="10">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="0.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="0.0"/>
                    </box>
                    <textElement/>
                    <textFieldExpression><![CDATA["Jan-Dec, " + $F{year}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="0" width="100" height="20"/>
                    <box leftPadding="0">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="0.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="0.0"/>
                    </box>
                    <textElement/>
                    <textFieldExpression><![CDATA[$V{yearSum}]]></textFieldExpression>
                </textField>
                <staticText>
                    <reportElement x="0" y="0" width="100" height="20"/>
                    <box>
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="0.0"/>
                    </box>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font isBold="true" isItalic="true"/>
                    </textElement>
                    <text><![CDATA[]]></text>
                </staticText>
                <staticText>
                    <reportElement x="300" y="0" width="100" height="20"/>
                    <box>
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="0.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font isBold="true" isItalic="true"/>
                    </textElement>
                    <text><![CDATA[]]></text>
                </staticText>
            </band>
        </groupFooter>
    </group>
    <group name="quaterGroup">
        <groupExpression><![CDATA[$F{year} + $F{q}]]></groupExpression>
    </group>
    <columnHeader>
        <band height="50">
            <staticText>
                <reportElement x="100" y="30" width="100" height="20"/>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Month]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="30" width="100" height="20"/>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Year]]></text>
            </staticText>
            <staticText>
                <reportElement x="200" y="30" width="100" height="20"/>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Month Sum]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="30" width="100" height="20"/>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Quarter Sum]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="300" y="0" width="100" height="20"/>
                <box>
                    <topPen lineWidth="0.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="0.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box>
                    <topPen lineWidth="0.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="0.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[]]></text>
            </staticText>
            <textField>
                <reportElement x="0" y="0" width="100" height="20">
                    <printWhenExpression><![CDATA[$V{yearGroup_COUNT} == 1]]></printWhenExpression>
                </reportElement>
                <box leftPadding="10">
                    <topPen lineWidth="0.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="0.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$F{year}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="200" y="0" width="100" height="20"/>
                <box leftPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$F{sum}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20"/>
                <box leftPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$F{month}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Group" evaluationGroup="quaterGroup" isBlankWhenNull="false">
                <reportElement stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="300" y="0" width="100" height="20" printWhenGroupChanges="quaterGroup">
                    <printWhenExpression><![CDATA[$V{quaterGroup_COUNT} == 1]]></printWhenExpression>
                </reportElement>
                <box leftPadding="10">
                    <topPen lineWidth="0.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="0.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$V{qSum}]]></textFieldExpression>
            </textField>
            <line>
                <reportElement x="300" y="0" width="100" height="1" printWhenGroupChanges="quaterGroup">
                    <printWhenExpression><![CDATA[$V{quaterGroup_COUNT} == 1]]></printWhenExpression>
                </reportElement>
            </line>
        </band>
    </detail>
</jasperReport>

The result will be (in pdf format): Resulting report in PDF format

In this sample I've used two elements in the Detail band for the Year column: one textField with only vertical borders and with printWhenExpression: "$V{yearGroup_COUNT} == 1" property (I'm show it only once for the whole yearGroup) and one staticText without any text and with only vertical borders.

I've used three elements in the Detail band for the Quarter Sum column:
one textField with only vertical borders and with printWhenExpression: "$V{quaterGroup_COUNT} == 1" property (I'm show it only once for the whole quaterGroup), one staticText without any text and with only vertical borders and the line element for drawing horizontal border with printWhenExpression: "$V{quaterGroup_COUNT} == 1" property.

Alex K
  • 22,315
  • 19
  • 108
  • 236
  • thanks your help...but as i have mentioned above, the output should be display in a table component but not in a report one. I have a table component contains a dataset to retrieve those records by a sql...Can this solution also be done in a table component? thanks again... – user1264222 Mar 21 '12 at 15:28
  • i work with the printWhenExpression method..but since I cannot put a line element into a table, so...how to show the horizontal line in code? thanks – user1264222 Mar 22 '12 at 02:01
  • I have not any problem with adding the `line` element. You should add it and set this element's `height` property with `1` value: – Alex K Mar 22 '12 at 09:17
  • @user1264222 Could you please post your jrxml, I'm trying to do the same: Merging cells across several rows (like the year column in your case) in a table (not a (sub)-report), but I don't know how to reference the group. The compiler can't find the $V{yearGroup_COUNT} when I use it in the table. – Torsten Dec 05 '12 at 17:16
  • @Torsten `The compiler can't find the $V{yearGroup_COUNT} when I use it in the table.` - Did you add the group? – Alex K Dec 05 '12 at 18:58
  • Hi Alex K, I hoped you'd answer :D I put the group to the table's subDataset and referenced one of the subDataset's fields in the group. Now it is working. However, I got another two questions, which I posted here: http://stackoverflow.com/questions/13739336/ireport-cell-spanning-in-table-component – Torsten Dec 06 '12 at 08:05
  • 1
    Hi @Alex K..Thanks for your answer..I have same requirement but I am little bit confused about bottom border of Year column. You didn't specify anywhere bottom border width=1. So how does it come? – Vishal Zanzrukia Apr 11 '14 at 08:28
  • @AlexK For my requirement , I don't want to print the field year in top. instead i need to merge the cells in the column and to display year aligned center vertically and horizontally. Will you help me for that? – Tinoy Jameson Dec 09 '15 at 05:47
  • Hi @AlexK, thanks for the answer. Luckily, I have same scenario with the OP. But, Since the data is dynamic, the data for each yearGroup (in this case) is may be split into the next page and ```"Jan-Dec, " + $F{year}``` will be printed in the next page. Then the bottom of the page for yearGroup will have no "border bottom" or "line". Do you know how to solve this? – Akza Sep 06 '20 at 12:30