Jaspersoft-Studio-Master-Child-Table-2

Report

Jaspersoft Studio Table 实现主子表（主表及明细子表）

报表案例下载：下载

1.表及数据环境

学生表

CREATE TABLE Student(
SID NUMBER,
NAME VARCHAR(20)
);

教师表

CREATE  TABLE Teacher(
TID NUMBER,
NAME VARCHAR(20)
);

课程信息表

CREATE  TABLE CourseList(
CID NUMBER,
TID NUMBER,
NAME VARCHAR(20)
);

学科成绩表

CREATE  TABLE CourseScore(
SID NUMBER,
CID NUMBER,
SCORE NUMBER
);

模拟数据插入

-- Student 表
INSERT INTO STUDENT ("SID", "NAME") VALUES ('1', '张三');
INSERT INTO STUDENT ("SID", "NAME") VALUES ('2', '李四');
-- Teacher 表
INSERT INTO TEACHER ("TID", "NAME") VALUES ('1', '老王');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('2', '老李');
INSERT INTO TEACHER ("TID", "NAME") VALUES ('3', '老张');
-- CourseList 表
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('1', '1', '语文');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('2', '2', '英语');
INSERT INTO CourseList ("CID", "TID", "NAME") VALUES ('3', '3', '数学');
-- CourseScore 表
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '1', '50');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '2', '60');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('1', '3', '80');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '1', '90');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '2', '88');
INSERT INTO CourseScore ("SID", "CID", "SCORE") VALUES ('2', '3', '86');

2.创建一个 jrmxl 文件

数据源如下

SELECT 1 FROM DUAL

创建 Parameter 参数（该参数为最外层 Parameter）

3.创建表格所需的数据源

创建数据源1 Parameter 参数 （参数属性与外层一致）

数据源 1

SELECT SID "学号", NAME "姓名" FROM Student WHERE SID = $P{学号}

数据源 2

SELECT  cl.Name "课程名",t.Name "任课老师名", cs.SCORE "成绩" FROM  
CourseScore  cs LEFT JOIN  Student s ON s.SID = cs.SID LEFT JOIN CourseList cl ON cs.CID = cl.CID 
LEFT JOIN Teacher t ON t.TID = cl.TID WHERE s.SID = $P{学号}

4.拖拽控件如下

5.进入Source 页面

在 jr-table –> datasetRun 中 添加 datasetParameter 参数

添加参数如下

<datasetParameter name="学号">
    <datasetParameterExpression><![CDATA[$P{学号}]]></datasetParameterExpression>
</datasetParameter>

6.运行界面

7.Source 源代码参考

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.18.1.final using JasperReports Library version 6.18.1-9d75d1969e774d4f179fb3be8401e98a0e6d1611  -->
<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="table_learn" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e73d3eea-fcc0-4b66-8aa0-392a70792dee">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="本地 Oracle 数据库"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="Table_TH" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" backcolor="#96CCFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="MAIN" uuid="0319fbc5-02b0-4555-81f2-f1d81d584a75">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="本地 Oracle 数据库"/>
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <parameter name="学号" class="java.math.BigDecimal"/>
        <queryString language="plsql">
            <![CDATA[SELECT SID "学号", NAME "姓名" FROM Student WHERE SID = $P{学号}]]>
        </queryString>
        <field name="学号" class="java.math.BigDecimal">
            <property name="com.jaspersoft.studio.field.name" value="学号"/>
            <property name="com.jaspersoft.studio.field.label" value="学号"/>
        </field>
        <field name="姓名" class="java.lang.String">
            <property name="com.jaspersoft.studio.field.name" value="姓名"/>
            <property name="com.jaspersoft.studio.field.label" value="姓名"/>
        </field>
    </subDataset>
    <subDataset name="SUB" uuid="e52e547b-3a33-419c-8860-0a2a7d5f5296">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="本地 Oracle 数据库"/>
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <parameter name="学号" class="java.math.BigDecimal"/>
        <queryString>
            <![CDATA[SELECT  cl.Name "课程名",t.Name "任课老师名", cs.SCORE "成绩" FROM  CourseScore  cs LEFT JOIN  Student s ON s.SID = cs.SID LEFT JOIN CourseList cl ON cs.CID = cl.CID LEFT JOIN Teacher t ON t.TID = cl.TID WHERE s.SID = $P{学号}]]>
        </queryString>
        <field name="课程名" class="java.lang.String">
            <property name="com.jaspersoft.studio.field.name" value="课程名"/>
            <property name="com.jaspersoft.studio.field.label" value="课程名"/>
        </field>
        <field name="任课老师名" class="java.lang.String">
            <property name="com.jaspersoft.studio.field.name" value="任课老师名"/>
            <property name="com.jaspersoft.studio.field.label" value="任课老师名"/>
        </field>
        <field name="成绩" class="java.math.BigDecimal">
            <property name="com.jaspersoft.studio.field.name" value="成绩"/>
            <property name="com.jaspersoft.studio.field.label" value="成绩"/>
        </field>
    </subDataset>
    <parameter name="学号" class="java.math.BigDecimal"/>
    <queryString>
        <![CDATA[SELECT 1 FROM DUAL]]>
    </queryString>
    <field name="1" class="java.math.BigDecimal">
        <property name="com.jaspersoft.studio.field.name" value="1"/>
        <property name="com.jaspersoft.studio.field.label" value="1"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="110" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <staticText>
                <reportElement x="0" y="10" width="100" height="30" uuid="3a67dfa7-ea1e-4035-88c0-209a5c6bc25b"/>
                <text><![CDATA[主表]]></text>
            </staticText>
            <componentElement>
                <reportElement x="0" y="50" width="200" height="60" uuid="69f48096-e92b-41c7-8d85-cf36d13be9cf">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                </reportElement>
                <jr:table 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="MAIN" uuid="55453540-2072-436d-93ea-9863d065b016">
                        <datasetParameter name="学号">
                            <datasetParameterExpression><![CDATA[$P{学号}]]></datasetParameterExpression>
                        </datasetParameter>
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:column width="100" uuid="68ac2e3b-33ef-41b5-a6aa-0153480150b0">
                        <jr:columnHeader height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="100" height="30" backcolor="#FFFFFF" uuid="3d59468b-c264-4dc2-b5ee-0a4b1720f69b"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <text><![CDATA[学号]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="100" height="30" uuid="00590376-c4bb-47d3-9baf-8d1c0bd08d9a"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <textFieldExpression><![CDATA[$F{学号}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="100" uuid="b17ffc48-f9db-4f31-bfdc-aed90406dc50">
                        <jr:columnHeader height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="100" height="30" uuid="45db3a1a-5930-4a0b-84c4-42de421d7f8c"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <text><![CDATA[姓名]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="100" height="30" uuid="c8ee747d-13b1-44b3-ac22-48e65f987c4a"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <textFieldExpression><![CDATA[$F{姓名}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
        <band height="110">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <staticText>
                <reportElement x="0" y="10" width="100" height="30" uuid="092e0d6c-8466-430c-a450-ce1f2326759a"/>
                <text><![CDATA[子表]]></text>
            </staticText>
            <componentElement>
                <reportElement x="0" y="50" width="200" height="60" uuid="2b7dd10c-2c21-4646-aa88-1394d861e0cd">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                </reportElement>
                <jr:table 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="SUB" uuid="ba0c3aed-bd77-4469-98fd-1f4ea11570d0">
                        <datasetParameter name="学号">
                            <datasetParameterExpression><![CDATA[$P{学号}]]></datasetParameterExpression>
                        </datasetParameter>
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:column width="66" uuid="485f23ba-c351-4aae-b0e3-7af70fad0e99">
                        <jr:columnHeader height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="a3877fa0-7821-4570-b3f3-0e14e34a1050"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <text><![CDATA[课程名]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="8b4d8f46-d86c-4468-8ead-206983a7dbc7"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <textFieldExpression><![CDATA[$F{课程名}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="86613c2b-1d9b-4823-a9a6-d9a638c5e6ac">
                        <jr:columnHeader height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="4097c759-a903-4f28-b90c-5a360d39ca8a"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <text><![CDATA[任课老师名]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="42d783c3-1b63-4524-9083-dcf53f8c38e1"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <textFieldExpression><![CDATA[$F{任课老师名}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="f12152c9-428e-4923-bac3-d77d1201f727">
                        <jr:columnHeader height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="530c4c81-4d43-4bef-b3f5-80f605ac9a46"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <text><![CDATA[成绩]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="aff45f42-8888-4f46-8d63-28e23e6da3f0"/>
                                <box>
                                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                                </box>
                                <textFieldExpression><![CDATA[$F{成绩}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
</jasperReport>

Title:Jaspersoft-Studio-Master-Child-Table-2

Author:

Created:2021-12-04, 13:18:00

Updated:2022-11-18, 09:11:42

Full URL:http://yoursite.com/2021/12/04/Jaspersoft-Studio-Master-Child-Table-2/

License: "CC BY-NC-SA 4.0" Keep Link & Author if Distribute.

Contents
  1. 1. Jaspersoft Studio Table 实现主子表（主表及明细子表）
    1. 1.1. 1.表及数据环境
    2. 1.2. 2.创建一个 jrmxl 文件
    3. 1.3. 3.创建表格所需的数据源
    4. 1.4. 4.拖拽控件如下
    5. 1.5. 5.进入Source 页面
    6. 1.6. 6.运行界面
    7. 1.7. 7.Source 源代码参考
|