| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Jasper - Pivot Queries | ||
|
The table from which is selected:
create table pivot_test as select 1 col_1, 2 col_2, 3 col_3, 4 col_4, 5 col_5, 6 col_6, 7 col_7, 8 col_8, '1st group' grp from dual union all select 10 col_1, 20 col_2, 30 col_3, 40 col_4, 50 col_5, 60 col_6, 70 col_7, 80 col_8, '1st group' grp from dual union all select -1 col_1, -2 col_2, -3 col_3, -4 col_4, -5 col_5, -6 col_6, -7 col_7, -8 col_8, '1st group' grp from dual union all -- select 1.1 col_1, 2.2 col_2, 3.3 col_3, 4.4 col_4, 5.5 col_5, 6.6 col_6, 7.7 col_7, 8.8 col_8, '2st group' grp from dual;
Uses a sub dataset in order to create a sort-of pivot query. See also this link.
<?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="pivot_test_1"
pageWidth="595"
pageHeight="842"
columnWidth="535"
leftMargin="20"
rightMargin="20"
topMargin="20"
bottomMargin="20">
<subDataset name="dataset1">
<parameter name="GRP" class="java.lang.String"/>
<queryString>
<![CDATA[
---------------
select
col_1, col_2, col_3, col_4
from pivot_test
where
grp = $P{GRP}
---------------
]]>
</queryString>
<field name="COL_1" class="java.math.BigDecimal"/>
<field name="COL_2" class="java.math.BigDecimal"/>
<field name="COL_3" class="java.math.BigDecimal"/>
<field name="COL_4" class="java.math.BigDecimal"/>
</subDataset>
<subDataset name="dataset2">
<parameter name="GRP" class="java.lang.String"/>
<queryString>
<![CDATA[
---------------------------
select
col_5, col_6, col_7, col_8
from pivot_test
where
grp = $P{GRP}
-------------------
]]>
</queryString>
<field name="COL_5" class="java.math.BigDecimal"/>
<field name="COL_6" class="java.math.BigDecimal"/>
<field name="COL_7" class="java.math.BigDecimal"/>
<field name="COL_8" class="java.math.BigDecimal"/>
</subDataset>
<queryString language="SQL">
<![CDATA[
-----------------------------------
select distinct grp from pivot_test
-----------------------------------
]]>
</queryString>
<field name="GRP" class="java.lang.String"/>
<group name="GRP">
<groupExpression><![CDATA[$F{GRP}]]></groupExpression>
<groupHeader>
<band height="28" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="100" height="14"/>
<textFieldExpression class="java.lang.String"><![CDATA["GRP: " + $F{GRP}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
<groupFooter><band height="14"/></groupFooter>
</group>
<detail>
<band height="42">
<componentElement>
<reportElement x="0" y="14" width="555" height="14"/>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="dataset1">
<datasetParameter name="GRP">
<datasetParameterExpression><![CDATA[$F{GRP}]]></datasetParameterExpression>
</datasetParameter>
</datasetRun>
<jr:listContents height="14">
<textField pattern="###0.00;-###0.00">
<reportElement x="50" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_1}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="150" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_2}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="250" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_3}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="350" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_4}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
<staticText>
<reportElement x="50" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_1]]></text>
</staticText>
<staticText>
<reportElement x="150" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_3]]></text>
</staticText>
<staticText>
<reportElement x="250" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_2]]></text>
</staticText>
<staticText>
<reportElement x="350" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_4]]></text>
</staticText>
</band>
<band height="42">
<componentElement>
<reportElement x="0" y="14" width="555" height="14"/>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="dataset2">
<datasetParameter name="GRP">
<datasetParameterExpression><![CDATA[$F{GRP}]]></datasetParameterExpression>
</datasetParameter>
</datasetRun>
<jr:listContents height="14">
<textField pattern="###0.00;-###0.00">
<reportElement x="50" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_5}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="150" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_6}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="250" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_7}]]></textFieldExpression>
</textField>
<textField pattern="###0.00;-###0.00">
<reportElement x="350" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{COL_8}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
<staticText>
<reportElement x="50" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_5]]></text>
</staticText>
<staticText>
<reportElement x="150" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_6]]></text>
</staticText>
<staticText>
<reportElement x="250" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_7]]></text>
</staticText>
<staticText>
<reportElement x="350" y="0" width="100" height="14"/>
<textElement textAlignment='Right'/>
<text><![CDATA[COL_8]]></text>
</staticText>
</band>
</detail>
</jasperReport>
See also other Jasper examples.
|