Jump to content
JasperReports Library 7.0 is now available ×

  • Displaying Data from Multiple Result Set Rows In A Single Report Line

    Tom C

    By Tom C

      • Features: Reports Version: v6.2 Product: Jaspersoft® Studio
     Share

    When having report query result set with more than one rows, the row record is retrieved into report detail band one row per report line. If users need to display all rows in one report line, they will have to change the report query to produce only one row containing tally information from multiple rows. This approach requires complex query coding expertise to redesign report query.

    An alternate approach is to use the original report query in a sub dataset to feed report List element to lay out multiple row query result horizontally in the report and in essence displaying them in a single report line.

    Below is a sample report template demonstrating such approach. User can review and test runt the report in TIBCO Jaspersoft® Studio v6.2 using any PostgreSQL database.

    Report design template (JRXML file):

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-01-31T14:37:17 -->
<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="Sample Report" pageWidth="940" pageHeight="140" orientation="Landscape"
              whenNoDataType="NoDataSection" columnWidth="900" leftMargin="20" rightMargin="20"
              topMargin="20" bottomMargin="20"
              uuid="3f9f3c7b-1c3d-4f6f-90b1-139c5536c0b1">
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <subDataset name="ListDataset" uuid="edbf3a16-018b-4b7b-88ab-8ff5dbb5100c">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/>
        <parameter name="Item" class="java.lang.Integer">
            <defaultValueExpression><![CDATA[4]]></defaultValueExpression>
        </parameter>
        <queryString>
            <![CDATA[select * from (
select 1 as Item, 33 as Amount, 133 as TotalAmount
union all
select 2 as Item, 14 as Amount, 214 as TotalAmount
union all
select 3 as Item, 7 as Amount, 307 as TotalAmount
union all
select 4 as Item, 1 as Amount, 401 as TotalAmount
) t
where item < $P{Item}+1]]>
        </queryString>
        <field name="Item" class="java.lang.Integer"/>
        <field name="Amount" class="java.lang.Integer"/>
        <field name="TotalAmount" class="java.lang.Integer"/>
    </subDataset>
    <parameter name="Item" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[4]]></defaultValueExpression>
    </parameter>
    <queryString language="SQL">
        <![CDATA[select 1 as dummy]]>
    </queryString>
    <field name="dummy" class="java.lang.Integer"/>
    <title>
        <band height="30" splitType="Stretch">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <frame>
                <reportElement mode="Opaque" x="0" y="0" width="900" height="30"
                               backcolor="#EBEBEB"
                               uuid="fee8b74b-d8e8-4396-b8ca-8dbda1f3b23c">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <property name="local_mesure_unitheight" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.height" value="px"/>
                </reportElement>
                <box>
                    <pen lineColor="#C8C8C8"/>
                    <topPen lineWidth="1.5" lineStyle="Solid" lineColor="#C8C8C8"/>
                    <leftPen lineWidth="1.5" lineStyle="Solid" lineColor="#C8C8C8"/>
                    <bottomPen lineWidth="1.5" lineStyle="Solid" lineColor="#C8C8C8"/>
                    <rightPen lineWidth="1.5" lineStyle="Solid" lineColor="#C8C8C8"/>
                </box>
                <staticText>
                    <reportElement x="10" y="5" width="880" height="20"
                                   uuid="7e62c516-cce4-4ea9-b0c1-84d93cb3b3c3"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font fontName="Arial" size="16" isBold="true"/>
                    </textElement>
                    <text><![CDATA[Sample Report Using List Element]]></text>
                </staticText>
                <image onErrorType="Blank">
                    <reportElement x="517" y="7" width="15" height="15"
                                   uuid="7bbe4b90-1816-472c-8ea2-166da40c6d75">
                        <property name="local_mesure_unitwidth" value="pixel"/>
                        <property name="com.jaspersoft.studio.unit.width" value="px"/>
                        <property name="local_mesure_unitheight" value="pixel"/>
                        <property name="com.jaspersoft.studio.unit.height" value="px"/>
                    </reportElement>
                    <imageExpression><![CDATA["repo:logo.png"]]></imageExpression>
                </image>
            </frame>
        </band>
    </title>
    <detail>
        <band height="25">
            <staticText>
                <reportElement mode="Transparent" x="100" y="5" width="175" height="20"
                               forecolor="#000000" backcolor="#FFFFFF"
                               uuid="eb6eaadc-687a-44a7-9f8e-626f264faca9">
                    <property name="local_mesure_unitx" value="pixel"/>
                    <property name="local_mesure_unity" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle"
                             rotation="None" markup="none">
                    <font fontName="Arial" size="12" isBold="true" isItalic="false"
                          isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica"
                          pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <text><![CDATA[Item 1]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Transparent" x="275" y="5" width="175" height="20"
                               forecolor="#000000" backcolor="#FFFFFF"
                               uuid="df1a9f03-0a9f-4c91-96b5-4c73a3e51aad">
                    <property name="local_mesure_unitx" value="pixel"/>
                    <property name="local_mesure_unity" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle"
                             rotation="None" markup="none">
                    <font fontName="Arial" size="12" isBold="true" isItalic="false"
                          isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica"
                          pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <text><![CDATA[Item 2]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Transparent" x="450" y="5" width="175" height="20"
                               forecolor="#000000" backcolor="#FFFFFF"
                               uuid="06bc3e1e-86a9-444a-b675-c15b52a6d2c2">
                    <property name="local_mesure_unitx" value="pixel"/>
                    <property name="local_mesure_unity" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle"
                             rotation="None" markup="none">
                    <font fontName="Arial" size="12" isBold="true" isItalic="false"
                          isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica"
                          pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                </textElement>
                <text><![CDATA[Item 3]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Transparent" x="625" y="5" width="175" height="20"
                               forecolor="#000000" backcolor="#FFFFFF"
                               uuid="732ea211-d523-45dd-a3b9-8f7dab7f14b6">
                    <property name="local_mesure_unitx" value="pixel"/>
                    <property name="local_mesure_unity" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
                    <font fontName="Arial" size="12" isBold="true" isItalic="false" isUnderline="false"
                          isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252"
                          isPdfEmbedded="false"/>
                </textElement>
                <text><![CDATA[Item 4]]></text>
            </staticText>
        </band>
        <band height="30">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <componentElement>
                <reportElement x="100" y="0" width="700" height="30" uuid="0ac8c6ba-05aa-4ce7-a3ff-a82565f93b3b">
                    <property name="com.jaspersoft.studio.unit.CONTENTS.width" value="pixel"/>
                </reportElement>
                <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"
                         printOrder="Horizontal">
                    <datasetRun subDataset="ListDataset" uuid="6b321b30-89e8-4ec9-af7e-c6e10b648bd6">
                        <datasetParameter name="Item">
                            <datasetParameterExpression><![CDATA[$P{Item}]]></datasetParameterExpression>
                        </datasetParameter>
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="175">
                        <textField>
                            <reportElement x="0" y="0" width="175" height="30"
                                           uuid="8da768a8-46c0-40f4-8773-56ed167ad667"/>
                            <box>
                                <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textElement textAlignment="Center" verticalAlignment="Middle">
                                <font fontName="Arial" size="20"/>
                            </textElement>
                            <textFieldExpression>
                                <![CDATA[$F{Amount}+" Total: "+$F{TotalAmount}]]>
                            </textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </detail>
</jasperReport>


    Report output:

    sample(1).png.f69a6550b1fc23891bce52840579eca7.png


    TTC-20160131

    sample(1).png.c6878fcda9d49ac5708faab95ee11199.png

     Share
    Go to entries

    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...

© 2024 Cloud Software Group,
Inc. All rights reserved.

Products

Explore