当前位置： 文江博客话题详情

如何按日期过滤表（交叉表）中的数据

发布于 2024-12-02 04:38:10 字数 11632 浏览 2 评论 0原文

我需要一些帮助来使用 iReport 为此布局创建报告。我可以使用交叉表创建记录，但它会创建重复记录。

                   Male                      Female

日期

总计数

查询

SELECT table1.`gender` AS table1_gender,
     registration_details.`date_reg` AS registration_details_date_reg
FROM
     `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`

where 性别出现在表 1 中（candidate_id : pk） date_reg 存在于 Registration_details

jrxml 中：

<?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="report12" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <style name="Crosstab Data Text" hAlign="Center"/>
    <style name="table">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="New Dataset 1">
        <queryString language="SQL">
            <![CDATA[SELECT
     table1.`gender` AS table1_gender,
    registration_details.`date_reg` AS registration_details_date_reg
FROM
 `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]>
        </queryString>
        <field name="table1_gender" class="java.lang.String"/>
        <field name="registration_details_date_reg" class="java.sql.Date"/>
    </subDataset>
    <subDataset name="Table Dataset 1"/>
    <queryString language="SQL">
        <![CDATA[SELECT
     table1.`gender` AS table1_gender,
     registration_details.`date_reg` AS registration_details_date_reg
FROM
     `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]>
    </queryString>
    <field name="table1_gender" class="java.lang.String"/>
    <field name="date_reg" class="java.sql.Date"/>
    <detail>
        <band height="125" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="555" height="125"/>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="New Dataset 1">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="date_reg" width="70" totalPosition="End">
                    <bucket>
                        <bucketExpression class="java.lang.String"><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($F{date_reg})]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="25"/>
                                <textElement/>
                                <textFieldExpression class="java.lang.String"><![CDATA[$V{date_reg}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="70" height="25"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total date_reg]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="table1_gender" height="30" totalPosition="End">
                    <bucket>
                        <bucketExpression class="java.lang.String"><![CDATA[$F{table1_gender}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="30"/>
                                <textElement/>
                                <textFieldExpression class="java.lang.String"><![CDATA[$V{student_gender}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total student_gender]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="table1_genderMeasure" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{table1_gender}]]></measureExpression>
                </measure>
                <crosstabCell width="50" height="25">
                    <cellContents>
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell height="25" rowTotalGroup="date_reg">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="50" columnTotalGroup="student_gender">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell rowTotalGroup="date_reg" columnTotalGroup="table1_gender">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </detail>
</jasperReport>

当前显示所有记录，在创建报告时需要建议/帮助 用户可以通过提供日期来获取记录。

还可以多次打印单条记录。

我将不胜感激任何帮助。

原文

I need some help in creating report using iReport for this layout. I am able to create records using crosstab, but its creating duplicate records.

                   Male                      Female

Date

Total Count

Query

SELECT table1.`gender` AS table1_gender,
     registration_details.`date_reg` AS registration_details_date_reg
FROM
     `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`

where
gender is present in table1 (candidate_id : pk)
date_reg is present in registration_details

The jrxml:

<?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="report12" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <style name="Crosstab Data Text" hAlign="Center"/>
    <style name="table">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table 1_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="New Dataset 1">
        <queryString language="SQL">
            <![CDATA[SELECT
     table1.`gender` AS table1_gender,
    registration_details.`date_reg` AS registration_details_date_reg
FROM
 `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]>
        </queryString>
        <field name="table1_gender" class="java.lang.String"/>
        <field name="registration_details_date_reg" class="java.sql.Date"/>
    </subDataset>
    <subDataset name="Table Dataset 1"/>
    <queryString language="SQL">
        <![CDATA[SELECT
     table1.`gender` AS table1_gender,
     registration_details.`date_reg` AS registration_details_date_reg
FROM
     `table1` table1 INNER JOIN `date_reg` date_reg ON table1.`candidate_id` = registration_details.`candidate_id`]]>
    </queryString>
    <field name="table1_gender" class="java.lang.String"/>
    <field name="date_reg" class="java.sql.Date"/>
    <detail>
        <band height="125" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="555" height="125"/>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="New Dataset 1">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="date_reg" width="70" totalPosition="End">
                    <bucket>
                        <bucketExpression class="java.lang.String"><![CDATA[(new SimpleDateFormat("yyyy-MM-dd")).format($F{date_reg})]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="25"/>
                                <textElement/>
                                <textFieldExpression class="java.lang.String"><![CDATA[$V{date_reg}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="70" height="25"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total date_reg]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="table1_gender" height="30" totalPosition="End">
                    <bucket>
                        <bucketExpression class="java.lang.String"><![CDATA[$F{table1_gender}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="30"/>
                                <textElement/>
                                <textFieldExpression class="java.lang.String"><![CDATA[$V{student_gender}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total student_gender]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="table1_genderMeasure" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{table1_gender}]]></measureExpression>
                </measure>
                <crosstabCell width="50" height="25">
                    <cellContents>
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell height="25" rowTotalGroup="date_reg">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="50" columnTotalGroup="student_gender">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell rowTotalGroup="date_reg" columnTotalGroup="table1_gender">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
                            <textElement/>
                            <textFieldExpression class="java.lang.Integer"><![CDATA[$V{table1_genderMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </detail>
</jasperReport>

Currently its showing all the records , Need suggestions/help in creating report
Where a User will be able to fetch records by providing date.

also its printing single records multiple time.

I would appreciate any help.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

中性美 2024-12-09 04:38:10

要按日期过滤数据，您可以添加参数（java.lang.String 或 java.util.Date）类型并在查询中使用它。

示例：

<parameter name="dateParam" isForPrompting="false" class="java.lang.String"/>
...
<parameter name="dqlDateFilter" isForPrompting="false" class="java.lang.String"> 
   <defaultValueExpression ><![CDATA[$P{dateParam} == null ? "1=1" : "registration_details.date_reg=$P{dateParam}"]]></defaultValueExpression> 
</parameter>
...
<queryString language="SQL">SELECT.... FROM.... WHERE $P!{dqlDateFilter}</queryString>

您应该为您的案例编写正确的日期过滤器。

For filtering data by date you can add paramater (java.lang.String or java.util.Date) type and use it in the query.

The example:

<parameter name="dateParam" isForPrompting="false" class="java.lang.String"/>
...
<parameter name="dqlDateFilter" isForPrompting="false" class="java.lang.String"> 
   <defaultValueExpression ><![CDATA[$P{dateParam} == null ? "1=1" : "registration_details.date_reg=$P{dateParam}"]]></defaultValueExpression> 
</parameter>
...
<queryString language="SQL">SELECT.... FROM.... WHERE $P!{dqlDateFilter}</queryString>

You should write the correct date filter for your case.

回复 原文
~没有更多了~

关于作者

你另情深

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

夢野间

文章 0 评论 0

百度③文鱼

文章 0 评论 0

小草泠泠

文章 0 评论 0

zhuwenyan

文章 0 评论 0

weirdo

文章 0 评论 0

坚持沉默

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文