Sometime there is a need to add subtotals in BIP RTF template. Though we know that this can be handled in SQL query data model but still there are times especially to modify some existing reports. So lets see with an example.
Oracle support note on Subtotals
Table of Contents
Example of subtotal requirement
Sample Xml File to group and add Grand Total and SubTotal
<?xml version = '1.0' encoding = 'utf-8'?>
<!--Generated by Oracle Analytics Publisher -Dataengine, datamodel:_Custom_Financials_Revenue_Management_RKS_TEst_xdm -->
<DATA_DS><PCONTRACTNO>10</PCONTRACTNO><PASOF>2022-09-26T00:00:00.000+00:00</PASOF>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>12</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-06-07</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-06-07</REVENUE_START_DATE><REVENUE_END_DATE>2020-06-06</REVENUE_END_DATE><DAYS>-842 0:0:0.0</DAYS><CONTRACT_LIABLITY>33</CONTRACT_LIABLITY><SUM_ASSET>600</SUM_ASSET><SUM_LIABLITY>-1440</SUM_LIABLITY><NET_CA_CL>-1440</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>22</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-06-07</CUSTOMER_CONTRACT_DATE><CONTRACT_ASSET>0</CONTRACT_ASSET><CONTRACT_LIABLITY>2</CONTRACT_LIABLITY><SUM_ASSET>0</SUM_ASSET><SUM_LIABLITY>0</SUM_LIABLITY><NET_CA_CL>0</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>10</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>1</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2018-12-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-02-01</REVENUE_START_DATE><REVENUE_END_DATE>2019-02-28</REVENUE_END_DATE><DAYS>-1306 0:0:0.0</DAYS><CONTRACT_LIABLITY>330</CONTRACT_LIABLITY><SUM_ASSET>329.71</SUM_ASSET><SUM_LIABLITY>-329.71</SUM_LIABLITY><NET_CA_CL>-329.71</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>10</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>3</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2018-12-01</CUSTOMER_CONTRACT_DATE><CONTRACT_ASSET>299.88</CONTRACT_ASSET><CONTRACT_LIABLITY>0</CONTRACT_LIABLITY><SUM_ASSET>0</SUM_ASSET><SUM_LIABLITY>0</SUM_LIABLITY><NET_CA_CL>299.88</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>10</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>6</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2018-12-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2018-12-01</REVENUE_START_DATE><REVENUE_END_DATE>2018-12-31</REVENUE_END_DATE><DAYS>-1365 0:0:0.0</DAYS><CONTRACT_LIABLITY>110</CONTRACT_LIABLITY><SUM_ASSET>109.9</SUM_ASSET><SUM_LIABLITY>-109.9</SUM_LIABLITY><NET_CA_CL>-109.9</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>10</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>8</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2018-12-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2018-12-01</REVENUE_START_DATE><REVENUE_END_DATE>2019-11-29</REVENUE_END_DATE><DAYS>-1032 0:0:0.0</DAYS><CONTRACT_LIABLITY>359.88</CONTRACT_LIABLITY><SUM_ASSET>330.55</SUM_ASSET><SUM_LIABLITY>-359.56</SUM_LIABLITY><NET_CA_CL>-359.56</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>10</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>9</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2018-12-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-01-01</REVENUE_START_DATE><REVENUE_END_DATE>2019-01-31</REVENUE_END_DATE><DAYS>-1334 0:0:0.0</DAYS><CONTRACT_LIABLITY>220</CONTRACT_LIABLITY><SUM_ASSET>219.8</SUM_ASSET><SUM_LIABLITY>-219.8</SUM_LIABLITY><NET_CA_CL>-219.8</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1001</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>1001</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-03-01</CUSTOMER_CONTRACT_DATE><CONTRACT_ASSET>9</CONTRACT_ASSET><CONTRACT_LIABLITY>0</CONTRACT_LIABLITY><SUM_ASSET>9</SUM_ASSET><SUM_LIABLITY>-9.99</SUM_LIABLITY><NET_CA_CL>-0.99</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1001</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>1002</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-03-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-03-01</REVENUE_START_DATE><REVENUE_END_DATE>2019-03-31</REVENUE_END_DATE><DAYS>-1275 0:0:0.0</DAYS><CONTRACT_LIABLITY>0</CONTRACT_LIABLITY><SUM_ASSET>350</SUM_ASSET><SUM_LIABLITY>-350</SUM_LIABLITY><NET_CA_CL>-350</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1001</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>1003</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-03-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-03-01</REVENUE_START_DATE><REVENUE_END_DATE>2020-02-29</REVENUE_END_DATE><DAYS>-940 0:0:0.0</DAYS><CONTRACT_LIABLITY>100</CONTRACT_LIABLITY><SUM_ASSET>450.93</SUM_ASSET><SUM_LIABLITY>-455.88</SUM_LIABLITY><NET_CA_CL>-455.88</NET_CA_CL>
</G_1>
<G_1>
<CUSTOMER_CONTRACT_NUMBER>1001</CUSTOMER_CONTRACT_NUMBER><PERF_OBLIGATION_NUMBER>1004</PERF_OBLIGATION_NUMBER><CUSTOMER_CONTRACT_DATE>2019-03-01</CUSTOMER_CONTRACT_DATE><REVENUE_START_DATE>2019-04-01</REVENUE_START_DATE><REVENUE_END_DATE>2019-04-30</REVENUE_END_DATE><DAYS>-1245 0:0:0.0</DAYS><CONTRACT_LIABLITY>5</CONTRACT_LIABLITY><SUM_ASSET>400</SUM_ASSET><SUM_LIABLITY>-400</SUM_LIABLITY><NET_CA_CL>-400</NET_CA_CL>
</G_1>
<CS_CONTRACT_ASSET>308.88</CS_CONTRACT_ASSET>
<CS_LIABLITY>-3674.84</CS_LIABLITY>
<CS_NET_CA_CL>-3365.96</CS_NET_CA_CL>
</DATA_DS>
Suppose we have an xml data coming from BIP data model like above and we need to add subtotal in RTF template for field CONTRACT_LIABLITY which is present in the repeating group G1.
How to add subtotals
First from the MS Word BiPublisher add in add load the xml data to the rtf file. Then create a simple table using the table wizard for the data.
Output of standard table
Now lets add subtotals and Grand totals
Now we need to do some modifications to add the sub totals.
Click on the fields menu icon to popup the fields browser
Now add the subtotal to the field contract_liablity and check checkbox on Grouping
For grand total add a table row after the group loop ends and add the sum of contract liablity without checking the on grouping checkbox.
And your output will be like below
Similarly you can add subtotals and grand totals for any field in your datamodel XML group.
Thanks for reading.
Recommended post
If condition in BIP RTF template
more on Fusion apps tech
