java - 在 JasperReports (iReport) 中对数据集的连续数字进行分组

Question

我的 jasper 报告中有一个查询，由用户给定的输入生成的结果集之一可能是以下数据:

memberId stockNo refineryCode constantSerialNo  serialNo
45       1       IAR          A-                98729
45       1       IAR          A-                98730
45       1       IAR          A-                98731
45       1       IAR          A-                98733
45       1       IAR          A-                98734
45       1       IAR          A-                98736
45       1       IAR          A-                98737
45       1       IAR          A-                98738
45       1       IAR          A-                98739

如果序列号是连续的，我想在一行上显示这些数据。因此，如果我想在报告中显示上述数据，我必须按以下方式显示它们:

memberId stockNo refineryCode constantSerialNo  serialNo
45       1       IAR          A-                98729 - 98731
45       1       IAR          A-                98733 - 98734
45       1       IAR          A-                98736 - 98739

我知道可能有一些解决方案在 sql 上使用游标或在 OOP 语言(如 Java)中使用 ORM 并发送到 jasper 报告。然而，出于好奇，我想问一下是否有使用 iReport 的表达式或组或任何其他我现在没有想到的东西的动态解决方案，这可以让我的生活更轻松。

Answer 1

这是一种方法，假设 serialNo 是某种数值而不是字符串。

1. sql 查询结果的数据集必须按 serialNo 排序，如上所示。
2. 使用以下组表达式创建一个报告组:$V{GroupCount} == new BigDecimal(0) ? $F{序列号}:$F{序列号}.subtract($V{GroupCount})。这将在每次 serialNo 序列中出现间隙时创建一个新组。
3. 创建一个变量 GroupCount 来计算当前组中连续 serialNo 值的数量。此变量用于跟踪下一个预期的 serialNo 值是什么。
4. 创建一个变量 StartRange，它将保存给定范围内连续 serialNo 值的起始值。该值应在新组开始时重置并具有以下值:$V{GroupCount}.intValue() == 1 ? $F{ID}:$V{StartRange}.
5. 将 serialNo 字段的 Evaluation Time 值设置为 ReportGroup。将以下值放入其中:$V{StartRange}+"- "+ $F{serialNo}。
6. 将 Detail 带的 Print When Expression 更改为以下内容:new Boolean($V{GroupCount}.intValue() == 1)。

我已经在下面发布了完整的 jrxml 代码。

<?xml version="1.0" encoding="utf-8"?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN"
"http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport name="Group_By_Consecutive" columnCount="1" printOrder="Vertical" orientation="Portrait" pageWidth="595" pageHeight="842" columnWidth="535" columnSpacing="0" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20" whenNoDataType="NoPages" isTitleNewPage="false" isSummaryNewPage="false">
  <property name="ireport.scriptlethandling" value="0" />
  <property name="ireport.encoding" value="UTF-8" />
  <import value="java.util.*" />
  <import value="net.sf.jasperreports.engine.*" />
  <import value="net.sf.jasperreports.engine.data.*" />
  <queryString>
    <![CDATA[select 1 as id, 'Name 1' as name from dual union all
select 2 as id, 'Name 2' as name from dual union all
select 3 as id, 'Name 3' as name from dual union all
select 4 as id, 'Name 4' as name from dual union all
select 6 as id, 'Name 6' as name from dual union all
select 7 as id, 'Name 7' as name from dual union all
select 8 as id, 'Name 8' as name from dual union all
select 9 as id, 'Name 9' as name from dual union all
select 10 as id, 'Name 10' as name from dual union all
select 11 as id, 'Name 11' as name from dual union all
select 14 as id, 'Name 14' as name from dual union all
select 15 as id, 'Name 15' as name from dual union all
select 16 as id, 'Name 16' as name from dual union all
select 17 as id, 'Name 17' as name from dual union all
select 23 as id, 'Name 23' as name from dual union all
select 24 as id, 'Name 24' as name from dual union all
select 25 as id, 'Name 25' as name from dual union all
select 26 as id, 'Name 26' as name from dual union all
select 27 as id, 'Name 27' as name from dual union all
select 28 as id, 'Name 28' as name from dual]]>
</queryString>
  <field name="ID" class="java.math.BigDecimal" />
  <field name="NAME" class="java.lang.String" />
  <variable name="GroupCount" class="java.math.BigDecimal" resetType="Group" resetGroup="Consecutive" calculation="Count">
    <variableExpression>
      <![CDATA[$F{ID}]]>
</variableExpression>
    <initialValueExpression>
      <![CDATA[new BigDecimal(0)]]>
</initialValueExpression>
  </variable>
  <variable name="StartRange" class="java.math.BigDecimal" resetType="Report" calculation="Nothing">
    <variableExpression>
      <![CDATA[$V{GroupCount}.intValue() == 1 ? $F{ID}:$V{StartRange}]]>
</variableExpression>
  </variable>
  <group name="Consecutive">
    <groupExpression>
      <![CDATA[$V{GroupCount} == new BigDecimal(0) ? $F{ID}:$F{ID}.subtract($V{GroupCount})]]>
</groupExpression>
    <groupHeader>
      <band height="0" isSplitAllowed="true"></band>
    </groupHeader>
    <groupFooter>
      <band height="0" isSplitAllowed="true"></band>
    </groupFooter>
  </group>
  <background>
    <band height="0" isSplitAllowed="true"></band>
  </background>
  <title>
    <band height="0" isSplitAllowed="true"></band>
  </title>
  <pageHeader>
    <band height="0" isSplitAllowed="true"></band>
  </pageHeader>
  <columnHeader>
    <band height="18" isSplitAllowed="true">
      <staticText>
        <reportElement x="0" y="0" width="200" height="18" key="staticText-1" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <text>
          <![CDATA[serialNo]]>
</text>
      </staticText>
      <staticText>
        <reportElement x="200" y="0" width="100" height="18" key="staticText-3" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <text>
          <![CDATA[Name]]>
</text>
      </staticText>
      <staticText>
        <reportElement x="300" y="0" width="100" height="18" key="staticText-4" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <text>
          <![CDATA[MetaData(Ignore)]]>
</text>
      </staticText>
    </band>
  </columnHeader>
  <detail>
    <band height="18" isSplitAllowed="true">
      <printWhenExpression>
        <![CDATA[new Boolean($V{GroupCount}.intValue() == 1)]]>
</printWhenExpression>
      <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
        <reportElement x="200" y="0" width="100" height="18" key="textField" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <textFieldExpression class="java.lang.String">
          <![CDATA[$F{NAME}]]>
</textFieldExpression>
      </textField>
      <textField isStretchWithOverflow="false" pattern="##0.00" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="Consecutive" hyperlinkType="None" hyperlinkTarget="Self">
        <reportElement x="0" y="0" width="200" height="18" key="textField" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <textFieldExpression class="java.lang.String">
          <![CDATA[$V{StartRange}+" - " + $F{ID}]]>
</textFieldExpression>
      </textField>
      <textField isStretchWithOverflow="false" pattern="##0.00" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self">
        <reportElement x="300" y="0" width="100" height="18" key="textField" />
        <box></box>
        <textElement>
          <font />
        </textElement>
        <textFieldExpression class="java.math.BigDecimal">
          <![CDATA[$V{GroupCount}]]>
</textFieldExpression>
      </textField>
    </band>
  </detail>
  <columnFooter>
    <band height="0" isSplitAllowed="true"></band>
  </columnFooter>
  <pageFooter>
    <band height="0" isSplitAllowed="true"></band>
  </pageFooter>
  <summary>
    <band height="0" isSplitAllowed="true"></band>
  </summary>
</jasperReport>