This article is relevant if you seek to produce charts or other graphical representations of your data in NetSuite and you prefer not to purchase third party charting tools.
Background
Because of the power of our Content Renderer Engine (CRE) which has solved the challenge of multiple joined NetSuite saved searches, including aggregates, we now have the possibility to bring information together that can be represented visually. NetSuite’s built-in graphical elements are good; yet they work in a particular manner and if you want to have more control, you have to look elsewhere.
In contrast, Google offers a Charts API which is free to use and very flexible. It is driven by basic JavaScript and it gives you great results. The Content Renderer Engine (CRE) allows us to easily use a NetSuite style Advanced HTML template to generate the chart.
CRE Profile with NetSuite Aggregate Saved Search to Generate Google Chart HTML
The key to this solution is that we can now take as many saved searches as we want, including aggregates that contain many formula fields, hand it to a template engine and output HTML. The resulting document contains basic JavaScript to automatically draw the chart once the HTML page is rendered.
With HTML output, it is straight forward to output this in a NetSuite Dashboard Portlet, as an inline HTML field on a form, in a SuiteLet, or in other creative ways.
The template is below. We have two saved searches driving this chart:
- margin: A summary of all income, cost and margin produced for the year by a professional.
- weekly: A summary of the last three weeks of billable work in contrast to a target.
In this case, we spin through the saved search results to generate a flattened array of information to feed into Google’s Chart API to produce a nice Combo Chart. Note, we are using the TrimPath scripting engine versus NetSuite’s FreeMarker due to limitations of formula fields imposed by their implementation. The TrimPath notation is very close to FreeMarker’s.
<?xml version="1.0"?><!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">
<html>
<head>
<script type='text/javascript' src='https://www.gstatic.com/charts/loader.js'></script>
<script type='text/javascript'>
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
//use the template to calculate the summary of all income, margin and costs
//due to NetSuite's FreeMarker limitations, use the TrimPath template engine as an option set in CRE
{var c = 0}
{var m = 0}
{var i = 0}
{var p = 0}
{for s in margin}
{eval}
c = parseFloat(c) + parseFloat(s.columns.Cost.value)
m = parseFloat(m) + parseFloat(s.columns.Margin.value)
i = parseFloat(m) + parseFloat(c)
{/eval}
{/for}
{eval}
p = m / i * 100;
p = p.toFixed(1) + '%';
{/eval}
function drawChart() {
//find related items in 3 week average by connecting up data
var data = google.visualization.arrayToDataTable([
['Resource', 'Cost', 'Margin', 'Weekly Target', '3 Week Trailing Average']
{for r in margin}
,[ '${r.columns.Item.value}', ${r.columns.Cost.value}, ${r.columns.Margin.value}
{var weekval = false}
{for w in weekly}
{if r.columns.item.internalid == w.columns.item}
, ${w.columns.Target.value}, ${w.columns._3WeeksAvg.value}
{var weekval = true}
{/if}
{/for}
{if weekval == false}
, 0, 0
{/if}
]
{/for}
]);
var options = {
title: 'Margin by Resources - Income: $' + formatNumber(${i}) + '; Cost: $' + formatNumber(${c}) + '; Margin: $'+ formatNumber(${m}) + ' @ ${p}',
vAxes: [{title: '$Billable as of: ' + new Date().toLocaleDateString()}, {title: 'Billable Hours', maxValue: 50} ],
seriesType: 'bars',
isStacked: true,
series: [{type:'bars'},{type:'bars'},{type:'line', targetAxisIndex: 1, lineWidth:5}, {type:'line', targetAxisIndex: 1, lineWidth:5}]
};
var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
function formatNumber (num) {
return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, "$1,")
}
</script>
</head>
<body>
<div id='chart_div' style='width: 100%; height: 600px;'></div>
</body>
</html>
Take Control and Present Data Visually
Once we solved the challenge of crafting multiple NetSuite linked saved searches which can output to a template engine, we then opened a world of possibility to overcome yesterday’s limitations without needing to break out SuiteScript to produce one-off programs to get the results we wanted. This allows a less technical person, one with good HTML skills, to be productive. We give away our CRE tool to NetSuite end users who have real-world problems to solve. If you have a challenge that you believe CRE could solve, let’s have a conversation.
See Related Articles
- Deliver Complex NetSuite Outputs via User-Centric Options
- Easier Crafting with Data-Driven Syntax for NetSuite Advanced PDF Templates
- Learn the Framework to Extend NetSuite Content Generation
- Content Renderer Engine 2.0 with NetSuite SuiteQL
- Learn How to Teach NetSuite to Generate Dynamic Excel Spreadsheets
- How To: Connect NetSuite Reports to Excel Web Queries
- Learn how to Batch Generate NetSuite Driven Documents
- Learn How to Bring NetSuite Subsidiary Data into Advanced PDFs
- How To: Generate NetSuite Item Weighted Price from Kits
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
Hi Marty
Thank you for writing the article on integration of Charts in Advanced PDF.
I would like to know how you integrated the Trimpath engine into Advanced PDF?
Hello,
We have created a tool (CRE: Content Renderer Engine) that allows us to use different Template engines to drive content generation. In the case above, because NetSuite’s implementation of the FreeMarker template engine does not know how to work with Aggregate Search column names, we solve it by using the TrimPath engine. This give us full control over the reaching the data from the search. See this article:
https://blog.prolecto.com/2015/06/01/supercharge-netsuite-advanced-pdfhtml-templates/
Here is an older article that discusses Trimpath and how it can be used in NetSuite. We wrote it before we built our CRE tool:
https://blog.prolecto.com/2014/01/04/framework-for-generating-custom-netsuite-pdf-files/
Marty