Excel output is a very popular choice among users for exporting report data from m-Power. When an Excel output is made from an m-Power report, the total line values in the generated output are raw values from the query rather than an Excel formula.
For developers who want their total lines in the Excel output to auto sum via the =SUM(range) function like this:
=SUM(A2:A10)this techblog post will show how to accomplish this using the m-Painter editor.
Requirements
The following points are required setup for this functionality to work in your m-Power report:
- Detail Level must be selected at runtime.
- The report template must be a Regular Report.
- Formatted Excel (outfmt=16) must be the output format chosen for Excel.
- The report may optionally have one selected subtotal dimension at runtime – that being Subtotal Level 1*.
*The auto sum functionality outlined in this post is not supported for reports with multiple dimension fields.
Walkthrough
Once the report is created, open m-Painter and go to the HTML source.
Step 1 – Initialize Freemarker variables
Find the comment <!-- END_REPORT_TABLE_HEADER --> and below this, copy and paste in the following Freemarker variables as shown in bold.
<!-- END_REPORT_TABLE_HEADER -->
<!--<#assign rowCounter = 0>-->
<!--<#assign firstSubtotal='Y'>-->
<!--<#assign detailOnly='Y'>-->
<!-- <#list report as section> -->These lines will initialize three Freemarker variables that will be needed for the auto sum functionality to work.
Step 2 – Add Freemarker to the Detail Level
Find where the detail level is printing out, which in contained entirely within these comments:
<!-- <#list section.detailRows as row> -->
...
...
<!-- </#list> -->Within this block, add the following code (in bold) right AFTER the detail row prints, which would immediately follow the <tr class="detailBackground" >...</tr> block.
<!-- <#list section.detailRows as row> -->
<tr id="level_[DETAIL]" class="level-row">
<td class="rsd" align="center" colspan="6">Detail Level</td>
</tr>
<tr class="detailBackground" data-level="detail">
<td class="ten text-right" nowrap="nowrap">${row.CNUM?html}</td>
<td class="ten text-right" nowrap="nowrap">${row.PNUM?html}</td>
<td class="ten text-right" nowrap="nowrap">${row.MTDSALES?html}</td>
<td class="ten text-right" nowrap="nowrap">${row.MTDCOST?html}</td>
</tr>
<!--<#if rowCounter = 0>
<#assign dataRange='=sum(C2:'/>
<#assign rowCounter = 2>
</#if>
<#assign rowCounter = rowCounter + 1>-->
<!-- </#list> -->This counter calculation logic will find the amount of detail rows returned within the given section of the report in order to define the appropriate cell range (ex. C2:C100) for the SUM() function in Excel.
The most important thing to notice with this code is various ranges for the SUM function are hardcoded to use column ‘C’. This is done because my MTDSALES field, which is being summed for Excel, will be the third column in the export. Meaning, it will be the ‘C’ column.
Further steps will also use column ‘C’ for defining ranges.
It is entirely possible to repeat the auto sum logic for my MTDCOST field, which is the fourth column in my m-Power report. You may duplicate this auto sum logic and refer to ‘D’ or whatever letter as needed to correspond to the same column in the Excel export.
Step 3 – Add Freemarker to the Subtotal Level
Find where the Subtotal Level 1 is printing out, which is contained within these comments:
<!-- <#if subTotal1.dsp != 0> -->
...
...
<!--</#if> -->Within this block, add the following code (in bold) right BEFORE the <tr class="sub1Background data-level="1"> tag.
<!-- <#if subTotal1.dsp != 0> -->
<tr id="level_[SHIPYEAR]" class="level-row">
<td class="rsd" align="center" colspan="6">Subtotal Level 1 by [SHIPYEAR]</td>
</tr>
<!--<#assign endOfDetailRange=rowCounter-1>
<#assign begOfNextDetailRange=rowCounter+1>
<#assign dataRange=dataRange+'C'+endOfDetailRange+',C'+begOfNextDetailRange+':'>
<#assign rowCounter = rowCounter + 1>
<#assign detailOnly='N'>--->
<!--<#if firstSubtotal='Y'>
<#assign subTotalDataRange='=sum(C2:C'+endOfDetailRange+')'>
<#else>
<#assign subTotalDataRange='=sum(C'+begOfNextRange+':C'+endOfDetailRange+')'>
</#if>
<#assign begOfNextRange=endOfDetailRange+2>-->
<tr class="sub1Background" data-level="1"> <td class="s1 text-right">${subTotal1.CNUM?html}</td> <td class="s1t">${lastRow.PNUM?html}</td>
<td class="s1 text-right">
<!--<#if page.outfmt==16>-->
${subTotalDataRange}
<!--<#else>-->
${subTotal1.MTDSALES?html}
<!--</#if>-->
</td>
<td class="s1 text-right">${subTotal1.MTDCOST?html}</td>
<!--<#assign subTotalDataRange=''>
<#assign firstSubtotal='N'>-->
</tr>
<!--</#if> -->These various lines of code are summed up here:
endOfDetailRange – Gets the row number from the last detail line before the subtotal prints out.
begOfNextDetailRange – Defines the row number from the first detail line after the subtotal has printed.
dataRange – concatenates the two previous variables to create the range needed for the =SUM() function that will be computed for the Grand Total level (not shown yet), based on the details.
What follows after is logic summarized as follows:
- IF the subtotal total line being printed is the first subtotal line of the report –
- Then the range for the SUM function is the first detail row, C2, combined with the end of the detail range.
- ELSE when the subtotal total line being printed is NOT the first subtotal line of the report –
- Then the range for the SUM function is the previous range’s last detail row plus two (skipping the subtotal row that prints).
Lastly, there is code to print out the computed auto sum function only when the user exports to Formatted Excel (outfmt=16). If not exporting to Excel, then the original value from the presentation layer will be printed in the cell.
Step 4 – Add Freemarker to the Grand Total Level
Find where the Grand Total level is printing out, which is contained within these comments:
<!-- <#if grandTotal.dsp != 0> -->
...
...
<!-- </#if> -->Within this block, add the following code (in bold) inside the table cell (<td>..</td>) where the value being auto summed is printing.
<!-- <#if grandTotal.dsp != 0> -->
<tr id="level_[GRAND]" class="level-row">
<td class="rsd" align="center" colspan="6">Grand Total Level</td>
</tr>
<tr class="grandBackground" data-level="0">
<td class="grand text-right">
Grand Total
</td>
<td class="grand text-right">
</td>
<td class="grand text-right">
<!--<#if page.outfmt==16>-->
<!--<#if detailOnly='N'>-->
${dataRange?keep_before_last(",")})
<!--<#else>-->
=sum(C2:C${rowCounter-1})
<!--</#if>-->
<!--<#else>-->
${grandTotal.MTDSALES?html}
<!--</#if>-->
</td>
<td class="grand text-right">
${grandTotal.MTDCOST?html}
</td>
</tr>
<!-- </#if> -->The parts in bold can be interpreted like this:
- IF the report is exported to Formatted Excel (outfmt=16) then
- IF this is only a detail report — no subtotals — assign the =SUM function on the Grand Total to the entire detail range ex. (C2:C300).
- ELSE assign the SUM function on the Grand Total line to the collection of detail ranges between the subtotal lines (ex. =SUM(C2:C84,C86:C144,C146:C227,C229:C300)
- ELSE when the report is not exported to Formatted Excel (outfmt=16) then print the original field value in the presentation.
Step 5 – Set the Excel button to Formatted Excel (outfmt=16)
Make sure if you have not already, to set your ‘Excel’ option to Formatted Excel. The autosum functionality will only be applicable for outfmt=16 and not the other Excel options.
After saving m-Painter you may now test and run the application.
Step 6 – Runtime
At runtime, the report when ran to the browser (HTML) will look absolutely no different – as if it was a stock report.
When Exported to Formatted Excel, both options will produce the auto sum functionality:
Detail Level only (with Grand Total):
Detail Level and Subtotal Level 1 (with Grand Total).
Formatting the Numeric Value
In order to specify the edit/format code of the auto sum cell, add the following attribute to the respective <th> column(s) in the m-Painter source:
<th data-col="MTDSALES" class="text-right" data-number-pattern="$#,##0.00">Other example of format codes include: “#,###”, “#,##0”, “$#,##0”, etc.
Additional Note
This technical blog article is intended for advanced technical users. The custom FreeMarker code provided is meant to introduce and guide users on how they might implement this functionality. Any additional assistance beyond the information provided here is outside the scope of normal hotline support. If you require further help, please contact mrc support to discuss potential consulting services.