BI Publisher RTF模板：动态列表格如何显示多行数据

问题背景 #

在BI Publisher的RTF模板中创建表格，以ACCOUNTING_DATE为维度设置动态列，聚合子分类账表中的UNROUNDED_ACCOUNTED_DR数据。按旧教程配置动态列后，模板仅返回一行数据，但数据模型的XML输出中存在多个OBLIGATION_NUMBER对应的行。尝试基于CONTRACT_NUMBER分组创建循环，多次尝试均报错或无返回值，需调整模板格式解决。

当前模板结构 #

XML数据片段 #

<?xml version = '1.0' encoding = 'utf-8'?>
<!--Generated by Oracle Analytics Publisher -Dataengine, datamodel:_Data_Model_xdm -->
<DATA_DS>
    <P_STARTDATE>2018-01-01T00:00:00.000+00:00</P_STARTDATE>
    <P_ENDDATE>2024-01-31T00:00:00.000+00:00</P_ENDDATE>
    <P_CONTRACT_NUMBER>69022</P_CONTRACT_NUMBER>
    <G_1>
        <CUSTOMER_NAME>Customer #1</CUSTOMER_NAME>
        <ACCOUNT_NUMBER>1000</ACCOUNT_NUMBER>
        <LEGAL_ENTITY>Company ABC</LEGAL_ENTITY>
        <CONTRACT_NUMBER>69022</CONTRACT_NUMBER>
        <OBLIGATION_NUMBER>72043</OBLIGATION_NUMBER>
        <ITEM>2018</ITEM>
        <ITEM_DESCRIPTION>Widget 1</ITEM_DESCRIPTION>
        <PLAN_START_DATE>12-01-23</PLAN_START_DATE>
        <PLAN_END_DATE>11-30-24</PLAN_END_DATE>
        <CONTRACT_LIABILITY_ACCOUNT>2000.10.000.000000.202000.0000.00000.00000</CONTRACT_LIABILITY_ACCOUNT>
        <CONTRACT_REVENUE_ACCOUNT>2000.10.510.170000.403000.0000.00000.00000</CONTRACT_REVENUE_ACCOUNT>
        <ORIGINAL_BALANCE>150</ORIGINAL_BALANCE>
        <REVENUE_RECOGNIZED>25</REVENUE_RECOGNIZED>
        <OPEN_BALANCE>125</OPEN_BALANCE>
        <UNROUNDED_ACCOUNTED_DR>12.5</UNROUNDED_ACCOUNTED_DR>
        <REVENUE_DETERMINATION>PIT</REVENUE_DETERMINATION>
        <ACCOUNTING_DATE>12-31-23</ACCOUNTING_DATE>
    </G_1>
    <G_1>
        <CUSTOMER_NAME>Customer #1</CUSTOMER_NAME>
        <ACCOUNT_NUMBER>1000</ACCOUNT_NUMBER>
        <LEGAL_ENTITY>Company ABC</LEGAL_ENTITY>
        <CONTRACT_NUMBER>69022</CONTRACT_NUMBER>
        <OBLIGATION_NUMBER>72065</OBLIGATION_NUMBER>
        <ITEM>337</ITEM>
        <ITEM_DESCRIPTION>Widget 2</ITEM_DESCRIPTION>
        <PLAN_START_DATE>12-01-23</PLAN_START_DATE>
        <PLAN_END_DATE>11-30-24</PLAN_END_DATE>
        <CONTRACT_LIABILITY_ACCOUNT>2000.10.000.000000.202000.0000.00000.00000</CONTRACT_LIABILITY_ACCOUNT>
        <CONTRACT_REVENUE_ACCOUNT>2000.10.510.170000.403000.0000.00000.00000</CONTRACT_REVENUE_ACCOUNT>
        <ORIGINAL_BALANCE>695</ORIGINAL_BALANCE>
        <REVENUE_RECOGNIZED>115.84</REVENUE_RECOGNIZED>
        <OPEN_BALANCE>579.16</OPEN_BALANCE>
        <UNROUNDED_ACCOUNTED_DR>57.92</UNROUNDED_ACCOUNTED_DR>
        <REVENUE_DETERMINATION>PIT</REVENUE_DETERMINATION>
        <ACCOUNTING_DATE>12-31-23</ACCOUNTING_DATE>
    </G_1>
    <G_1>
        <CUSTOMER_NAME>Customer #1</CUSTOMER_NAME>
        <ACCOUNT_NUMBER>1000</ACCOUNT_NUMBER>
        <LEGAL_ENTITY>Company ABC</LEGAL_ENTITY>
        <CONTRACT_NUMBER>69022</CONTRACT_NUMBER>
        <OBLIGATION_NUMBER>72075</OBLIGATION_NUMBER>
        <ITEM>1841</ITEM>
        <ITEM_DESCRIPTION>Widget 3</ITEM_DESCRIPTION>
        <CONTRACT_LIABILITY_ACCOUNT>4000.10.000.000000.202000.0000.00000.00000</CONTRACT_LIABILITY_ACCOUNT>
        <CONTRACT_REVENUE_ACCOUNT>4000.10.900.417000.405000.0000.00000.00000</CONTRACT_REVENUE_ACCOUNT>
        <ORIGINAL_BALANCE>100</ORIGINAL_BALANCE>
        <REVENUE_RECOGNIZED>100</REVENUE_RECOGNIZED>
        <OPEN_BALANCE>0</OPEN_BALANCE>
        <UNROUNDED_ACCOUNTED_DR>100</UNROUNDED_ACCOUNTED_DR>
        <REVENUE_DETERMINATION>PIT</REVENUE_DETERMINATION>
        <ACCOUNTING_DATE>12-14-23</ACCOUNTING_DATE>
    </G_1>
</DATA_DS>

尝试的代码 #

第1列第1行 #

<?horizontal-break-table:1?>

第1列第2行 #

<?for-each-group@cell://G_1;CONTRACT_NUMBER?>
    <?variable@incontext:CON;CONTRACT_NUMBER?>
    <?CUSTOMER_NAME?>

    <?for-each@cell://current-group();OBLIGATION_NUMBER?>
        <?OBLIGATION_NUMBER?>
        <?ITEM?>
        <?ITEM_DESCRIPTION?>
        <?PLAN_START_DATE?>
        <?PLAN_END_DATE?>
        <?CONTRACT_LIABILITY_ACCOUNT?>
        <?CONTRACT_REVENUE_ACCOUNT?>
        <?ORIGINAL_BALANCE?>
        <?REVENUE_RECOGNIZED?>
        <?OPEN_BALANCE?>
        <?REVENUE_DETERMINATION?>
        <?ACCOUNTING_DATE?>
        <?sum(current-group()[OBLIGATION_NUMBER=current()/OBLIGATION_NUMBER]/UNROUNDED_ACCOUNTED_DR)?>
    <?end for-each@cell?>

<?end for-each-group@cell?>

解决方案 #

核心问题分析 #

1. 原模板中<?for-each-group@section:G_1;CONTRACT_NUMBER?>使用@section会将每个合同编号单独分页，且仅生成一行数据，未遍历同合同下的多个OBLIGATION_NUMBER。
2. 动态列的聚合逻辑未关联当前行的OBLIGATION_NUMBER，导致数据匹配错误。

调整后的模板结构 #

表头部分（保持动态列逻辑不变）

表体部分（修正循环与聚合逻辑）

在<tbody>标签内，先按CONTRACT_NUMBER分组，再遍历组内的每个OBLIGATION_NUMBER对应的G_1节点：

<tbody>
<?for-each-group:G_1;CONTRACT_NUMBER?>
    <?variable@incontext:CURRENT_CONTRACT;CONTRACT_NUMBER?>
    <?for-each:current-group()?>
    <tr>
        <td><?CUSTOMER_NAME?></td>
        <td><?ACCOUNT_NUMBER?></td>
        <td><?LEGAL_ENTITY?></td>
        <td><?CONTRACT_NUMBER?></td>
        <td><?OBLIGATION_NUMBER?></td>
        <td><?ITEM?></td>
        <td><?ITEM_DESCRIPTION?></td>
        <td><?PLAN_START_DATE?></td>
        <td><?PLAN_END_DATE?></td>
        <td><?CONTRACT_LIABILITY_ACCOUNT?></td>
        <td><?CONTRACT_REVENUE_ACCOUNT?></td>
        <td><?ORIGINAL_BALANCE?></td>
        <td><?REVENUE_RECOGNIZED?></td>
        <td><?OPEN_BALANCE?></td>
        <td><?REVENUE_DETERMINATION?></td>
        <td>
            <?for-each-group@cell:../G_1;ACCOUNTING_DATE?>
                <?sum(current-group()[CONTRACT_NUMBER=$CURRENT_CONTRACT and OBLIGATION_NUMBER=current()/../OBLIGATION_NUMBER]/UNROUNDED_ACCOUNTED_DR)?>
            <?end for-each-group?>
        </td>
    </tr>
    <?end for-each?>
<?end for-each-group?>
</tbody>

关键调整点 #

1. 移除@section，改用普通for-each-group分组合同编号，避免分页导致的单行限制。
2. 在合同分组内，用for-each:current-group()遍历每个OBLIGATION_NUMBER对应的行数据，确保每个义务编号生成一行。
3. 动态列聚合时，同时匹配当前行的CONTRACT_NUMBER和OBLIGATION_NUMBER，精准计算对应日期的UNROUNDED_ACCOUNTED_DR总和。

内容的提问来源于stack exchange，提问作者Lindsay A.