Overview
As a consultant, one of the most common scenarios I see is customers who have datasets with one-to-many relationships.
To understand what a one-to-many-relationship is, imagine a m-Power Regular Report template with an order header table that is joined to an order line items table. At runtime, the report prints a row for every line item in the order, meaning the fields from the header record will be repeated (duplicated).
Often times developers will opt to show or hide repeating data, and this is sufficient for their reporting needs. But what happens when a repeated value is numeric and needs to be counted only once for further subtotaling in the report?
This techblog post will address that question and teach you how to address this situation via Freemarker.
If you are not familiar with Apache Freemarker, it is strongly encouraged to first read this documentation here to understand basic syntax and usage.
Example Report
Observe the following tables, which have a one-to-many relationship.
One header record in the ORDERHEAD table…
Is linked to multiple records in the ORDERDET table…
A report built over these two tables would net a result similar to what is shown in Figure 3. The report has the detail level, two subtotal levels, and a grand total (not shown).
The report is structured as such:
Detail Level
Subtotal Level 2 – Order Number (ONUM)
Subtotal Level 1 – Order Type (OTYPE)
Grand Total
Observe the Order Amount column, which is a value from the header table. The amount is naturally repeated for any order with more than one line item and subsequently summed on the subtotal lines of the report.
Using Order #300158 as an example, the order amount to print on the subtotal by order number line is $4,471. But because there are two line items for this order, the order amount sums to $9,482.
To appropriate show $4,471 on the order subtotal, first we’ll go the Field Settings of the data model and specify the Order Amount’s aggregate option as “No Subtotal”. Once done, rebuild the report.
While this appropriately gives the right amount wanted on the Total for #300158 and #301506 lines (Subtotal Level 2), it does not give the desired total on the “Total for EDI Orders” line (Subtotal Level 1).
This is because as a “No Subtotal” value, the report just prints the order amount from the very last line item’s order amount which is $25,843.
The total amount for EDI orders ($30,584) will need to be summed behind the scenes and printed in that cell.
$4,471 + $ 25,843 = $30,584.
Using Freemarker, we will be able to do the summation in a custom variable.
The idea here will be that every time we reach a subtotal by Order Number (subtotal level 2), we will add that order’s amount to the previous total. Once we reach the subtotal by Order Type (subtotal level 1) the summed value can be printed. The report will repeat this process until all the different orders subtotals lines and the order type subtotal lines have printed.
Steps
1. In the HTML source of the report, find the <!-- <#list report as section> --> comment. This is where the data table execution begins.
2. Create a custom Freemarker variable above this comment, that will store the summed amount for subtotal level 1. In this example, that variable is called s1_amount and is initialized to a value of 0.
<!-- END_SORT_TABLE_HEADER -->
<!-- END_REPORT_TABLE_HEADER -->
<!--<#assign s1_amount = 0 >-->
<!-- <#list report as section> -->
<!-- <#assign lastRow = section.lastDetail> -->
<!-- <#assign noSubtotal = section.lastDetail> -->3. Further down in the HTML source, find the subtotal level 2 section. In this block add another assign statement that will add the current order amount (lastRow.OAMOUNT) to the current value of s1_amount.
<!-- <#assign subTotal2 = section.subtotals[2]> -->
<!-- <#if subTotal2.dsp != 0> -->
<!--<#assign s1_amount = s1_amount + lastRow.OAMOUNT_o >-->
<tr id="level_[ONUM]" class="level-row">
<td class="rsd" align="center" colspan="5">Subtotal Level 2 by [ONUM]</td>
</tr>
<tr class="sub2Background" data-level="2">
<td class="s2 td_left"></td>
<td class="s2 td_right">Total for #${lastRow.ONUM?html}</td>
<td nowrap="nowrap" class=""></td>
<td class="s2"> </td>
<td class="s2 text-right">${lastRow.OAMOUNT?html}</td>
</tr>
<!--</#if> -->Each time the report reaches a Subtotal Level 2 line it will loop through the above block of code and add the current subtotal line’s order amount (lastRow.OAMOUNT_o) to the previously stored total.
Once each Subtotal Level 2 line has been looped through, the final total of ${s1_amount} will known and can be printed on Subtotal Level 1.
4. To reference a custom Freemarker variable, simply use the ${variablename}in the HTML source, where ‘variablename’ is whatever name given to your custom variable.
<td class="s1 text-right">${s1_amount}</td>From the m-Painter GUI, I can easily insert that value as well:
5. While back in the HTML source, edit the Subtotal Level 1 section code. After the s1_amount has printed in the report, the value of s1_amount will need to be initialized back to 0.
<!-- <#assign subTotal1 = section.subtotals[1]> -->
<!-- <#if subTotal1.dsp != 0> -->
<tr id="level_[OTYPE]" class="level-row">
<td class="rsd" align="center" colspan="5">Subtotal Level 1 by [OTYPE]</td>
</tr>
<tr class="sub1Background" data-level="1">
<td class="s1 td_left">Total for ${lastRow.OTYPE?html} orders</td>
<td class="s1 td_right"> </td>
<td nowrap="nowrap"></td>
<td class="s1"> </td>
<td class="s1 text-right">${s1_amount}</td>
</tr>
<!--<#assign s1_amount = 0 >-->
<!--</#if> -->5. Save m-Painter.
6. The grand total value will also need to be computed in a similar fashion. This time, a new variable named grand_amount will be created, right after the s1_amount variable.
<!-- END_SORT_TABLE_HEADER -->
<!-- END_REPORT_TABLE_HEADER -->
<!--<#assign s1_amount = 0 >-->
<!--<#assign grand_amount = 0 >-->
<!-- <#list report as section> -->
<!-- <#assign lastRow = section.lastDetail> -->
<!-- <#assign noSubtotal = section.lastDetail> -->7. Further down in the HTML source, find the subtotal level 1 section again. In this block add another assign statement that will add the subtotal’s amount (s1_amount) to the current total (grand_amount).
<!-- <#assign subTotal1 = section.subtotals[1]> -->
<!-- <#if subTotal1.dsp != 0> -->
<!--<#assign grand_amount = grand_amount + s1_amount >-->
<tr id="level_[OTYPE]" class="level-row">
<td class="rsd" align="center" colspan="5">Subtotal Level 1 by [OTYPE]</td>
</tr>
<tr class="sub1Background" data-level="1">
<td class="s1 td_left">Total for ${lastRow.OTYPE?html} orders</td>
<td class="s1 td_right"> </td>
<td nowrap="nowrap"></td>
<td class="s1"> </td>
<td class="s1 text-right"> ${s1_amount}</td>
</tr>
<!--<#assign s1_amount = 0 >-->
<!--</#if> -->Each time the report reaches a Subtotal Level 1 line it will loop through the above block of code and add the current subtotal line’s order amount to the previously stored total.
Once each Subtotal Level 1 line has been looped through, the final total of ${grand_amount} will known and can be printed on the Grand Total level.
8. Reference ${grand_amount} on the Grand Total level of the report.
<td class="grand text-right">${grand_amount}</td>Save m-Painter again.
Results
Here I will show the report output. The first picture shows the report ran with details:
The two EDI type orders total $30,584 ($4,471+$25,843 = $30,584)
The two FAX type orders total $27,958. ($27,003 + 954 = $27,958).
Now, here is the same report without the Detail Level included:
The Grand Total across all order types is $124,083 ($30,584 + $27,958 + $36,883 + $28,656).