如何在Crosstab显示列，即使数据不存在 [英] How to show column at Crosstab even the data is absent

查看：123 发布时间：2018/11/19 20:14:11 jasper-reports

本文介绍了如何在Crosstab显示列，即使数据不存在的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有没有提示，如果我想从数组创建交叉表列，然后将更正信息插入交叉表？

例如，我有一个像在我的数据库下面：

客户---- activity_date ----活动|

customer1 - - 01-01-2016 ---- A |

customer1 ---- 01-03-2016 ---- B |

customer2 ---- 01-01-2016 ---- A |

当用户从JAN 16到MAR 16请求报告时，报告应如下所示：

客户---- 01-2016 ---- 02-2016 ---- 03-2016 |

customer1 ----活动数：1 ----活动数：0 ----活动数：1

customer2 ----活动数：1 ----活动数：0 ----活动数：0

总计----活动总和： 2 ----活动总和：0 ----活动数：1

目前的问题是，作为re是2016年2月没有数据，报告中缺少02-2016列。

有没有办法（比如scriptlet）创建一个数组作为列，然后当activity_date == column_date时告诉JasperReport插入正确的数据？

我正在使用Jaspersoft Studio。

解决方案

要显示没有日期的日期（在您的情况下没有活动），您应该使用数据源传递数据。 JasperReports 对任何日期范围或其他内容都不了解。它只需要一个数据。

问题

第一个问题是获取日期范围内的数据数据不存在

第二个 - 是为了避免在交叉表中显示'null'数据

解决方案

如果使用DB，您可以使用outter join和某个日期范围' generator'显示数据。

我们应该解决这个问题：

获取清单某段时间内的所有日期（天）。对于不同的RDBMS，语法将有所不同。

对于 PostgreSQL ，您可以在

注意：

我试图隐藏空行（带空值）帮助删除行空白，空白时空，打印表达式属性没有任何成功。挖掘源代码对我也没有帮助。

您可以在此处找到有关类似尝试隐藏交叉表中空记录的更多信息：

隐藏空行组
JasperReports

在交叉表中隐藏行

如何在没有数据时隐藏交叉表中的行

交叉表：禁止空列，保留行详细信息

也许是时候在Jaspersoft / Tibco社区投票支持这个新旧功能（隐藏空行）：）

Do anyone have hints, if I want to create crosstab columns from array, then insert the correction information into the crosstab?

For example, I have a table like below in my DB:

customer ---- activity_date ---- activity|

customer1 ---- 01-01-2016 ---- A|

customer1 ---- 01-03-2016 ---- B|

customer2 ---- 01-01-2016 ---- A|

When users request report from JAN 16 to MAR 16, the report should be shown like below:

customer ---- 01-2016 ---- 02-2016 ---- 03-2016 |

customer1 ---- Activity count: 1 ---- Activity count:0 ---- Activity count:1

customer2 ---- Activity count:1 ---- Activity count:0 ---- Activity count:0

total ---- Activity sum:2 ---- Activity sum:0 ---- Activity count:1

The current problem is that, as there is no data within Feb 2016, the column 02-2016 is missing in the report.

Is there any way (like scriptlet) to create an array as column, then tell the JasperReport the insert correct data when activity_date == column_date?

I'm using Jaspersoft Studio.

解决方案

To show dates without dates (without activities in your case) you should pass the data with datasource. The JasperReports does not know anything about any ranges of dates or something else. It is just require a data.

The problems

The first problem is to get data in date range even the data is absent
And the second one - is to avoid showing in crosstab 'null' data

Solution

In case using DB you can use outter join and some date range 'generator' to show the data.

We should solve this tasks:
1. Getting the list of all dates (days) in some period of time. For different RDBMS the syntax will be different.
  
  For PostgreSQL you can find the solution in Getting date list in a range in PostgreSQL post
  For MySQL - MySQL how to fill missing dates in range & generate days from date range For SQL Server - SQL Server: How to select all days in a date range even if no data exists for some days
2. Using left or right outter join.
3. Sorting data by date and data you want
In case using JavaBean datasources you hould do the same - add dates you need (without data) and sort data. We can skip implementing the data sorting and ask the JasperReports engine to do it for us.
The crosstab has one 'feature' - we can't hide the row with condition. Even we set all properties for hiding all textFields - the empty row will be drawn. If we try to use a filter on Crosstab our additional rows will be disappear. I think the good idea (in this pitiful situation) is to have special name for such row (we can also replace 0 with something better). In my sample it will be 'Not set'.

The sample

I've used the PostgreSQL in this sample.

The report's template

<?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="crss_dates_group" pageWidth="842" pageHeight="595" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="6886d70f-dbf3-4dfa-bbee-d5bc70b1c45d">
    <style name="Crosstab Data Text" hAlign="Center"/>
    <subDataset name="dsDates" uuid="9b8d3c04-25f5-40f0-b116-a77f8d2f7445">
        <queryString language="SQL">
            <![CDATA[SELECT  activityName, to_char(generate_series, 'YYYY-MM-DD') AS activityDate
FROM myTable RIGHT OUTER JOIN (SELECT (generate_series('2010-07-18', '2010-07-29', '1 day'::interval))::date) fake
ON to_char(activityDateFromMyTable, 'YYYY-MM-DD')=to_char(generate_series, 'YYYY-MM-DD') ORDER BY 2, 1]]>
        </queryString>
        <field name="activityName" class="java.lang.String"/>
        <field name="activityDate" class="java.lang.String"/>
        <group name="activityDateGroup">
            <groupExpression><![CDATA[$F{activityDate}]]></groupExpression>
        </group>
    </subDataset>
    <title>
        <band height="79" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="802" height="79" uuid="d39eef3f-aada-406f-99ee-1d2ce2bde5c8"/>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="dsDates" uuid="619c0498-512a-4f23-9f1e-6a5d7cfa986d">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="activityName" width="95" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{activityName}]]></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="95" height="25" uuid="c25490b6-a836-41fb-a36c-a7ebb211bf03"/>
                                <textFieldExpression><![CDATA[$V{activityName} == null ? "Not set" : $V{activityName}]]></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="95" height="25" uuid="12efa463-c4a3-4120-b0e2-0664856cc616"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total by Date]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="activityDate" height="30" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{activityDate}]]></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="61" height="30" uuid="5b931464-5a7a-4e57-a51a-3d687c0a4130"/>
                                <textFieldExpression><![CDATA[$V{activityDate}]]></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" uuid="227c77a6-b1c1-485f-95cf-95b43bc95920"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total by Activity]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="activityNameMeasure" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{activityName}]]></measureExpression>
                </measure>
                <crosstabCell width="61" height="25">
                    <cellContents>
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="61" height="25" uuid="b8a8aacb-58d1-447a-9628-7f045b039f9f"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="61" height="25" rowTotalGroup="activityName">
                    <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="61" height="25" uuid="02e88c9a-e9cc-4674-9301-21676d3f33bc"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="50" columnTotalGroup="activityDate">
                    <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" uuid="d39d1353-61a6-4041-96d6-2065bae0041b"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell rowTotalGroup="activityName" columnTotalGroup="activityDate">
                    <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" uuid="09aa0d57-5cfa-4e78-af85-0e718c0fee44"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </title>
</jasperReport>

The result in iReport

Notes:

I've tried to hide empty row (with null) with help of "Remove Line With Blank", "Blank When Null", "Print When Expression" properties without any success. The digging in source codes does not help me too.

You can find more info about similar attempts to hide empty records in crosstab here:

Maybe it is a time to vote for this "new old" feature (hiding empty row) at Jaspersoft/Tibco community :)

这篇关于如何在Crosstab显示列，即使数据不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

如何在Crosstab显示列，即使数据不存在 [英] How to show column at Crosstab even the data is absent

问题描述

客户---- activity_date ----活动|

客户---- 01-2016 ---- 02-2016 ---- 03-2016 |

问题

解决方案

注意：

customer ---- activity_date ---- activity|

customer ---- 01-2016 ---- 02-2016 ---- 03-2016 |

The problems

Solution

The sample

The report's template

The result in iReport

Notes:

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

如何在Crosstab显示列，即使数据不存在 [英] How to show column at Crosstab even the data is absent

问题描述

客户---- activity_date ----活动|

客户---- 01-2016 ---- 02-2016 ---- 03-2016 |

问题

解决方案

注意：

customer ---- activity_date ---- activity|

customer ---- 01-2016 ---- 02-2016 ---- 03-2016 |

The problems

Solution

The sample

The report's template

The result in iReport

Notes:

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭