Adding Grand Total and SubTotal in RTF template BI Publisher

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

Example of subtotal requirement

image 11 SubTotal in RTF template 1

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.

image 3 SubTotal in RTF template 2
Notice the data Set its taking the repeating group.

image 4 SubTotal in RTF template 3
Standard table structure when a table is added from table wizard

Output of standard table

image 5 SubTotal in RTF template 4
Standard output of bip 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

image 7 SubTotal in RTF template 5

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.

image 8 SubTotal in RTF template 6

And your output will be like below

image 9 SubTotal in RTF template 7

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

Bursting in Fusion BIP

more on Fusion apps tech

wpsbutton
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart