| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Jasper - Groups | ||
First example
Demonstration of the agregate function sum(...).
<?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 = "groups"
pageWidth = "595"
pageHeight = "845"
columnWidth = "595"
leftMargin = "0"
rightMargin = "0"
topMargin = "0"
bottomMargin = "0"
>
<queryString language="SQL">
<![CDATA[
select
SUM(VAL),
client
from (
select 1 val, 'Small Joe' client from dual union all
select 3 val, 'Small Joe' client from dual union all
select 2 val, 'Small Joe' client from dual union all
select 4 val, 'Small Joe' client from dual union all
select 2301 val, 'Big Inc' client from dual union all
select 895 val, 'Big Inc' client from dual union all
select 3333 val, 'Big Inc' client from dual
) foo_bar
group by
client
]]>
</queryString>
<field name="SUM(VAL)" class="java.math.BigDecimal"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="CLIENT" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<group name="CLIENT">
<groupExpression><![CDATA[$F{CLIENT}]]></groupExpression>
<groupHeader>
<band height="13" splitType="Stretch">
<staticText> <reportElement x= "0" y="0" width="139" height="13" /> <text><![CDATA[Client:]]></text> </staticText>
<textField> <reportElement x="139" y="0" width="416" height="13" /> <textFieldExpression class="java.lang.String"><![CDATA[$F{CLIENT}]]></textFieldExpression> </textField>
</band>
</groupHeader>
<groupFooter> <band height="8" splitType="Stretch"> </band> </groupFooter>
</group>
<title> </title>
<detail>
<band height="20" splitType="Stretch">
<staticText><reportElement x= "0" y="0" width="139" height="20"/> <textElement> </textElement> <text><![CDATA[Val per Client:]]></text> </staticText>
<textField> <reportElement x="139" y="0" width="416" height="20"/> <textElement> </textElement> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{SUM(VAL)}]]></textFieldExpression> </textField>
</band>
</detail>
<pageFooter> </pageFooter>
<summary> </summary>
</jasperReport>
Second example
A bit more elaborate use of the <groupHeader> and <groupFooter> tags.
<?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 ="report name"
pageWidth = "595"
pageHeight = "845"
columnWidth = "595"
leftMargin = "0"
rightMargin = "0"
topMargin = "0"
bottomMargin = "0"
>
<queryString language="SQL">
<![CDATA[select
count(1),
sum(val),
itm,
city,
clnt
from (
--
select 1 val, 'Boston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
select 3 val, 'Boston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
select 2 val, 'Boston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
select 4 val, 'Boston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
--
select 8 val, 'Chigaco' city, 'Apple' itm, 'Small Joe' clnt from dual union all
select 12 val, 'Chigaco' city, 'Apple' itm, 'Small Joe' clnt from dual union all
--
select 22 val, 'Houston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
select 19 val, 'Houston' city, 'Apple' itm, 'Small Joe' clnt from dual union all
--
-----
--
select 4 val, 'Boston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
select 6 val, 'Boston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
select 2 val, 'Boston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
--
select 18 val, 'Chigaco' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
select 22 val, 'Chigaco' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
--
select 50 val, 'Houston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
select 26 val, 'Houston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
select 24 val, 'Houston' city, 'Lemon' itm, 'Small Joe' clnt from dual union all
--
----
----
--
select 2301 val, 'Boston' city, 'Apple' itm, 'Big Inc' clnt from dual union all
select 895 val, 'Boston' city, 'Apple' itm, 'Big Inc' clnt from dual union all
select 3333 val, 'Boston' city, 'Apple' itm, 'Big Inc' clnt from dual union all
--
select 5001 val, 'Chigaco' city, 'Apple' itm, 'Big Inc' clnt from dual union all
select 4999 val, 'Chigaco' city, 'Apple' itm, 'Big Inc' clnt from dual union all
select 5005 val, 'Chigaco' city, 'Apple' itm, 'Big Inc' clnt from dual union all
select 4995 val, 'Chigaco' city, 'Apple' itm, 'Big Inc' clnt from dual union all
--
select 10000 val, 'Houston' city, 'Apple' itm, 'Big Inc' clnt from dual union all
--
----
--
select 2002 val, 'Boston' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 1001 val, 'Boston' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 1441 val, 'Boston' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
--
select 5001 val, 'Chigaco' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 4999 val, 'Chigaco' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 5005 val, 'Chigaco' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 4995 val, 'Chigaco' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
--
select 1010 val, 'Houston' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
select 10101 val, 'Houston' city, 'Lemon' itm, 'Big Inc' clnt from dual union all
--
select 8000 val, 'Houston' city, 'Pear' itm, 'Big Inc' clnt from dual union all
select 1111 val, 'Houston' city, 'Pear' itm, 'Big Inc' clnt from dual union all
select 888 val, 'Houston' city, 'Pear' itm, 'Big Inc' clnt from dual
--
)
--
foo_bar
group by
clnt,
city,
itm
order by
clnt,
city,
itm]]>
</queryString>
<field name="COUNT(1)" class="java.math.BigDecimal"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="SUM(VAL)" class="java.math.BigDecimal"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="ITM" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="CITY" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="CLNT" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<group name="CLNT"> <groupExpression><![CDATA[$F{CLNT}]]></groupExpression>
<groupHeader>
<band height="27" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="200" height="27" />
<text><![CDATA[Group header for clnt]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="27"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{CLNT}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter>
<band height="20" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="200" height="20" />
<text><![CDATA[Group Footer for clnt]]></text>
</staticText>
</band>
</groupFooter>
</group>
<group name="CITY"> <groupExpression><![CDATA[$F{CITY}]]></groupExpression>
<groupHeader>
<band height="28" splitType="Stretch">
<staticText>
<reportElement mode="Opaque" x="20" y="0" width="200" height="27" />
<text><![CDATA[Group header for city]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="27"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{CITY}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter>
<band height="90" splitType="Stretch">
<staticText>
<reportElement x="20" y="0" width="200" height="20" />
<text><![CDATA[Group Footer for city]]></text>
</staticText>
</band>
</groupFooter>
</group>
<group name="ITM"> <groupExpression><![CDATA[$F{ITM}]]></groupExpression>
<groupHeader>
<band height="28" splitType="Stretch">
<staticText>
<reportElement x="40" y="0" width="139" height="27"/>
<text><![CDATA[group header for itm]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="27"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{ITM}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter>
<band height="40" splitType="Stretch">
<staticText>
<reportElement x="40" y="0" width="200" height="20" />
<text><![CDATA[Group Footer for itm]]></text>
</staticText>
</band>
</groupFooter>
</group>
<detail>
<band height="40" splitType="Stretch">
<staticText>
<reportElement x="60" y="0" width="179" height="20"/>
<text><![CDATA[COUNT(1)]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="20"/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COUNT(1)}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="60" y="20" width="179" height="20"/>
<text><![CDATA[SUM(VAL)]]></text>
</staticText>
<textField>
<reportElement x="139" y="20" width="416" height="20"/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{SUM(VAL)}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter> </columnFooter>
<pageFooter> </pageFooter>
<summary> </summary>
</jasperReport>
Third example
Demonstrates grouping on two rather than on field/attribute/column/whatever the term du jour is. It places the group's value for those two fields into one rather than two group headers.
<?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 ="report name"
pageWidth = "300"
pageHeight = "100"
columnWidth = "300"
leftMargin = "0"
rightMargin = "0"
topMargin = "0"
bottomMargin = "0"
>
<queryString language="SQL">
<![CDATA[select
count(1),
sum(val),
itm,
city,
clnt
from (
--
select 1 val, 'Boston' city, 'apple' itm, 'Small Joe' clnt from dual union all
select 3 val, 'Boston' city, 'apple' itm, 'Small Joe' clnt from dual union all
select 2 val, 'Boston' city, 'apple' itm, 'Small Joe' clnt from dual union all
select 4 val, 'Boston' city, 'apple' itm, 'Small Joe' clnt from dual union all
--
select 8 val, 'Chigaco' city, 'apple' itm, 'Small Joe' clnt from dual union all
select 12 val, 'Chigaco' city, 'apple' itm, 'Small Joe' clnt from dual union all
--
select 22 val, 'Houston' city, 'apple' itm, 'Small Joe' clnt from dual union all
select 19 val, 'Houston' city, 'apple' itm, 'Small Joe' clnt from dual union all
--
-----
--
select 4 val, 'Boston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
select 6 val, 'Boston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
select 2 val, 'Boston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
--
select 18 val, 'Chigaco' city, 'lemon' itm, 'Small Joe' clnt from dual union all
select 22 val, 'Chigaco' city, 'lemon' itm, 'Small Joe' clnt from dual union all
--
select 50 val, 'Houston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
select 26 val, 'Houston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
select 24 val, 'Houston' city, 'lemon' itm, 'Small Joe' clnt from dual union all
--
----
----
--
select 2301 val, 'Boston' city, 'apple' itm, 'Big Inc' clnt from dual union all
select 895 val, 'Boston' city, 'apple' itm, 'Big Inc' clnt from dual union all
select 3333 val, 'Boston' city, 'apple' itm, 'Big Inc' clnt from dual union all
--
select 5001 val, 'Chigaco' city, 'apple' itm, 'Big Inc' clnt from dual union all
select 4999 val, 'Chigaco' city, 'apple' itm, 'Big Inc' clnt from dual union all
select 5005 val, 'Chigaco' city, 'apple' itm, 'Big Inc' clnt from dual union all
select 4995 val, 'Chigaco' city, 'apple' itm, 'Big Inc' clnt from dual union all
--
select 10000 val, 'Houston' city, 'apple' itm, 'Big Inc' clnt from dual union all
--
----
--
select 2002 val, 'Boston' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 1001 val, 'Boston' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 1441 val, 'Boston' city, 'lemon' itm, 'Big Inc' clnt from dual union all
--
select 3001 val, 'Chigaco' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 2999 val, 'Chigaco' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 2005 val, 'Chigaco' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 1995 val, 'Chigaco' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 1111 val, 'Chigaco' city, 'lemon' itm, 'Big Inc' clnt from dual union all
--
select 1010 val, 'Houston' city, 'lemon' itm, 'Big Inc' clnt from dual union all
select 10101 val, 'Houston' city, 'lemon' itm, 'Big Inc' clnt from dual union all
--
select 8000 val, 'Houston' city, 'pear' itm, 'Big Inc' clnt from dual union all
select 1111 val, 'Houston' city, 'pear' itm, 'Big Inc' clnt from dual union all
select 888 val, 'Houston' city, 'pear' itm, 'Big Inc' clnt from dual union all
--
select 0 val, 'Houston' city, 'lemon' itm, 'XXXXXXXXX' clnt from dual union all
select 0 val, 'X-Ray' city, 'lemon' itm, 'XXXXXXXXX' clnt from dual
--
)
--
foo_bar
group by
clnt,
city,
itm
order by
clnt,
city,
itm]]>
</queryString>
<field name="COUNT(1)" class="java.math.BigDecimal"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="SUM(VAL)" class="java.math.BigDecimal"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="ITM" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="CITY" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<field name="CLNT" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field>
<group name="CLNT"> <groupExpression><![CDATA[$F{CLNT}]]></groupExpression></group>
<group name="CITY"> <groupExpression><![CDATA[$F{CITY}]]></groupExpression>
<groupHeader>
<band height="20" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="200" height="20" />
<text><![CDATA[Group header for clnt and City]]></text>
</staticText>
<textField>
<reportElement x="200" y="0" width="416" height="20" />
<textFieldExpression class="java.lang.String"><![CDATA[$F{CLNT} + " in " + $F{CITY}]]></textFieldExpression>
</textField>
<!--
<textField>
<reportElement mode="Opaque" x="220" y="20" width="300" height="27" />
<textFieldExpression class="java.lang.String"><![CDATA[$F{CITY}]]></textFieldExpression>
</textField>
-->
</band>
</groupHeader>
<groupFooter>
<band height="30" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="200" height="20" />
<text><![CDATA[Group footer for clnt and city]]></text>
</staticText>
</band>
</groupFooter>
</group>
<group name="ITM"> <groupExpression><![CDATA[$F{ITM}]]></groupExpression>
<groupHeader>
<band height="28" splitType="Stretch">
<staticText>
<reportElement x="40" y="0" width="139" height="27"/>
<text><![CDATA[group header for itm]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="27"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{ITM}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter>
<band height="40" splitType="Stretch">
<staticText>
<reportElement x="40" y="0" width="200" height="20" />
<text><![CDATA[Group Footer for itm]]></text>
</staticText>
</band>
</groupFooter>
</group>
<detail>
<band height="40" splitType="Stretch">
<staticText>
<reportElement x="60" y="0" width="179" height="20"/>
<text><![CDATA[COUNT(1)]]></text>
</staticText>
<textField>
<reportElement x="139" y="0" width="416" height="20"/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COUNT(1)}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="60" y="20" width="179" height="20"/>
<text><![CDATA[SUM(VAL)]]></text>
</staticText>
<textField>
<reportElement x="139" y="20" width="416" height="20"/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{SUM(VAL)}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter> </columnFooter>
<pageFooter> </pageFooter>
<summary> </summary>
</jasperReport>
See also other Jasper examples.
|