=SUM(range)<\/code> function<\/a> like this:<\/p>\n\n\n\n=SUM(A2:A10)<\/code><\/pre>\n\n\n\nthis techblog post will show how to accomplish this using the m-Painter editor.<\/p>\n\n\n\n
Requirements<\/h2>\n\n\n\n
The following points are required setup for this functionality to work in your m-Power report:<\/p>\n\n\n\n
\n- Detail Level must be selected at runtime. <\/li>\n\n\n\n
- The report template must be a Regular Report.<\/li>\n\n\n\n
- Formatted Excel (outfmt=16) must be the output format chosen for Excel. <\/li>\n\n\n\n
- The report may optionally have one selected subtotal dimension at runtime – that being Subtotal Level 1*<\/strong>. <\/li>\n<\/ul>\n\n\n\n
Walkthrough<\/h2>\n\n\n\n
Once the report is created, open m-Painter and go to the HTML source.<\/p>\n\n\n\n
Step 1 – Initialize Freemarker variables<\/h3>\n\n\n\n
Find the comment <!-- END_REPORT_TABLE_HEADER --><\/code> and below this, copy and paste in the following Freemarker variables as shown in bold<\/strong>.<\/p>\n\n\n\n<!-- END_REPORT_TABLE_HEADER -->\n<!--<#assign rowCounter = 0>-->\n<!--<#assign firstSubtotal='Y'>-->\n<!--<#assign detailOnly='Y'>--><\/strong>\n<!-- <#list report as section> --><\/code><\/pre>\n\n\n\nThese lines will initialize three Freemarker variables that will be needed for the auto sum functionality to work. <\/p>\n\n\n\n
Step 2 – Add Freemarker to the Detail Level<\/h3>\n\n\n\n
Find where the detail level is printing out, which in contained entirely within these comments:<\/p>\n\n\n\n
<!-- <#list section.detailRows as row> -->\n...\n...\n<!-- <\/#list> --><\/code><\/pre>\n\n\n\nWithin this block, add the following code (in bold)<\/strong> right AFTER the detail row prints, which would immediately follow the <tr class=\"detailBackground\" >...<\/tr> <\/code>block.<\/p>\n\n\n\n<!-- <#list section.detailRows as row> -->\n <tr id=\"level_[DETAIL]\" class=\"level-row\">\n <td class=\"rsd\" align=\"center\" colspan=\"6\">Detail Level<\/td>\n <\/tr>\n <tr class=\"detailBackground\" data-level=\"detail\">\n <td class=\"ten text-right\" nowrap=\"nowrap\">${row.CNUM?html}<\/td>\n <td class=\"ten text-right\" nowrap=\"nowrap\">${row.PNUM?html}<\/td>\n <td class=\"ten text-right\" nowrap=\"nowrap\">${row.MTDSALES?html}<\/td>\n <td class=\"ten text-right\" nowrap=\"nowrap\">${row.MTDCOST?html}<\/td>\n <\/tr>\n <!--<#if rowCounter = 0>\n <#assign dataRange='=sum(C2:'\/>\n <#assign rowCounter = 2>\n <\/#if>\n <#assign rowCounter = rowCounter + 1>--> \n<\/strong><!-- <\/#list> --><\/code><\/pre>\n\n\n\nThis 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. <\/p>\n\n\n\n
\nThe 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. <\/p>\n\n\n\n
Further steps will also use column ‘C’ for defining ranges.<\/p>\n\n\n\n
Step 3 – Add Freemarker to the Subtotal Level<\/h3>\n\n\n\n
Find where the Subtotal Level 1 is printing out, which is contained within these comments:<\/p>\n\n\n\n
<!-- <#if subTotal1.dsp != 0> -->\n...\n...\n<!--<\/#if> --><\/code><\/pre>\n\n\n\nWithin this block, add the following code (in bold<\/strong>) right BEFORE the <tr class=\"sub1Background data-level=\"1\"><\/code> tag.<\/p>\n\n\n\n<!-- <#if subTotal1.dsp != 0> -->\n\n <tr id=\"level_[SHIPYEAR]\" class=\"level-row\">\n <td class=\"rsd\" align=\"center\" colspan=\"6\">Subtotal Level 1 by [SHIPYEAR]<\/td>\n <\/tr>\n\n <!--<#assign endOfDetailRange=rowCounter-1>\n <#assign begOfNextDetailRange=rowCounter+1>\n <#assign dataRange=dataRange+'C'+endOfDetailRange+',C'+begOfNextDetailRange+':'>\n <#assign rowCounter = rowCounter + 1>\n <#assign detailOnly='N'>--->\n\n <!--<#if firstSubtotal='Y'>\n <#assign subTotalDataRange='=sum(C2:C'+endOfDetailRange+')'>\n <#else>\n <#assign subTotalDataRange='=sum(C'+begOfNextRange+':C'+endOfDetailRange+')'>\n <\/#if>\n \n <#assign begOfNextRange=endOfDetailRange+2>--><\/strong>\n \n<tr class=\"sub1Background\" data-level=\"1\">\t \t\t <td class=\"s1 text-right\">${subTotal1.CNUM?html}<\/td> \t\t <td class=\"s1t\">${lastRow.PNUM?html}<\/td>\n <td class=\"s1 text-right\">\n <!--<#if page.outfmt==16>-->\n ${subTotalDataRange}\n <!--<#else>--><\/strong>\n ${subTotal1.MTDSALES?html}\n <!--<\/#if>-->\n<\/strong> <\/td>\n <td class=\"s1 text-right\">${subTotal1.MTDCOST?html}<\/td>\n <!--<#assign subTotalDataRange=''>\t\n <#assign firstSubtotal='N'>--><\/strong>\n<\/tr>\n\n<!--<\/#if> --><\/code><\/pre>\n\n\n\nThese various lines of code are summed up here:<\/p>\n\n\n\n
endOfDetailRange<\/code> – Gets the row number from the last detail line before the subtotal prints out.<\/p>\n\n\n\nbegOfNextDetailRange <\/code>– Defines the row number from the first detail line after the subtotal has printed.<\/p>\n\n\n\n <\/strong>dataRange <\/code>– concatenates the two previous variables to create the range needed for the =SUM()<\/code> function that will be computed for the Grand Total level (not shown yet), based on the details.<\/p>\n\n\n\nWhat follows after is logic summarized as follows:<\/p>\n\n\n\n
\n- IF the subtotal total line being printed is the first subtotal line of the report –\n
\n- Then the range for the SUM function is the first detail row, C2, combined with the end of the detail range.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- ELSE when the subtotal total line being printed is NOT the first subtotal line of the report –\n
\n- Then the range for the SUM function is the previous range’s last detail row plus two (skipping the subtotal row that prints). <\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
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.<\/p>\n\n\n\n
Step 4 – Add Freemarker to the Grand Total Level<\/h3>\n\n\n\n
Find where the Grand Total level is printing out, which is contained within these comments:<\/p>\n\n\n\n
<!-- <#if grandTotal.dsp != 0> -->\n...\n...\n<!-- <\/#if> --><\/code><\/pre>\n\n\n\nWithin this block, add the following code (in bold<\/strong>) inside the table cell (<td>..<\/td><\/code>) where the value being auto summed is printing.<\/p>\n\n\n\n\n<!-- <#if grandTotal.dsp != 0> -->\n\n <tr id=\"level_[GRAND]\" class=\"level-row\">\n <td class=\"rsd\" align=\"center\" colspan=\"6\">Grand Total Level<\/td>\n <\/tr>\n\n <tr class=\"grandBackground\" data-level=\"0\">\n <td class=\"grand text-right\">\n Grand Total\n <\/td>\n <td class=\"grand text-right\">\n <\/td>\n <td class=\"grand text-right\">\n <!--<#if page.outfmt==16>-->\n <!--<#if detailOnly='N'>-->\n ${dataRange?keep_before_last(\",\")})\n <!--<#else>-->\n =sum(C2:C${rowCounter-1})\n <!--<\/#if>-->\n <!--<#else>--><\/strong>\n ${grandTotal.MTDSALES?html}\n <!--<\/#if>--><\/strong>\n <\/td>\n <td class=\"grand text-right\">\n ${grandTotal.MTDCOST?html}\n <\/td>\n <\/tr>\n\n<!-- <\/#if> --><\/code><\/pre>\n\n\n\nThe parts in bold can be interpreted like this:<\/p>\n\n\n\n
\n- IF the report is exported to Formatted Excel (outfmt=16) then\n
\n- 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).<\/li>\n\n\n\n
- 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)<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- ELSE when the report is not <\/strong>exported to Formatted Excel (outfmt=16) then print the original field value in the presentation.<\/li>\n<\/ul>\n\n\n\n
Step 5 – Set the Excel button to Formatted Excel (outfmt=16)<\/h3>\n\n\n\n
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. <\/p>\n\n\n\n![\"\"\/](\"\/techblog\/images\/autosum_formattedexcelbutton.jpg\")
\n\t\t\t