Discrepancy Between Group (Sum) Variable and Record Counts

0

Greetings all. I am building a report from a data set with the following fields:

 

<field name="prop_id" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="prop_id"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="business_date" class="java.sql.Date">
        <property name="com.jaspersoft.studio.field.label" value="business_date"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="transaction_date" class="java.sql.Timestamp">
        <property name="com.jaspersoft.studio.field.label" value="transaction_date"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="sale_hour" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="sale_hour"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="sale_minute" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="sale_minute"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="plunum" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="plunum"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="count" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="count"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="cm_type" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="cm_type"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>
    <field name="short_name" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="short_name"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="lunch_vm_transaction_view"/>
    </field>

I have created two groups based on the prop_id, and cm_type fields. The data is sorted as follows:

    <queryString>
        <![CDATA[SELECT * FROM poll.lunch_vm_transaction_view WHERE
business_date >= $P{startDate} AND business_date <= $P{endDate}
ORDER BY prop_id, cm_type, sale_hour, sale_minute]]>
    </queryString>

I am attempting to do some grouping/aggregation based on time of day. As such, I have created five variables - IsBreakfast, IsLunch, IsSnack, IsDinner, IsLate - with definitions such as the following "($F{sale_hour} >= 20) ? true : false". In my Detail band, I have the following:

        <textField>
                <reportElement x="642" y="0" width="160" height="14" uuid="224bbfe3-69d5-4bab-91b0-67cfc28e0f03"/>
                <textFieldExpression><![CDATA[$V{isBreakfast} ? "BRKFST" : $V{isLunch} ? "LNCH" : $V{isSnack} ? "SNCK"  : $V{isDinner} ? "DNR" : "LT"]]></textFieldExpression>
            </textField>

In this field, every record is categorized correctly. The issue comes in the summary footers for the Combo and Property groups. In the Combo group for eample, I have the following footer:

        <groupFooter>
            <band height="24">
                <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.grid.JSSGridBagLayout"/>
                <property name="com.jaspersoft.studio.unit.height" value="px"/>
                <staticText>
                    <reportElement x="0" y="0" width="101" height="24" uuid="ba5a2d2c-1a08-4202-89df-54f1c121decd"/>
                    <text><![CDATA[Combo Size Totals:]]></text>
                </staticText>
                <textField>
                    <reportElement x="101" y="0" width="101" height="24" uuid="6db92494-65f5-446e-a64d-de169779c131"/>
                </textField>
                <textField>
                    <reportElement x="202" y="0" width="100" height="24" uuid="2b406783-9886-4e0d-bc00-ebbcc8124450"/>
                    <textFieldExpression><![CDATA[$V{ComboTotalBreakfast}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="302" y="0" width="100" height="24" uuid="d49cde59-9b9b-406b-a567-52f8e434339b"/>
                    <textFieldExpression><![CDATA[$V{ComboTotalLunch}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="402" y="0" width="100" height="24" uuid="029df21b-a3bf-4e8f-9ca3-a11e3d6e9292"/>
                    <textFieldExpression><![CDATA[$V{ComboTotalSnack}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="502" y="0" width="100" height="24" uuid="4b104ee8-a5aa-4530-ad80-5ff0b23ba9d7"/>
                    <textFieldExpression><![CDATA[$V{ComboTotalDinner}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="602" y="0" width="100" height="24" uuid="9897e000-a667-4644-b522-9c80d79c6b5c"/>
                    <textFieldExpression><![CDATA[$V{ComboTotalLate}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="702" y="0" width="100" height="24" uuid="136021ba-89eb-4b5f-9f39-e429a1ce65e4"/>
                    <textFieldExpression><![CDATA[$V{ComboCount}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>

Each of the variables is very simple with; they simply sum up the count field based on the time of day. In example:

    <variable name="ComboTotalLate" class="java.lang.Integer" resetType="Group" resetGroup="CMTypeGroup" calculation="Sum">
        <variableExpression><![CDATA[$V{isLate} ? $F{count} : 0]]></variableExpression>
    </variable>

However, these "ComboTotal***" variables are not matching up to how the records are classified in the Detail band. Take the following as an example.

This is near the end of a combo group. The records are ordered by hour and minute, so the day parts (Lunch, Breakfast, Late, etc) are also sorted accordingly. As you can see in the Detail band, three records are appropriately grouped as "LT/Late". However, in the summary row, only two records (7th columns) are counted as "Late". This is just one example; the issue appears throughout the report. It is not restricted to the "Late" daypart, but can be observed in others. This one was just among the most evident for illustration.

I have tried changing the evluation times on the TextFields but the issue persists. Any advice is appreciated.

kmarchewa's picture
Joined: Aug 23 2019 - 10:43am
Last seen: 15 hours 5 min ago

0 Answers:

No answers yet
Feedback
randomness