BI Publisher - How to get rows to repeat in a table w/ dynamic column on RTF template

Question

I have a table where I am pivoting the data in the last column by accounting date as (ACCOUNTING_DATE) and a debit from one of the subledger tables as (UNROUNDED_ACCOUNTED_DR).

I found an old tutorial on how to make dynamic columns so this is what I followed. https://www.youtube.com/watch?v=Zu11uJ03S9Y I'm not very experienced with template customization so I'd appreciate some help understanding how to adjust the formatting.

This is the overall structure of my table with how it's currently formatted:

Customer Name <?horizontal-break-table:1?>	Account Number	Legal Entity	Contract 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	<?for-each-group@column:G_1;ACCOUNTING_DATE?><?ACCOUNTING_DATE?><?end for-each-group?>
<?for-each-group@section:G_1;CONTRACT_NUMBER?><?variable@incontext:CON;CONTRACT_NUMBER?><?CUSTOMER_NAME?>	<?ACCOUNT_NUMBER?>	<?LEGAL_ENTITY?>	<?CONTRACT_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?>	<?for-each-group@cell://G_1;ACCOUNTING_DATE?><?if:count(current-group()[CONTRACT_NUMBER=$CON])?><?sum(current-group()[CONTRACT_NUMBER=$CON]/UNROUNDED_ACCOUNTED_DR)?><?end if?><?end for-each-group?><?end for-each?>

At present, it returns one line. However, the XML output from my data model has more lines by Obligation_Number, so I am trying to get the remaining lines to display.

This is the current output:

Here is a snippet of theXML data:

<?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>

I tried creating a for-each loop by Contract_Number group but different iterations of this produces errors to where it does not run/return values. I've never done anything advanced like this in a template so I'm unsure how to modify the table.

Column #1, First Row:

<?horizontal-break-table:1?>

Column #1, Second Row:

<?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?>

Answer 1

You can use Word (BI Publisher) Table wizard or you can make your own table. Here is a small sample with your data and with calculated column as last.

... and the result