I needed to roll up profitability numbers by sales rep and by office. I created a saved search that included all the detail at a line item level. Next, I engineered an Advanced PDF Template to “pretty print” the results. Then the real trick… rolling up the numbers for a summary. I needed to know that the rolled up numbers matched my detail exactly. The best way to do this… use the detail to print the summary!

My tool of choice was Freemarker and NetSuite’s Advanced PDF Template. Where I ran into trouble was finding documentation on Freemarker lists and hashes. Here is my cheat sheet.

In my example, I’m going to roll up the extended price, NetSuite’s calculated cost, and the vendor PO cost of an item. I’ll roll them up to the sales rep level.

In my Advanced PDF Template, I start with this Freemarker code.

<#assign salesReps = {} />
<#list results as result>

This creates a Freemarker list (not a hash) and loops through every line item, one at a time.

Then I check to see if there is a list entry for my sales rep. The rep’s name serves as a unique key in my list. If an entry doesn’t exist, I add one. This syntax is really quirky in Freemarker. I’m not even going to try to explain it! The magic happens at the “plus” sign.

<#if !salesReps[result.salesrep]??>
    <#assign salesReps = salesReps + {result.salesrep :
          {“ext_price” : result.ext_price, 
            “est_cost” : result.est_cost,
            “vpo_cost” : result.vpo_cost}} />

Note that the list has a key of the sales rep’s name (unique), followed by a hash of fields I want to roll up by sales rep.

In the <#else /> clause, I need to roll up the hash fields. Here’s how that works.

<#assign new_ext_price = salesReps[result.salesrep].ext_price + result.ext_price />
<#assign salesReps = salesReps + {result.salesrep : 
    {“est_price” : new_ext_price,…

And finally, printing the rolled up values…

<#list salesReps as name, values>
    <tr>
        <td>${name}</td>
        <td>${values.ext_price?string.currency}</td>
        …

In my search to understand Freemarker lists and hashes, I found these links.

• https://freemarker.apache.org/docs/dgui_template_exp.html#dgui_template_exp_direct_hash
• https://freemarker.apache.org/docs/ref_builtins_sequence.html
• https://freemarker.apache.org/docs/ref_builtins_hash.html

Beyond these… the well ran dry!