Aggregate Date / First Value / Multiple Values per Field

[zgadson]

Hello, desperately seeking some assistance getting my report to work.

I have a logistics report to track freight for my comapny but it returns multiple lines for one order. In the sample of my report below, it returns multiple lines for every one load number, likely due to the data source consisting of multiple tracking updates for every load. That said, how do I filter the Status Update and Status fields to only use the most recent values? What I want is for every load number to be contained to only one line with the most recent update and status.

If I can use the expression editor for this, that woud be awesome, but I'd sincerely appreciate any direction or advice. I'm sure it's not as difficult as it seems but I'm completely lost and frustrated.

Relevant Code:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 7.2.0.final using JasperReports Library version 6.6.0  -->
    <queryFields>
        <queryField id="Load.LoadNum"/>
        <queryField id="LoadStatusActivity.DateTime_StatusEvent"/>
        <queryField id="LoadStatusActivity.LoadStatusComment"/>
    </queryFields>
    <groupList>
        <group columnName="LoadStatusActivity.LoadStatusComment"/>
        <group columnName="LoadStatusActivity.DateTime_StatusEvent"/>
        <group columnName="Load.LoadNum"/>
    </groupList>
    <queryFilterString> Load.LoadNum == 'L181008-00023'</queryFilterString>
    <sortList>
        <sortColumn columnName="LoadStatusActivity.DateTime_StatusEvent" ascending="false"/>
    </sortList>
</query>]]>
    </queryString>
    <field name="Load.LoadNum" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="LoadNum"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="Load"/>
        <fieldDescription><![CDATA[LoadNum]]></fieldDescription>
    </field>
    <field name="LoadStatusActivity.DateTime_StatusEvent" class="java.sql.Timestamp">
        <property name="com.jaspersoft.studio.field.label" value="DateTime_StatusEvent"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="LoadStatusActivity"/>
        <fieldDescription><![CDATA[DateTime_StatusEvent]]></fieldDescription>
    </field>
    <field name="LoadStatusActivity.LoadStatusComment" class="java.lang.String">
        <fieldDescription><![CDATA[LoadStatusComment]]></fieldDescription>
    </field>
    <group name="Load.LoadNum">
        <groupExpression><![CDATA[$F{Load.LoadNum}]]></groupExpression>
    </group>
    <columnHeader>
        <band height="44" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="100" height="30" uuid="3f6001d9-85cd-4717-bbf9-905cf1bbd0f0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6062cd80-9dfa-4723-9cbe-1d0650881bbd"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Load Number]]></text>
            </staticText>
            <staticText>
                <reportElement x="120" y="0" width="100" height="30" uuid="36de86f2-54fb-4ab8-95a4-e8c25eb49a61">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="af544b8b-36da-498e-8808-8e894aaafae0"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[status Update]]></text>
            </staticText>
            <staticText>
                <reportElement x="280" y="0" width="100" height="30" uuid="6d53d1a5-f2cf-4a99-af28-caacff67c487">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e828e01c-312e-464e-932f-869d1751a788"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[status Comment]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="24" splitType="Stretch">
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="100" height="18" isRemoveLineWhenBlank="true" uuid="12a476ec-cf43-4b94-a988-ea00621baf32">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6062cd80-9dfa-4723-9cbe-1d0650881bbd"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{Load.LoadNum}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="120" y="0" width="100" height="19" uuid="26c71bd3-5248-4318-b26d-4320d1d84b80">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="af544b8b-36da-498e-8808-8e894aaafae0"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{LoadStatusActivity.DateTime_StatusEvent}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="280" y="0" width="100" height="18" uuid="b001bdcb-adbb-4fdc-8c9a-88145a2cf213">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e828e01c-312e-464e-932f-869d1751a788"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{LoadStatusActivity.LoadStatusComment}]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

 

[matthew.kellar]

Hi there,

This should really be done in the SQL of the report to make the most sense. If you're able to edit the SQL, try implementing something like this:

 

SELECT order_id, update_date

FROM <table>

WHERE (order_id, update_date) IN

(

SELECT order_id, max(update_date)

FROM <table>

GROUP BY order_id

)

 

 

This is a very simplified example, but the same principles should be easily applied to your case.

[zgadson]

Thanks for the reply, however, I do have 2 questions.

1) My data is arranged more in a freeform type of way - I did not use the Table function. Do I need to use the Table for this to work?

2) Below is all I have access to as far as SQL coding. I've tried a few simple SQL Select statements but nothing seems to work in here. Where/how would I add the code you provided? (I'm really not computer illiterate, but this is entirely foreign to me.)

Thank you!

Zach

[djohnson53]

None of your pictures work in any of your posts.

[zgadson]

I can see my posts just fine from my own machine, but you are correct. I've noticed they do not show up when I use a computer other than my own. They're all hosted in my Gmail photos which is clearly not working.

Can you see the photo here?

/sites/default/files/user_uploads/djohnson53/capture_1.png

[djohnson53]

yes