Geek asks and answers

How to sum specific lines in iReport with a request?

I am trying to create a pivot table using iReport. My dataset returns a list of purchases and prices. Sort of

Milk, $1.23

Chicken, $5.45

Milk, $1.44

etc. I want my table to be able to break my product into a product. I need a table with columns:

  • How many times a product was purchased (#rows where milk was made),
  • the total dollar spent on the product (the sum of the prices where the product is milk),
  • and the average price of the product (column 2 is divided by column 1).

How can i do this? I play with variables and I can get the total amount of all prices, but I don’t know how to do this with a subset of the data using a more complex query.

+5
source share
1 answer

. product : . .

:

<?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="group_average2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="product" class="java.lang.String"/>
    <field name="price" class="java.lang.Integer"/>
    <sortField name="product"/>
    <variable name="totalSum" class="java.lang.Double" resetType="Group" resetGroup="productGroup" calculation="Sum">
        <variableExpression><![CDATA[$F{price}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="averageSum" class="java.lang.Double" resetType="Group" resetGroup="productGroup" calculation="Average">
        <variableExpression><![CDATA[$F{price}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <group name="productGroup">
        <groupExpression><![CDATA[$F{product}]]></groupExpression>
        <groupFooter>
            <band height="20">
                <textField>
                    <reportElement x="0" y="0" width="122" height="20"/>
                    <box leftPadding="10" rightPadding="10">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement/>
                    <textFieldExpression><![CDATA[$F{product}]]></textFieldExpression>
                </textField>
                <textField pattern="###0.00;-###0.00">
                    <reportElement x="244" y="0" width="122" height="20"/>
                    <box leftPadding="10" rightPadding="10">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement textAlignment="Right"/>
                    <textFieldExpression><![CDATA[$V{totalSum}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="122" y="0" width="122" height="20"/>
                    <box leftPadding="10" rightPadding="10">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement/>
                    <textFieldExpression><![CDATA[$V{productGroup_COUNT}]]></textFieldExpression>
                </textField>
                <textField pattern="###0.00;-###0.00">
                    <reportElement x="366" y="0" width="149" height="20"/>
                    <box leftPadding="10" rightPadding="10">
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement textAlignment="Right"/>
                    <textFieldExpression><![CDATA[$V{averageSum}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <pageHeader>
        <band height="19">
            <staticText>
                <reportElement x="0" y="-1" width="122" height="20"/>
                <box leftPadding="10" rightPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Product]]></text>
            </staticText>
            <staticText>
                <reportElement x="122" y="-1" width="122" height="20"/>
                <box leftPadding="10" rightPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Number of orders]]></text>
            </staticText>
            <staticText>
                <reportElement x="244" y="-1" width="122" height="20"/>
                <box leftPadding="10" rightPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Total sum]]></text>
            </staticText>
            <staticText>
                <reportElement x="366" y="-1" width="149" height="20"/>
                <box leftPadding="10" rightPadding="10">
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Average sum of order]]></text>
            </staticText>
        </band>
    </pageHeader>
</jasperReport>

CSV . - SQL-.

iReport:

The report design

( iReport):

The result in iReport

JasperReports JasperReports.

, , . jasperforge.org:

In order to get an accurate data representation, the data in the data source should be already ordered according to the group expressions used in the report. One can either perform data sorting through the report query, or use the <sortField/> element.

+8

More articles:


All Articles